I published the math behind the CUMIPMT() & CUMPRINC() functions at the bottom of my spreadsheet for the post Annuity – Excel Functions and Math. I think it got lost in all the annuity math. When I Google the math for these two functions, my blog doesn’t show up until the 6th or 7th page, depending on the wording of the search. I see the question asked without a real answer, so I am posting the formulas again by themselves. I am also doing it because it is my blog and I can.

Download “CUM“

Also the math for a single row of a simple amortization schedule:

Download “CUM“

HI there. Thank you for all of your work!

When I plug your calculation for m (p*i/(12*(1-(1+(i/12))^-n))) into your mathematical formula for the CUMIPMT function in Excel, I get

=(((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^(b-1)+p*i/(12*(1-(1+(i/12))^-n))*q/i)-((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^e+p*i/(12*(1-(1+(i/12))^-n))*q/i))-p*i/(12*(1-(1+(i/12))^-n))*(e-b+1)

This seems to work fine as long as the Payments Per Year remains 12. If I change the Payments Per Year to 6 for example, the Excel CUMIPMT function does not equal what

=(((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^(b-1)+p*i/(12*(1-(1+(i/12))^-n))*q/i)-((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^e+p*i/(12*(1-(1+(i/12))^-n))*q/i))-p*i/(12*(1-(1+(i/12))^-n))*(e-b+1)

resolves to. How could I make this formula work for 6 Payments Per Year instead of 12?

Thank you!!

Hi Kendall,

The formula is based on a normal mortgage of 12 monthly payments per year. If you change the months to 6 from 12, you need to adjust one of the other four inputs. For example, if you calculate a new payment based on the other data inputs in yellow, the formula will match the function.

If I set up the mathematical formula for the CUMIPMT to include the calculation for m, I get:

=(((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^(b-1)+p*i/(12*(1-(1+(i/12))^-n))*q/i)-((p-p*i/(12*(1-(1+(i/12))^-n))*q/i)*(1+(i/q))^e+p*i/(12*(1-(1+(i/12))^-n))*q/i))-p*i/(12*(1-(1+(i/12))^-n))*(e-b+1)

This formula seems to work fine if the Payments Per Year (q) are 12. If I have 6 payments per year, this formula does not calculate the same value as the CUMIPMT function in excel. Any help with this would be greatly appreciated!! Thank you!

Hello!

How did you get payment $2302?

I’ve got 41666 by this calc 100000*5/(12*(1-pow((1+(5/12)),-48)))

hello!

how did you get payment $2302?

is there anything wrong in my calculations 100000*5/(12*(1-pow((1+(5/12)),-48)))?

I get 41667 everytime

=PMT(0.05/12,48,100000)=2,302.93

=100000*0.05/(12*(1-(1+(0.05/12))^-48))=2,302.93

There is any way to use CumIPmt in a matrix range?

Ex: {SUM(CumIPmt (i/q;n;p;b;e;0)}

Hi Jose,

I’m not sure what you are asking for. Could you send me an Excel workbook with an example of what you would like the matrix to look like?

blog@pistulka.com

Don

Dear Don

Based on your excel file, CUMPRINC requires i,q,n,p,b,e for parameters. But your function requires i,q,m,p,b,e. How to convert “n” parameter in CUMPRINC to “m” parameter in cell G14/G17

Converting the Excel inputs to m only needs p, i, and n.

m=p*i/(12*(1-(1+(i/12))^-n))

Don

If we have rate of interest calculated on monthly basis, does this become

m = balance * apr / (1 – 1 / ((1 + (apr)) ** term))

Atul,

Almost,

m=balance*apr/12/(1-1/(1+apr/12)^term)

Using the default data on my example:

2,302.93=p*i/12/(1-1/(1+i/12)^n)

Unless you meant that the rate was divided by 12, before calculating the formula. Then your formula would work.

Don

Hello!

How did you get payment $2302?

this 100000*5/(12*(1-pow((1+(5/12)),-48))) gives me 41666.66895195165 not 2302

Hi

The CUMIMPT formula does not make use of the payment amount (m), however, your mathematical formula does use it. As much as you are getting the same result as the formula, I would imagine there is a different mathematical formula powering the function.

Hi Keldon,

The reason the Excel formula does not ask for M (payment) is that the inputs it does ask for are enough to calculate the payment. So asking for the payment is not necessary.

Don

amazing!!!! Thank YOU!!!!

Thanks You

thank you.

You’re welcome Shawnta,

This post may be helpful also:

Math for amortization rows with CPR, Servicing, CDR, & Loss SeverityMath for amortization rows with CPR, Servicing, CDR, & Loss Severity

[…] The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions July 22, 2015 […]

Exactly what I was looking for. Thanks!

Glad I could help