Question

Set up an amortization schedule that amortizes $500,000 with 5 years of cyclical quarterly payments. The annual interest rate is 6%. The payment schedule is: " one-third a normal payment" at the end of quarter 1, "normal payments" at the end of quarters 2 and 3, and "50% of a normal payment" at the end of quarter 4.

please solve in excel and list equations that go in the cells. Thank you :)

Answer 1

Normal payment is calculated using PMT function in Excel :

rate = 6% / 4 (converting annual rate into quarterly rate)

nper = 5 * 4 (5 years with 4 quarterly payments each year)

pv = -500000 (beginning principal)

PMT is calculated to be $29,122.87

Now, the amortization schedule looks as below :

Now, we use goal seek to change the value of "normal payment" so that the principal outstanding at the end of 5 years is zero, i.e. the loan is fully amortized

"Normal payment is calculated to be $41,170.60