(a) Mortgage Amortization Assume you have purchased a house valued at $800,000 in year 2030. You choose to pay $200,000 (down payment) today and put the rest to a 25-year level payment mortgage plan. The nominal interest rate is 3.60% compounded monthly. The first payment is due one month from today. To construct an amortization table of the mortgage, your tasks are as follows. For all dollar amounts, show your results up to $0.01. In cells C4, C5, C9, C13, input all values given. In cells C7, C11 and C15, calculate the relevant parameters. In cell C17, calculate the monthly mortgage payment. Now turn to the amortization table. The first row (time 0) has been filled out for you. As shown in the cell formula, the "Ending Balance for time 0 is simply your mortgage amount. Fill in the "Beginning Balance" for time 1. Fill in the Mortgage Payment, Interest Payment, Principle Payment and Ending Balance" for time 1. Repeat the preceding step for time 2, so that you familiarize yourself with the calculation. Copy the cell formulas to the end of the mortgage term. If everything is done correctly, the Ending Balance should be obvious to you. (b) Salary Increase (G) Assume that your salary increases by 2% each year. You decide to increase your mortgage payments geometrically at the same rate every twelve payments. The monthly payment for the first year is the same as calculated in (a), and the last payment is smaller than usual. Calculate the total number of payments needed in order to pay off the entire mortgage. A column "Payment Scale (Column L) has been added which may be helpful. Input your final answer in cell C21. (a) Mortgage Amortization Assume you have purchased a house valued at $800,000 in year 2030. You choose to pay $200,000 (down payment) today and put the rest to a 25-year level payment mortgage plan. The nominal interest rate is 3.60% compounded monthly. The first payment is due one month from today. To construct an amortization table of the mortgage, your tasks are as follows. For all dollar amounts, show your results up to $0.01. In cells C4, C5, C9, C13, input all values given. In cells C7, C11 and C15, calculate the relevant parameters. In cell C17, calculate the monthly mortgage payment. Now turn to the amortization table. The first row (time 0) has been filled out for you. As shown in the cell formula, the "Ending Balance for time 0 is simply your mortgage amount. Fill in the "Beginning Balance" for time 1. Fill in the Mortgage Payment, Interest Payment, Principle Payment and Ending Balance" for time 1. Repeat the preceding step for time 2, so that you familiarize yourself with the calculation. Copy the cell formulas to the end of the mortgage term. If everything is done correctly, the Ending Balance should be obvious to you. (b) Salary Increase (G) Assume that your salary increases by 2% each year. You decide to increase your mortgage payments geometrically at the same rate every twelve payments. The monthly payment for the first year is the same as calculated in (a), and the last payment is smaller than usual. Calculate the total number of payments needed in order to pay off the entire mortgage. A column "Payment Scale (Column L) has been added which may be helpful. Input your final answer in cell C21
