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