CUMPRINC
Add up the principal portion of a loan's payments between two periods — how much the balance fell.
Syntax
CUMPRINC(rate, nper, pv, start_period, end_period, type)| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period. |
nper | Required | The total number of payments. |
pv | Required | The amount borrowed, as a positive number. |
start_period | Required | The first payment to include, counting from 1. |
end_period | Required | The last payment to include. |
type | Required | 0 for payments at the end of each period, 1 for the start. Required. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=CUMPRINC(0.05/12,360,200000,1,12,0) | -2950.73 | The first year of a 200,000 mortgage at 5% pays down less than 3,000. |
=200000+CUMPRINC(0.05/12,360,200000,1,60,0) | 183657.46 | The balance still owed after five years. |
=CUMPRINC(0.05/12,360,200000,1,360,0) | -200000.00 | Over the whole term the principal repaid is the loan. The .00 is floating-point residue from summing 360 payments. |
Remaining balance
Add CUMPRINC to the original loan to get the balance after any number of
payments — the figure a lender quotes for paying off early or refinancing. It
comes back negative (principal repaid is money out), which is why it is added
rather than subtracted.
The first years barely move it
On a 30-year loan, twelve payments of 1,073.64 total 12,883.68, and only 2,950.73 of that reduces the balance. Setting the two cumulative functions side by side for each year shows how late in the term the principal payments catch up with the interest.
Like CUMIPMT, type is required and pv must be
positive.
Excel compatibility
Matches Excel, including the required type argument and #NUM! for a negative pv or a start after the end.