Create the table and graph in Excel to show monthly loan payments, principal payments, interest payments, and remaining principal. You will want to use the PMT, PPMT, and IPMT functions as shown. Once complete, enter the inputs below into your table and answer the following questions. Upload your Excel file when complete.

Purchase Price = $650,000

Down payment = 20%

Interest Rate = 3.75%

Amortization = 30 years

Term = 30 years

Questions:

1. What is the size of the mortgage loan? (i.e., How much is being borrowed?)

2. How many payments will the borrower make over the life of the loan?

3. What is the Loan to Value ratio (%)?

4. How much is the total monthly payment?

5. How much principal will remain at the end of the 30 year term?

6. How much total interest will the borrower pay over the life of the loan?

7. What is the total cost of the loan? (i.e., How much total will the borrow pay in interest and principal over the life of the loan?)

8. If the loan term is 10 years, rather than 30 years (amortization remains at 30 years), how much principal will remain at the end of the term?

9. If both the loan term and amortization are 10 years, what will the total monthly payment be?

10. If the interest rate is reduced by 25 basis points, how much will the borrower save over the life of the loan (as compared to the original 3.75% interest, 30 year term, 30 year amortization)?