Question

Write a VBA program to compare two mortgages based on total interest costs for Canadian users.
General requirements

Allow user to provide info on two mortgages to be compared

Obtain all user inputs through Userform

The following inputs should apply to both mortgages

Property value

Down pay in percentage

Payment frequency (limit to monthly, semi-monthly, and weekly)

Ask user for the following mortgage specific inputs

Type: Fixed or Variable

Term: 1, 2, 3, 4, 5

Mortgage rate for FRM

Prime and deal for VRM

Conditional on mortgage info provided, ask user to provide interest forecast for the relevant period. For example, if a user wants to compare a 2-year FRM vs a 4-year VRM, your model should ask the use to provide quarterly prime change from year 1 to year 4, and the long term fixed mortgage rate.

Compute total interest costs to fully repay the loan

Use CUMIPMT function (no amortization table allowed)

Based on assumptions under Assumptions below

Outputs

All user provided information on mortgages and interest rates should be put in a table in the spreadsheet in an organized manner

Total interest costs for each option should be listed at the end of the table with decision clearly stated based on comparing the total interest costs.

Assumptions

Semi-annual compounding

When two mortgages (S and L) are not equal in terms (S with shorter term and L with longer term), assume an add-on 30-year FRM (A) will follow mortgage S, such that the term of mortgage S plus the term of mortgage A equals the term of mortgage L.

For the add-on fixed rate mortgage A, use forecasted prime rate at the beginning of the year this add-on FRM starts as the fixed mortgage rate.  

Following mortgage L (and equivalently mortgage S+A), mortgage loan balance will be fully amortized at the long term fixed rate in a 30-year FRM (without term) provided by the user.

Hypothetical mortgage (the add-on mortgage and the terminal mortgage) always starts with 30-year life

any information would be helpful

Answer 1

Seven factors that determine your mortgage interest rate

1. Credit scores

2. Home location

3. Home price and loan amount

4. Down payment

5. Loan term

6. Interest rate type

7. Loan type

Sales_Price = float(input('Enter sales price of house in COUNTRYName: '))

Down_Payment = float(input('Enter down payment as a percentage of Sales Price, e.g. 5 for 5%: '))
Loan_Amount = Sales_Price*(1-Down_Payment/100)Mortgage_Type = float(input('Enter mortgage type in years, e.g 10 for 20 years: '))
Loan_Term = int(12*Mortgage_Type)
Interest_Rate = float(input('Enter loan interest rate as a percentage, e.g 5 for 5%: '))
R = 1 +(Interest_Rate)/(12*100)X = Loan_Amount*(R**Loan_Term)*(1-R)/(1-R**Loan_Term)Monthly_Interest = []
Monthly_Balance = []for i in range(1,Loan_Term+1):
Interest = Loan_Amount*(R-1)
Loan_Amount = Loan_Amount - (X-Interest)
Monthly_Interest = np.append(Monthly_Interest,Interest)
Monthly_Balance = np.append(Monthly_Balance, Loan_Amount)