Home » Excel Spreadsheet » 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.

allf2

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

Enjoy

Download “AllFormula.xlsx

 

 

 

 

Leave a Reply

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

*
*