B
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
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