Question

Assume you want to take out a $300,000 loan on a 25-year mortgage with end of month payments. The annual rate of interest is 8 percent. 20 years from now, you need to make an ending additional lump sum payment of $45,000. Because you expect your income to increase, you want to structure the loan so that the beginning of each year your monthly payments increase by 2.5 percent. Determine the amount of each year’s monthly payment with Excel Solver.

Answer 1

This is an end- of- month growing annuity paid on the mortgage whose

Present value is $ 300000 for a period of

20 yrs.*12=240 months

at a monthly interest rate of 8%/12=0.6667% or 0.006667 in decimal form

this pmt.growing at a rate of 2.5%,ie.0.025

with a lumpsum payment of $ 45000 at the end of

20 yrs.*12= 240 th month

With the above inputs,

Amount of each year’s monthly payment can be found

by using the following PV of growing annuity formula,

PV=(Pmt./(r-g))*(1-((1+g)/(1+r))^n)

where ,

PV=the present value of the loan,ie. $ 300000

Pmt.=the equal monthly payment that is to be found out---?

r= interest rate per period, ie. Month,ie.0.006667

g=growth rate,ie. 2.5% or 0.025

n=no.of months,ie. 240

Inputting the values,in the formula,

300000=(Pmt./(0.006667-0.025))*(1-((1+0.025)/(1+0.006667))^240)+(45000/(1.006667)^240)

we get the starting monthly pmt. As

71.05

300011