Set up a Excel spreadsheet with three input fields at the top Amount Borrowed,

Annual Interest Rate, and Life of Loan in years. This problem should work any

combination of the three inputs.

In a fourth cell calculate the monthly payment using the annuity formula. In the

fifth cell use the PMT function for the payment.

Assume you are taking out a 30-year mortgage. The program should work for any

combination of mortgage amounts and interest rates.

Then set up a repayment table. Set up five columns headed:

Payment

Interest Paid

Principal Paid

Additional Payments

Remaining Principal

At the top of the sheet include 2 cells with summations of total interest and

principal. Use for testing a loan of $200,000 and an interest rate of 4%.

Then do two graphs on separate sheets:

1. Monthly payment and monthly interest and principal payment across

the life of the loan. The payment line will be a straight horizontal line

set up two columns and two rows for the graphing input.

2. Remaining principal across the life of the loan. (Why isnt the loan paid

off after making half the payments?)

Print out twelve months of the repayment table, and twelve months of the

formulas.

in .xlsx please :)