How to build a loan amortization schedule in Excel and Google Sheets

Step by step

1. 1Enter your loan inputs: loan amount in B1 (e.g. 20000), annual interest rate in B2 (e.g. 0.06), and term in months in B3 (e.g. 36).
2. 2Calculate the fixed monthly payment in B4 using =PMT(B2/12,B3,-B1). The rate argument is annual/12; nper is the total months; pv is negative loan amount so the result is positive.
3. 3Build a schedule table starting in row 7. Put period numbers 1 through B3 in column A. In B7 enter the opening balance: =$B$1.
4. 4In C7 enter the interest portion: =IPMT($B$2/12,A7,$B$3,-$B$1). In D7 enter the principal portion: =PPMT($B$2/12,A7,$B$3,-$B$1). In E7 enter the closing balance: =B7+D7.
5. 5In row 8, set B8=E7 (prior closing balance), then copy C7:E7 down through the last period. The closing balance in the final row should equal zero (or a rounding residual).

Tips

• IPMT and PPMT take the period number (argument per) as the current row's period — do not confuse it with the total nper. Lock rate and nper references with $ so they do not shift when copying down.
• Floating-point rounding can leave a small residual in the last row. Clamp it with =MAX(E7+D8,0) on the final closing balance, or use ROUND on each payment component.
• To summarize total interest paid, use =SUM(C7:C42) on the interest column; compare it to total payments (=$B$4*$B$3) minus the original principal to verify the schedule balances.

Frequently asked