| Home Mortgage Spreadsheet Assignment |
| The purpose of this project is to investigate the cost of a home mortgage. |
| A couple has decided to purchase a home. The negotiated price is $175,000 |
| The couple can pay the standard 20% down payment, and the rest will be |
| financed at the annual interest rate of 4.5% for a 30-year loan. |
| Some questions: |
| 1. Is a mortgage agreement a type of annuity? |
| 2. Calculate the down payment. |
| 3. Calculate the amount to be borrowed (amortized). |
| 4. Calculate the monthly payment. |
| 5. Since there will be 360 monthly payments, how much will actually be paid for the home? |
| Don't forget to add in the down payment. |
| 6. How much of your result from Question 5 is interest? |
| 7. Complete the ammortization table below for all 360 payments. As you complete the rows, |
| pay attention to the amount of interest and the amount of principal that is paid each month. |
| Some hints: Complete rows 1 and 2 of the table, then copy row 2 over and over in order |
| to finish the table at the 360th row. |
| As you copy the rows, make sure that the monthly payment amount does not change. |
| Your monthly payment amount is an absolute cell reference. |
| You do not have to enter each period number individually. In the first row of your table, |
| enter 1. In the second row of your table, enter a formula, "=cell + 1". Then copy that cell |
| formula down the column of your table. |
| Period # | Outstanding Principal | Interest Due | Payment Amount | Portion of Principal Reduced |
| 1 |
| More Questions: |
| 8. The first payment consists of $________ in interest and $_________ in principal. |
| 9. So, approximatlely the first 200 payments consist of mostly _________. |
| 10. As the number of payments increases, what happens to the amount of interest that is included in each |
| month's payment? |
| 11. As the number of payments increases, what happens to the amount of principal that is included in each |
| month's payment? |
| 12. Sum up the "Interest" column in the above table. Does it agree with your calculation in question 6? |
| 13. Sum up the column in the above table titled, "Payment Amount" in the above table. Does it |
| agree with your calculation in question 5 (minus the down payment)? |
| 14. Sum up the column in the above table titled, "Portion of Principal Reduced" in the above table. Does it |
| agree with your calculation in question 3? |
| 15.Suppose the couple decides to deposit an extra $100 in principal each month. Complete the ammortization |
| table below. |
| Period # | Outstanding Principal | Interest Due | Payment Amount | Extra Principal Deposit | Portion of Principal Reduced |
| 1 |
| Remaining Questions: |
| 16. What is the total amount of interest paid on this loan? |
| 17. How many fewer payments were made when an extra $100 in principal was paid each month? |
| | The number of payments was reduced by | | | payments. |
| 18. How much money was saved when an extra $100 in principal was paid each each month? |
kevin_225
home_mortgage_spreadsheet_assignment.xlsx
