Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to build a loan amortization schedule in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=PMT(B2/12, C2*12, -A2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Loan Amount | Interest Rate | Loan Term (years) |
| 200000 | 0.05 | 30 |
| Monthly Payment |
=PMT(B2/12, C2*12, -A2)→1073.6432460243
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your loan amount, interest rate, and loan term in separate cells.
- 2Use the PMT function to calculate the monthly payment.
- 3Create columns for payment number, payment date, principal, interest, balance, and fill them using formulas.
Tips
- Ensure your interest rate is entered as a decimal (e.g., 5% as 0.05).
- Adjust the date format in your payment date column for clarity.
- Check for locale-specific differences in function syntax if using non-English versions.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What does the PMT function do?
The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.
How do I adjust for different compounding periods?
For different compounding periods, adjust the interest rate and number of periods accordingly in the PMT function.
Can I use this schedule for adjustable-rate loans?
No, this schedule is for fixed-rate loans. Adjustable-rate loans require more complex calculations.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.