Assume we want to take out a $300,000 loan on a 20-

year mortgage with end-of-month payments. The annual rate of interest is 6%. Twenty years from now, we need to make a $40,000 ending balloon payment.

Because we expect our income to increase, we want to structure the loan so at the beginning of each year, our monthly payments increase by 2%.

a. Determine the amount of each year’s monthly payment. You should use a lookup table to look up each year’s monthly payment and to look up the year based on the month (e.g., month 13 is year 2, etc.).

b. Suppose payment each month is to be same, and there is no balloon payment. Show that the monthly payment you can calculate from your spreadsheet matches the value given by the Excel PMT function PMT(0.0612,240,300000,0,0).