The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions

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

 

cum1

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

cum2Download “CUM

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

9 Comments

  1. 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.

    1. 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

Leave a Reply to Math for amortization rows with CPR and Servicing | Excel@CFO Cancel reply

Your email address will not be published. Required fields are marked *