Question

One way banks may try to reduce prepayment associated with mortgage loans is to charge a “prepayment penalty” if you pay off the loan during a penalty period - some charge the fee only for a refinance, others for any reason say during the first 5 years of the loan.   The bank may offer lower origination fees in exchange for the penalty.   Assume the initial loan of $250,000 is for a 30 year FRM at 4%.

Let’s assume the penalty is 6 months of interest for prepayment. After 1 year (end of month 12) you decide to move – What is the amount of the penalty you would pay, i.e. the total interest on months 13 – 18 you would have paid? (Round to the nearest cent)

Answer 1

Using function IPMT in excel

Loan amount	250000
Interest rate	4%
time (yrs)	30
Monthly interest rate	0.33%
No. Of Months	360
Monthly payment	1,193.54
Interest in 13th months	818.66
Interest in 14th months	817.41
Interest in 15th months	816.15
Interest in 16th months	814.90
Interest in 17th months	813.63
Interest in 18th months	812.37
Total interest from 13-18th month	4,893.12

Showing formula in excel

Loan amount	250000
Interest rate	0.04
time (yrs)	30
Monthly interest rate	=K44/12
No. Of Months	=K45*12
Monthly payment	=PMT(K46,K47,-K43)
Interest in 13th months	=IPMT($K$46,13,$K$47,-$K$43)
Interest in 14th months	=IPMT($K$46,14,$K$47,-$K$43)
Interest in 15th months	=IPMT($K$46,15,$K$47,-$K$43)
Interest in 16th months	=IPMT($K$46,16,$K$47,-$K$43)
Interest in 17th months	=IPMT($K$46,17,$K$47,-$K$43)
Interest in 18th months	=IPMT($K$46,18,$K$47,-$K$43)
Total interest from 13-18th month	=SUM(K50:K55)
Loan amount	250000
Interest rate	0.04
time (yrs)	30
Monthly interest rate	=K44/12
No. Of Months	=K45*12
Monthly payment	=PMT(K46,K47,-K43)
Interest in 13th months	=IPMT($K$46,13,$K$47,-$K$43)
Interest in 14th months	=IPMT($K$46,14,$K$47,-$K$43)
Interest in 15th months	=IPMT($K$46,15,$K$47,-$K$43)
Interest in 16th months	=IPMT($K$46,16,$K$47,-$K$43)
Interest in 17th months	=IPMT($K$46,17,$K$47,-$K$43)
Interest in 18th months	=IPMT($K$46,18,$K$47,-$K$43)
Total interest from 13-18th month	=SUM(K50:K55)