cumprinc & cumipmt not matching reality

[Beverley]

Hi there,

I'm trying to calculate in Excel what Principle and Interest payment amounts
should be, and I can't get it to match what it actually is.

For example, I put in a loan amount of 138,987.50.

In real life, the first monthly principle payment was $200.99. Excel gives
it as $192.96.

The formula I've used for principle is
=ABS(CUMPRINC(6.25%/12,12*25,$B$3,B5,B5,0))

Where
- 6.25% is the annual interest rate, divided by 12 to give the monthly
interest rate
- 12 * 25 is the number of months in 25 years
- $b$3 is the original loan amount of 138,987.50
- b5 is the payment number (1, in this case to give the first payment)
- 0 is the timing of the payment.

I don't actually know what the timing is supposed to be, but when I change
it to 1, the value is out by even more -- it is 912.11 instead of the
expected (or hoped-for!) 200.99. So I think it must be correct as 0.

Am I right in using the original loan amount for Present Value? Or is it
supposed to be the original loan amount minus the total principle payments
made to this point? (Of course, in the first payment, that's the same, but
it would make a huge difference later on.)

Any suggestions?

Thanks,
Beverley

 

[Beverley]

Whoops, I made an error in the original posting. The original loan amount I
should have been using was 142,987.50 not 138...

First payment (reality) was the same as below (200.99)
First payment (according to excel) is $198.52 with timing = 0 or $938.36
with timing = 1

Difference is only $2.47 I know, but it's still wrong so I can't really use
the results.

Thanks,
Beverley

 

[Bernie Deitrick]

Beverly,

It is my experience that when Excel doesn't match reality, reality is wrong
;-)

Banks do make mistakes or have a strange payment basis, like using days
instead of months, etc.

With standard periods, your first payment of interest is

=ROUND(142987.5*0.0625/12,2)

or

$744.73

Your payments are

=ROUND(ABS(PMT(0.0625/12,25*12,A1,,0)),2)

or

$943.24

So your first principle payment is
$943.24 - $744.73

or $198.51

You'll get 198.52 if you don't use the rounding but, in my experience, banks
rarely accept fractions of pennies...

HTH,
Bernie
MS Excel MVP

 

[Michael Malinsky]

I came up with the same answers that you did using the PPMT and IPMT
functions. I also calculated loan amortizations using Excel's loan
amortization template and a loan amortization program. These also resulted
in the same first principal payments of $198.52. I would first ask how the
$200.99 was calculated. I would also point out that the Excel loan
amortization adjusted the final payment by $4.88 and the other program
adjusted the final interest payment by $3.49 due to rounding. Maybe the
rounding of the payments was loaded into the first payment or somehow
allocated among all payments?

I know this doesn't solve your problem, but this does verify that you are
using the Excel functions correctly.

Mike

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh

 

[hgrove]

Beverley wrote...
...

I'm trying to calculate in Excel what Principle and Interest
payment amounts should be, and I can't get it to match what it
actually is.

Where are you getting the 'actual' figures?

For example, I put in a loan amount of 138,987.50.

In real life, the first monthly principle payment was $200.99.

Define 'in real life'.

Excel gives it as $192.96.

The formula I've used for principle is
=ABS(CUMPRINC(6.25%/12,12*25,$B$3,B5,B5,0))

Where
- 6.25% is the annual interest rate, divided by 12 to give the
monthly interest rate
- 12 * 25 is the number of months in 25 years
- $b$3 is the original loan amount of 138,987.50
- b5 is the payment number (1, in this case to give the first
payment)
- 0 is the timing of the payment.

If your APR is 6.25%, then your monthly *effective* interest rate i
6.25%/12 = 0.00520833 (rounded). The monthly loan payment amount i
given by

=PMT(6.25%/12,25*12,-138987.5,0,0)

which returns 916.86. This agrees with interest theory, in which th
common monthly payment would be

138987.5 * (0.0625 / 12) / (1 - (1 + 0.0625 / 12) ^ -(25 * 12))

This assumes payments are made at the *end* of each month, which i
unusual for loans. From this, the interest portion of the first month'
payment is the effective monthly interest rate times the principa
balance, or 0.00520833 * 138987.5 = 723.89, and the principal portio
is the total payment minus the interest portion, or 916.86 - 723.89
192.97, which is just off $0.01 from rounding error.

Excel's results are consistent with interest theory *IF* the 6.25
annual interest rate is nominal compounded monthly. If your annua
interest rate is something else, so that the monthly effective rat
isn't just 1/12 if the stated annual rate, then you need to use you
*actual* monthly effective interest rate, and that could result in th
$200.99 first payment principal portion.

So what's your monthly payment, principal and interest combined

 

[Domenic]

Hi Beverley,

That's because, although your payments are made monthly, interest is
compounded semi-annually. Therefore, you need to use the effective
monthly interest rate for your calculations.

Monthly Payment=PMT(((6.25%/2+1)^(2/12)-1),12*25,B3)

Principal Portion of Payment=PPMT(((6.25%/2+1)^(2/12)-1),B5,12*25,B3)

Interest Portion of Payment=IPMT(((6.25%/2+1)^(2/12)-1),B5,12*25,B3)

Hope this helps!

 

[hgrove]

Beverley wrote...

Whoops, I made an error in the original posting. The original
loan amount I should have been using was 142,987.50 . . .

First payment (reality) was the same as below (200.99)
First payment (according to excel) is $198.52 with timing = 0 or
$938.36 with timing = 1

...

Looks like you have a Canadian mortgage. Canadian APRs aren't the sam
as US APRs, The former are nominal compounded semiannually, so you
total principal and interest monthly payment is given by

=PMT((1+6.25%/2)^(1/6)-1,12*12,-142,987.5,0,0)

which returns $936.20 (rounded), and the principal portion of the firs
payment is

=PPMT((1+6.25%/2)^(1/6)-1,1,12*12,-142,987.5,0,0)

which returns $200.99. So your discrepancy is due entirely to how th
monthly effective interest rate should be calculated. It's *NOT
6.25%/12. It's (1+6.25%/2)^(1/6)-1

 

[Beverley]

It is Canadian, yes.

I will try that, thank you all for replying!

Beverley

 

[Beverley]

That worked!

Okay... now I hate to be a pest, but could you please explain this formula?
Now that I have it working for monthly payments, I'd like to try it for
weekly payments.

(1+6.25%/2)^(1/6)-1

Obviously the 6.25% is my annual interest rate. Are the others constants,
or which number should be changed to go from monthly to weekly?

I've tried to find this formula on the internet, but I'm not sure exactly
what it is so that makes it tricky. I found one at
http://homepages.cambrianc.on.ca/tutorial/thetutorialcentre/mathscience/math/compound_interest.htm
that looks promising:

S = P (1 + I) ^ n
where:
S = the future value
P = the principal amount
I = the periodic rate of interest*
n = the # of compounding periods for the term of the loan

I = Nominal (Annual) Rate of Interest (j) = j / m
# of compounding periods per year (m)

Put those together and that gives
S = P (1 + (j/m))^n

So if I've got the right formula, and I'm reading yours correctly, that
would mean that you've said "m" = 2 and "n" = 1/6. Where do those numbers
come from? Also you subtracted 1 at the end -- which maybe shows I have not
found the right formula at all.

Thanks again!

Beverley

news:[email protected]...

So your discrepancy is due entirely to how the

 

[Domenic]

For weekly payments, the effective weekly interest rate is 0.001184226
or =(6.25%/2+1)^(2/52)-1

Monthly Payment:

=PMT((6.25%/2+1)^(2/52)-1,25*52,142987.5)

Principle portion of first payment:

=PPMT((6.25%/2+1)^(2/52)-1,1,25*52,142987.5)

Interest portion of first payment:

=IPMT((6.25%/2+1)^(2/52)-1,1,25*52,142987.5)

 

[Beverley]

Thank you very much!

One last question, and then I'll shut up. (For today)

How do you deal with extra (principle-only) payments? Should I be changing
what the original loan amount is used in the calculation?

For example, imagine I pay an extra $1000 one day. Would that mean I should
change the pv value from 142987.5 to 141987.5 for all dates after that, or
would it stay at the original value?

Beverley

 

[Domenic]

These formulas are based on the same monthly or weekly payments over the
term of the loan. So what you can do is set up a spreadsheet that will
take into account pre-payments.

Here's an example of how one can be set up for a loan that's paid
monthly:

Column Headings:

A1 = Period
B1 = Payment
C1 = Principal
D1 = Interest
E1 = Balance Owing

Terms of the loan:

G1 contains the loan amount
G2 contains the annual interest rate
G3 contains the amortization period (years)

Formulas:

A2 =IF(B2<>"",ROW()-ROW($A$2)+1,"") and copy down
C2 =IF(B2<>"",B2-G1*((G2/2+1)^(2/12)-1),"")
D2 =IF(B2<>"",G1*((G2/2+1)^(2/12)-1),"")
E2 =IF(B2<>"",G1-C2,"")

C3 =IF(B3<>"",B3-E2*(($G$2/2+1)^(2/12)-1),"") and copy down
D3 =IF(B3<>"",E2*(($G$2/2+1)^(2/12)-1),"") and copy down
E3 =IF(B3<>"",E2-C3,"") and copy down

Now, all you have to do is enter your payment, including any pre-payment
that may apply, for each month as they become due and you'll
automatically get a break down of principal, interest, and balance
outstanding.

Hope this helps!