Interest Payments

[rbiamonte]

Given a loan amount, I am trying to calculate the monthly payment; but I would also like to find the respective amounts related to principal and interest. Currently, I am using the pmt formula for the payment and the ispmt for the interest. After getting those two cell amounts, I was finding the difference for the principal. However, the total prinicpal paid does not tie back to the original loan amount. It is off by a few thousand dollars so I don't think it relates to rounding.

Thanks in advance,

Ron

[MickG]

Excel Forum, rbiamonte 13_8_08 (Pmt)
Hi, Ron
I have found that using the PMT function does not always return an accurate answer.
This is because the function requires you to divide by 12 for monthly repayments.
This is not how APR is calculated, as it compounds the Monthly rate over the period of the Loan.
As an example, if you use the PMT function on a £10,000 loan at an interest rate of 6.4 % over a 12 month repayment period. You will get a repayment amount of £862.5, where as the actual Repayment should be £861.67 (Figs taken from a reliable source)
If you use the formula below (For payments in arrears) you will get the correct answer.

Rearrange in standard form:-
P = Principle. r = Annual Interest Rate. R = (1+ r). S = Periodic repayment.
n = Period (mth etc.)

Please Login or Register  to view this content.

Note:- When dealing with Loans and Mortgages the normal repayment period is a month.
In these cases the 12th root of R should be used to represent a period of one month

In the example above if the interest rate was 10% then R would = 1.1 and would be replaced by 1.1^(1/12) = 1.00797414
If the period was 3 Years then "n" would read 36 equalling 3 x 12.
In the case (n + 1). 1 is always 1 irrespective of whether the periods are months or years.
NB:- For Payments in advance, alter the bottom line of the equation to:- R^(n+1) -R

NB:- With regard to the Function "IsPmt", I believe the value returned is the "Interest" at a specific point during the Loan, But this does not seem to equate with a Logical test.
If you just wanted the total interest, I should have thought Total repayments - Loan would be simpler.
Hope this is of some help
Regards Mick

[rbiamonte]

I tried the formula you provided - And my monthly payment seems to be really high. I have attatched both my original sheet as well as the formula you gave. However the more I look at mine (sheet 1), my total principle amount is off only by the last payment.

Thanks in advance for your help Mick -

Ron

[MickG]

Hi, Ron
Hopefully I've attached a modified copy of your file .
You will see at the top of the sheet, the formula Results, and on the extreme right of your Data a new column of Data showing the Remaining amount left on the loan after each Month after the dedection of the formula amount. At the Bottom you will see the Last Row shows Nothing remaining.
See what you make of it
Regards Mick

[rbiamonte]

Thanks for your help this worked

[seckert]

I hate to contradict the other excel pros, but it's actually far simpler than this (I do this for a living).

For a standard mortgage, which it looks like you have here, the excel payment function works perfectly. The rate stated on a any financial instrument not APR, so don't worry about that. Divide the yearly rate by 12 and that's your monthly rate.

Payment (constant) = pmt(rate/12, term, -Bal(0) )
Int(1) = Bal(0) * rate / 12
Bal(1) = Bal(0) + Int(1) - Payment

This will amortize your loan to zero withing a few pennies. Round as you like.