MAT $3330-$ Excel Homework $-$ Amortization Schedule development

For this excel assignment, you are going to create amortization schedules for both a home mortgage and a car

loan. Make sure you save this spreadsheet somewhere that you can use it later in your life $-$ it will definitely come

in handy.

Create a template for an amortization schedule. Your spreadsheet should allow the end user to change

the loan amount, the term of the loan and the interest rate on the loan. The columns in your

amortization table should include the following items:

Payment Number

Payment Amount

Principal

Interest

Loan Balance after Payment

Create an amortization schedule for a $30$ year, $$250,000$ mortgage $($loan$)$ on a house with monthly

payments and an interest rate equal to $8\%$ compounded monthly. As stated above, allow for the ability to

change the loan amount, the term of the loan and the interest rate on the loan in your spreadsheet. The

original loan amount will go in the last column of the first line of your amortization table.

How much total interest is paid over the term of the loan? What is the outstanding balance at the end of

$10$ years, $15$ years, $20$ years?

At the end of two years, you have a great uncle who passes away and leaves you $$20,000.$ You decide

that you are going to add that amount to your ${24}^{th}$ mortgage payment. When you manually add that

amount to your ${24}^{th}$ payment, your spreadsheet will still reflect your original mortgage payment in all

subsequent calculations but your outstanding balance will decline much faster than originally. How long

does it now take to pay off the mortgage versus your original term of $30$ years?

Create a separate amortization schedule for a $5$ year, $$25,000$ car loan with monthly payments and an

interest rate equal to $10\%$ compounded monthly. As with the mortgage amortization schedule, allow for

the ability to change the loan amount, the term of the loan and the loan interest rate.