You 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, you will need to make a $40,000 ending balloon payment. Because you expect your income to increase, you want to structure the loan so at the beginning of each year, your 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 the 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.06/12,240,-300000,0,0).