Creating your own amortization schedule requires only a few functions. This post walks you through creating the schedule from scratch.
The yellow cells are user input cells. The Payment cell has this formula
Because I am using a positive present value in the PMT() function, I negate the whole function to return a positive payment.
Period I just filled a series of numbers down to 360. You could make a formula for this, but I never do. If I wanted to make it dynamic, I would conditionally format the rows to make the text color the same as the background color if it exceeds the term.
The interest and payment are calculated, so this column simply subtracts them.
Multiply the loan balance by the interest rate divided by 12. The important thing to remember when building an amortization schedule is to keep your periods consistent. The payments are monthly, so the interest rate is converted to months. In the PMT() function above, the interest rate is also divided by12 and the nper (in years) is multiplied by 12.
That one’s pretty easy. I’ve computed the payment above and it doesn’t change, so I refer to that calculation.
The old balance less the principal reduction for this payment. E7 refers to D1, the starting balance.
Now fill all these formulas down as far as you need. The ending balance probably won’t be zero. This is a pretty simplified amortization. The bank likes to complicate things with 360 day years and APR’s, but I like to keep it simple.