FIN2020 Excel and VBA – Individual Assignment
YOU MUST UPLOAD THE EXCEL FILE TO CANVAS BY
Deadline: 5:00 PM, Date: Monday 9th December 2024
Page 2 of 4
Assignment
The assignment contains two parts. In Part I, you are expected to build a mortgage
calculator. Part II requires a more advanced trading rule selector. All your works need to
be saved in one workbook. The cell references, formulas, and functions must be saved
and remain active in the spreadsheets. (Don't paste values!) Another workbook entitled
'Individual project information.xlsx' provides essential information related to the Part II
questions.
Part I: Mortgage calculator (40%)
You need to create a functional mortgage calculator that allows users to input loan
parameters and calculate monthly payments, total interest paid, and the loan amortization
schedule. The calculator needs to be built in one worksheet named “Mortgage Calculator”.
Input Fields must include the following elements:
Customer Information:
• Name
• Age
• Gender
• Age to retire
• Personal income (annual salary)
Loan Information:
• Loan amount
• Property value
• Fixed interest rate
• Fixed rate terms
• Flexible interest rate
• Loan term (years)
Other inputs can be added subject to demand.
The input should follow the constraints: 1) The loan term cannot exceed the remaining
working years of the customer (i.e., age to retire - current age); 2) The loan amount must not
exceed **% of the property value; 3) the loan amount must not exceed 5 times the
customer's annual salary.
The calculator is designed to implement both fixed-rate and adjustable-rate mortgage
calculations. The mortgage starts with the fixed rate term. After the fixed rate term, your
calculator needs to re-compute the monthly payment based on the flexible interest rate, and
then adjust the payments periodically as the interest rate changes during the loan term.
The output requirements for the mortgage calculator are as follows:
• Monthly Payments: Calculate monthly payments for both fixed-rate and adjustablerate
mortgages.
• Amortization Schedule: Provide a detailed schedule that breaks down each payment
into principal and interest, and displays the remaining balance after each payment.
• Interest Rate Adjustments: For adjustable-rate mortgages, the amortization schedule
must reflect any changes in interest rates at specified adjustment periods. Page 3 of 4
• Loan Term Constraint: Ensure the loan term does not exceed the customer’s
remaining working years (age to retire - current age).
• Loan Amount Constraints: Verify that the loan amount is no more than **% of the
property value and does not exceed five times the customer’s annual salary.
• Error Handling: Implement error messages to alert users if any input violates these
constraints, ensuring correct inputs entry.
Other output can be added if you would like to set up additional functions. Then, three charts
need to be created based on the outputs.
1): a pie chart showing the proportion of total payments that go towards principal vs. interest
over the life of the loan.
2): a line graph that displays the loan balance over time. This should clearly show how the
principal is paid down over the loan term.
3): a stacked bar chart that shows the monthly breakdown of principal and interest payments
for the first 24 months of the loan.
Part II: The trading rule selector (60%)
Some investors intend to apply several technical trading strategies in the US stock market.
They aim to create a trading rule selector using an Excel workbook to facilitate this.
However, they lack proficiency in Excel operations and VBA coding. Consequently, they
have enlisted your expertise to develop a user-friendly trading rule selector.
The investors have chosen specific stocks for investment but are uncertain about the
historical performance of the technical trading rules. Once the investors know about the
historical performance, they would like to invest in one or more of these trading rules to
generate profits. You are expected to finish the following tasks to achieve these targets. The
answers for Part II can be saved in different worksheets with meaningful worksheet names.
1) Each student is assigned 5 constituent stocks from the Standard & Poor's 500 index.
Please check the details from the 'Stock names' worksheet of 'Individual project
information.xlsx'. Obtain the daily closing price (last_price) series for these stocks
from Bloomberg. The data sample should range from 01/09/2022 (1st September
2022) to 01/09/2024 (1st September 2024). Save the obtained price series into the
workbook and name the new worksheet 'Original Stock prices'. No need to correct
the errors caused by the Bloomberg functions. No further calculations can be made
in this worksheet.
10%
2) Use the Excel and VBA knowledge from our lectures to construct 5 technical trading
rules for each stock. You need to save the process data, formulas, and functions in
the workbook properly. The 5 trading rules include the following:
a. 3 Moving Average rules: If the daily closing price of the stock moves up above
the moving average over the past q days, MA(q), go long the stock until its
daily closing price moves down below MA(q), at which time go short the
stock. If the stock's daily closing price moves down below MA(q), go short Page 4 of 4
until the daily closing price moves up above MA(q), at which time go long the
stock.
b. 2 Oscillator rules: If the relative strength indicator (RSI) over the past h days,
RSI(h), is above 50 + v for at least 2 days and then moves below 50 + v, go
short the stock and hold the position until the next signal. If RSI(h) is below
50-v for at least 2 days and then moves above 50-v, go long the stock and
hold the position until the next signal. No signals are generated in other
scenarios.
As a result, you will have 5*5=25 trading rules in total. The trading rule inputs, e.g., q,
h, and v, are assigned according to your student number in the 'TTR inputs'
worksheet of 'Individual project information.xlsx'.
20%
3) In-sample evaluation. Your task involves creating a user-friendly input system, either
as a window or within a worksheet, to collect specific information from users. Here
are the details:
• Users should be able to input their personal information, including their name,
age, and initial investment amount.
• They should also be able to select a stock of their choice.
• Users can specify their preferred performance metrics, such as high profits,
low volatility, or a high Sharpe/Sortino ratio.
• Include a 'Start' button that, when clicked, initiates the calculations.
Upon receiving this information and clicking 'Start,' the system should:
• Identify and report the name of the technical trading rule that best aligns with
the user's specified requirements for performance metrics. For example, if the
users choose the Sharpe ratio (volatility) as the performance metric, the
trading rule with the highest (lowest) Sharpe ratio should be presented.
• Generate a cumulative performance plot based on the returns generated by
the chosen trading rule and the initial investment amount.
Your solution should make it easy for users to input their preferences and receive
clear information on the selected trading rule's name and the cumulative
performance of their investment.
30%
Save your workbook titled 'Your student number+FIN2020' (for instance, your student
number is 123456, the file title should be 123456FIN2020) and ensure it is in the
appropriate format to support the VBA code.
Once you are done, please upload your workbook on Canvas by 5:00 PM (UK time).
Date: Monday 9th December 2024.
Good luck!
請加QQ：99515681 郵箱：99515681@qq.com WX：codinghelp
