Mortgage Analysis

Part I

  1. (10 points) Type your name in the title and fill in the terms for each scenario in sheet 1 and 2. Cells- A6:D6
  2.  (10 points)Calculate the monthly payment using the equation for both terms. Cell- E6
  3.  (10 points)Use the built in PMT function in Excel to verify the payment is correct. Cell- F6
  4.  (40 points)Create Amortization tables for both sheets. Cells- A11:E11 (and down for the length of the mortgage)

 

Part II                    

  1.  (10 points)In the Investment column calculate the amount of money that will be invested each month for both sheets. Remember once the house is paid off the full $2000 will be invested. Cells- G11:down for 40 years
  2. 6.  (40 points)In the Accumulation column calculate the balance of the investment each month assuming it grew at an interest rate of 12% per year (Cell- G6) over 40 years for both sheets. Cells- H11:down for 40 years
  3. (20 points)Use the built in FV function of Excel to verify the total amount accumulated in both sheets. Remember at some point the investment amount changes; therefore, you will need to use the FV function more than once. Final Answer: Cell- H6

 

 

Part III

  1. (10 points) Calculate the property taxes for the first ten years assuming they are $2000 for year 1 and rise by 3% each year. Cells- L6:L15
  2. (10 points) Calculate the total interest paid each year for the first ten years of the loan. Remember the Amortization Schedule is in months and we are constructing this table in years. Cells- M6:M15
  3. (10 points) The Tax Deduction is the sum of the interest paid for the year and the property tax. Calculate the tax deduction for the first ten years. Cells- N6:N15
  4. (10 points) We will call the last column Tax Benefit. To simplify calculations let’s assume the couple are in the 25% tax bracket, so the tax deduction will lower their taxes due by approximately 25% of the deduction. However, a person who doesn’t own a house doesn’t pay the property taxes. So, this column will be the difference between the tax savings (25% of the deduction) and the property tax. Cells- O6:O15

 

Part IV

  1. (20 points) Write a reflection. Share some of your findings. What did you find interesting? Anything shocking? Are you left with any “what if” questions?

Note: If each calculation is built upon functions calling on cells you should be able to adjust any of the terms to see what happens if you change anything. Cell- K18