Math for amortization rows with CPR and Servicing


After you read this post, check out my updated post


In my post called The Math Behind Excel’s CUMIPMT() & CUMPRINC() Functions I showed how to calculate a single row of a simple amortization schedule, using math instead of the amortization schedule. In this post and spreadsheet I show how to calculate the starting balance, payment, interest, principal, servicing, CPR prepayment, and ending balance with only math. What application does this have in the real world, you ask? I don’t know, but the math was fun.

The inputs are in yellow cells:

AllF1The named cells are next to the inputs. The “Payment#” is the payment number you want to show the balances. Next are two rows of payment number 16. The top one is a simple lookup of the amortization schedule and the bottom one are the formulas only. The top one is just a check against the formulas.


The formulas were easy after the starting and ending balance formulas where worked out.


Download “AllFormula.xlsx





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.

Leave a Reply

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