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:
The 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.