Excel Amortization Schedule Generator
Build a loan amortization schedule with a payment row for every period, split into interest and principal.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Loan Amortization Schedule | ||||||
| 2 | |||||||
| 3 | Loan amount | 250000 | Payment | =-PMT(B4/B6,B5*B6,B3) | |||
| 4 | Annual rate | 0.055 | Total paid | =SUM(C9:C368)+SUM(D9:D368)+SUM(F9:F368) | |||
| 5 | Term (years) | 30 | Total interest | =SUM(C9:C368) | |||
| 6 | Payments per year | 12 | |||||
| 7 | |||||||
| 8 | Payment # | Payment | Interest | Principal | Balance | Extra payment | |
| 9 | 1 | =IF(B3<=0,"",MIN(E3,B3+B3*B4/B6)) | =IF(B3<=0,"",B3*B4/B6) | =IF(B3<=0,"",B9-C9) | =IF(B3<=0,"",MAX(0,B3-D9-N(F9))) | ||
| 10 | 2 | =IF(E9<=0,"",MIN(E3,E9+E9*B4/B6)) | =IF(E9<=0,"",E9*B4/B6) | =IF(E9<=0,"",B10-C10) | =IF(E9<=0,"",MAX(0,E9-D10-N(F10))) | ||
| 11 | 3 | =IF(E10<=0,"",MIN(E3,E10+E10*B4/B6)) | =IF(E10<=0,"",E10*B4/B6) | =IF(E10<=0,"",B11-C11) | =IF(E10<=0,"",MAX(0,E10-D11-N(F11))) | ||
| 12 | 4 | =IF(E11<=0,"",MIN(E3,E11+E11*B4/B6)) | =IF(E11<=0,"",E11*B4/B6) | =IF(E11<=0,"",B12-C12) | =IF(E11<=0,"",MAX(0,E11-D12-N(F12))) | ||
| 13 | 5 | =IF(E12<=0,"",MIN(E3,E12+E12*B4/B6)) | =IF(E12<=0,"",E12*B4/B6) | =IF(E12<=0,"",B13-C13) | =IF(E12<=0,"",MAX(0,E12-D13-N(F13))) | ||
| 14 | 6 | =IF(E13<=0,"",MIN(E3,E13+E13*B4/B6)) | =IF(E13<=0,"",E13*B4/B6) | =IF(E13<=0,"",B14-C14) | =IF(E13<=0,"",MAX(0,E13-D14-N(F14))) | ||
| 15 | 7 | =IF(E14<=0,"",MIN(E3,E14+E14*B4/B6)) | =IF(E14<=0,"",E14*B4/B6) | =IF(E14<=0,"",B15-C15) | =IF(E14<=0,"",MAX(0,E14-D15-N(F15))) | ||
| 16 | 8 | =IF(E15<=0,"",MIN(E3,E15+E15*B4/B6)) | =IF(E15<=0,"",E15*B4/B6) | =IF(E15<=0,"",B16-C16) | =IF(E15<=0,"",MAX(0,E15-D16-N(F16))) | ||
| 17 | 9 | =IF(E16<=0,"",MIN(E3,E16+E16*B4/B6)) | =IF(E16<=0,"",E16*B4/B6) | =IF(E16<=0,"",B17-C17) | =IF(E16<=0,"",MAX(0,E16-D17-N(F17))) | ||
| 18 | 10 | =IF(E17<=0,"",MIN(E3,E17+E17*B4/B6)) | =IF(E17<=0,"",E17*B4/B6) | =IF(E17<=0,"",B18-C18) | =IF(E17<=0,"",MAX(0,E17-D18-N(F18))) | ||
| 19 | 11 | =IF(E18<=0,"",MIN(E3,E18+E18*B4/B6)) | =IF(E18<=0,"",E18*B4/B6) | =IF(E18<=0,"",B19-C19) | =IF(E18<=0,"",MAX(0,E18-D19-N(F19))) | ||
| 20 | 12 | =IF(E19<=0,"",MIN(E3,E19+E19*B4/B6)) | =IF(E19<=0,"",E19*B4/B6) | =IF(E19<=0,"",B20-C20) | =IF(E19<=0,"",MAX(0,E19-D20-N(F20))) | ||
| 21 | 13 | =IF(E20<=0,"",MIN(E3,E20+E20*B4/B6)) | =IF(E20<=0,"",E20*B4/B6) | =IF(E20<=0,"",B21-C21) | =IF(E20<=0,"",MAX(0,E20-D21-N(F21))) | ||
| 22 | 14 | =IF(E21<=0,"",MIN(E3,E21+E21*B4/B6)) | =IF(E21<=0,"",E21*B4/B6) | =IF(E21<=0,"",B22-C22) | =IF(E21<=0,"",MAX(0,E21-D22-N(F22))) |
Preview of the first 22 rows. Cells shown as =SUM(...) are live formulas in the downloaded workbook, not pasted numbers.
What you get
- •A summary block with monthly payment, total interest, and total cost - all formulas.
- •One row per payment showing interest, principal, and the remaining balance.
- •An optional extra-payment column that shortens the schedule as you fill it in.
- •Built on PMT, IPMT and PPMT, so changing the rate or term updates every row.