代做FIN2020、代写Java/c++程序语言

日期：2024-12-07 10:31

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 90% 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 90% 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!