Question

A borrower takes out a 20-year mortgage for $500,000 with an interest rate of 6%. The loan requires monthly payments and has a 3% fee if the loan is repaid within 10 years. What is the effective interest rate on the loan if the borrower repays the loan after 72 payments?

Answer 1

loan amount or present value = 500000

interest rate per month or Rate=6%/12 = 0.5%

number of months of amortization or NPER =20*12 = 240

balance after 20 years or FV = 0

Monthly payment made Excel function = PMT(Rate, NPER, -PV, FV)

=PMT(0.5%, 240, -500000, 0)

=$3,582.16

Loan is repaid on 72 months end. So NPER remaining months = 240-72 =168

rate = 0.5%

PMT = $3582.16

balance after 20 years or FV = 0

Balance owned at 72 month = -PV(Rate, NPER, PMT, FV)

=-PV(0.5%, 168, 3582.16, 0)

=$406,492.73

fees paid = 3% of 500000 = 15000

Total amount paid at 72 month end that is future value for calculation of effective interest rate

Future value paid = 15000+406492.73 =421492.73

NPER or monthly payment made = 72

PMT = 3582.16

loan amount or Present value = 500000

Monthly rate formula = Rate(NPER, PMT. -PV, FV)

=Rate(72, 3582.16, -500000, 421492.73)

=0.5372433112%

Effective interest rate excel function = Effect(monthly rate* number of compounding in year,  number of compounding in year)

=Effect(0.5372433112%*12, 12)

=6.6408688003% or 6.64%

So effective annual interest rate on loan is 6.64%