Re: cumprinc & cumipmt not matching reality

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: hgrove (hgrove.19i06r_at_excelforum-nospam.com)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 12:55:41 -0500

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 is
6.25%/12 = 0.00520833 (rounded). The monthly loan payment amount is
given by

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

which returns 916.86. This agrees with interest theory, in which the
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 is
unusual for loans. From this, the interest portion of the first month's
payment is the effective monthly interest rate times the principal
balance, or 0.00520833 * 138987.5 = 723.89, and the principal portion
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 annual
interest rate is something else, so that the monthly effective rate
isn't just 1/12 if the stated annual rate, then you need to use your
*actual* monthly effective interest rate, and that could result in the
$200.99 first payment principal portion.

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

---
Message posted from http://www.ExcelForum.com/

Relevant Pages

  • Re: Calculating balance due on a loan
    ... The net present value of the loan. ... A payment schedule covering principle, interest, and any balloon payment ... If you have a conventional loan agreement, each payment is applied first to ...
    (microsoft.public.mac.office.excel)
  • RE: Excel Spreadsheet
    ... Principle Int Rate # of days Int Billed Prime Rate Prime amount ... Nper is the total number of payment periods in an annuity. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: CUMPRINC Function Returns #N/A
    ... Norman, This isn't a balloon. ... It is a credit card payment ... amount to pay down the second debt. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Have Interest Only -- Should I Refinance to 30 year Fixed?
    ... > and closing costs by just figuring out how much extra you can afford to ... > each payment as payment toward the principal unless the loan docs forbid ... principle to an interest only payment, ...
    (misc.consumers.house)