Assignment#$1$ Loan Amortization $($Due Date February $28,2024)$

A$.$ You are thinking of purchasing a house that costs $$415,000.$ You have $$35,000$ in cash that you can use as a down payment, but you need to borrow the rest of the purchase price. Assume there are no closing costs. The bank is offering a $30-$year mortgage that requires monthly payments and has an annual interest rate of $4\mathrm{.}15\%$ per year.

What will your monthly payments be if you sign up for this mortgage?

Present the amortization schedule $($Beginning balance, Monthly payment, Principal payment, Interest payment, Ending balance$)$ on a monthly basis using Excel.

Calculate the total amount of interest paid throughout the life of the loan $($sum up the amounts in the Interest column$).$

Create a graph depicting the changes in the portions of interest and principal for each monthly payment throughout the life of the loan.

B$.$ Suppose you have the option to take out a $15-$year mortgage with an annual interest rate of $3\mathrm{.}49\%.$ What will the new monthly payment be$?$

Present a new amortization schedule $($Beginning balance, Monthly payment, Principal payment, Interest payment, Ending balance$)$ on a neve excel worksheet.

Calculate the total amount of interest paid throughout the life of the loan $($sum up the amounts in the Interest column$).$

Create a graph depicting the changes in the portions of interest and principal for each monthly payment throughout the life of the loan.

C$.$ How much do you save if you go with the $15-$year mortgage versus the $30-$year mortgage example above? Compute the difference between the total interest paid in parts A and B$.$

Grading rubric :

Points

$\backslash$table$[[$A$.$ Spreadsheet analysis of time value of money problem $($loan amortization$)$:$,],[$Correct calculation of monthly loan payment,$20],[$Determination of interest and principal for each payment,$20]]$