Question

Mr. Lamb borrowed $85,000 at 11.40% compounded monthly. He agreed to repay the loan in equal monthly payments over 15 years. What is the size of the monthly payment rounded up to nearest cent? How much of the 24th payment is interest? How much of the 137th payment goes towards principal? How much principal was paid down in the third year? Now assume that in part (a) you had rounded the payments down to the nearest dollar, what would be the size of the final payment?

Answer 1

a]

Monthly loan payment is calculated using PMT function in Excel :

rate = 11.4% / 12   (converting annual rate into monthly rate)

nper = 15*12 (15 year loan with 12 monthly payments each year)

pv = 85000 (loan amount)

PMT is calculated to be $987.56

b]

The interest amount in the 24th month payment is calculated using IPMT function in Excel :

rate = 11.4% / 12   (converting annual rate into monthly rate)

per = 24 (we are calculating the interest amount in the 24th month payment)

nper = 15*12 (15 year loan with 12 monthly payments each year)

pv = 85000 (loan amount)

IPMT is calculated to be $763.76

c]

The principal amount in the 137th month payment is calculated using IPMT function in Excel :

rate = 11.4% / 12   (converting annual rate into monthly rate)

per = 137 (we are calculating the principal amount in the 137th month payment)

nper = 15*12 (15 year loan with 12 monthly payments each year)

pv = 85000 (loan amount)

PPMT is calculated to be $651.46

d]

We calculate the principal paid down in the 3rd year (between 25th and 36th months) using CUMPRINC function in Excel :

rate = 11.4% / 12   (converting annual rate into monthly rate)

nper = 15*12 (15 year loan with 12 monthly payments each year)

pv = 85000 (loan amount)

start period = 25 (We are calculating principal paid off between 25th and 36th month)

end period = 36 (We are calculating principal paid off between 25th and 36th month)

type = 0 (each payment is made at the end of month)

CUMPRINC is calculated to be $2,857.43

Principal paid down in the 3rd year is $2,857.43