FIN2020 Excel and VBA

FIN2020 Excel and VBA – Individual Assignment

YOU MUST UPLOAD THE EXCEL FILE TO CANVAS BY

Deadline: 5:00 PM, Date: Monday 9th December 2024Assignment

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 besavedand remain active in the spreadsheets. (Don't paste values!) Another workbook entitled

'ndividual project information.xlsx' provides essential information related to the Part IIquestions.

Part I: Mortgage calculator (40%) You need to create a functional mortgage calculator that allows users to input loanparameters and calculate monthly payments, total interest paid, and the loanamortizationschedule. The calculator needs to be built in one worksheet named “MortgageCalculator”. 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 remainingworking years of the customer (i.e., age to retire - current age); 2) The loan amount must notexceed 90% of the property value; 3) the loan amount must not exceed 5 times thecustomer's annual salary.The calculator is designed to implement both fixed-rate and adjustable-rate mortgagecalculations. The mortgage starts with the fixed rate term. After the fixed rate term, yourcalculator needs to re-compute the monthly payment based on the flexible interest rate, andthen 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 paymentinto principal and interest, and displays the remaining balance after each payment.

• Interest Rate Adjustments: For adjustable-rate mortgages, the amortization schedulemust reflect any changes in interest rates at specified adjustment periods.Page 2 of 4 • Loan Term Constraint: Ensure the loan term does not exceed the customer’sremaining working years (age to retire - current age).Loan Amount Constraints: Verify that the loan amount is no more than 90% of theproperty value and does not exceed five times the customer’s annual salary.Error Handling: Implement error messages to alert users if any input violates theseconstraints,ensuring correct inputs entry.Other output can be added if you would like to set up additional functions. Then, three chartsneed to be created based on the outputs.

1): a pie chart showing the proportion of total payments that go towards principal vs. interesover the life of the loan.

2): a line graph that displays the loan balance over time. This should clearly show how theprincipal 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 thehistorical performance of the technical trading rules. 代寫FIN2020 Excel and VBA Once the investors know about thehistorical performance, they would like to invest in one or more of these tradingrulestogenerate 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 meaningfulworksheet names.

1. 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 'Individualprojectinformation.xlsx'. Obtain the daily closing price (last_price) series for these stocksfromBloomberg. The data sample should range from 01/09/2022 (1st September2022) to 01/09/2024 (1st September 2024). Save the obtained price series into theworkbook and name the new worksheet 'Original Stock prices'. No need to correctthe errors caused by the Bloomberg functions. No further calculations can be madin this worksheet.10%

2) Use the Excel and VBA knowledge from our lectures to construct 5 technical tradingules for each stock. You need to save the process data, formulas, and functions inhe workbook properly. The 5 trading rules include the following:

1. 3 Moving Average rules: If the daily closing price of the stock moves up abovethe moving average over the past q days, MA(q), go long the stock until itsPage 3 of 4until the daily closing price moves up above MA(q), at which time go long thestock.

1. 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, goshort the stock and hold the position until the next signal. If RSI(h) is below50-v for at least 2 days and then moves above 50-v, go long the stock andhold the position until the next signal. No signals are generated in otherscenariosAs 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. 3) In-sample evaluation. Your task involves creating a user-friendly input system, eitheras a window or within a worksheet, to collect specific information from users. Hereare 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 withthe user's specified requirements for performance metrics. For example, if theusers choose the Sharpe ratio (volatility) as the performance metric, thetrading rule with the highest (lowest) Sharpe ratio should be presented.

• Generate a cumulative performance plot based on the returns generated bythe chosen trading rule and the initial investment amount.Your solution should make it easy for users to input their preferences and receiveclear information on the selected trading rule's name and the cumulativeperformance of their investment.30%Save your workbook titled 'Your student number+FIN2020' (for instance, your studentnumber 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! Page 4 of 4