How to build a fixed payment schedule in Excel and Google Sheets

Step by step

1. 1Put the annual interest rate in B2 (e.g. 0.06 for 6%), total months in B3 (e.g. 36), and loan amount in B4 (e.g. 10000). Add labels in A2:A4.
2. 2In a schedule table, create a header row with columns: Period, Payment, Interest, Principal, Balance. Enter period numbers 1 through 36 in the Period column.
3. 3In the Payment column (e.g. F2), enter =PMT($B$2/12,$B$3,-$B$4). Lock all references so the formula stays constant when copied down.
4. 4In the Interest column enter =IPMT($B$2/12,E2,$B$3,-$B$4) where E2 is the period number. In the Principal column enter =PPMT($B$2/12,E2,$B$3,-$B$4). Copy both formulas for all 36 periods.
5. 5In the Balance column enter =$B$4+SUM($G$2:G2) (where G is the Principal column) to show the running outstanding balance. Verify the final balance rounds to zero.

Tips

• IPMT and PPMT require the period number as their second argument — reference the period column rather than hardcoding it, or every interest/principal split will be wrong.
• Sign convention matters: passing the loan as a positive PV returns a negative PMT. Negate PV with -B4 or wrap PMT in ABS to keep all schedule values positive.
• Validate the schedule by summing the Principal column — it should equal B4 exactly (within a rounding cent).

Frequently asked