A loan amortization schedule spreadsheet shows each payment’s interest, principal, and remaining balance over time. In Excel for Microsoft 365, enter the loan amount, annual interest rate, and number of monthly payments, calculate the payment with =PMT(B3/12,B4,-B2), then build rows that apply interest to the opening balance and subtract principal paid.
What a loan amortization schedule shows
Amortization is the gradual repayment of a loan through scheduled payments. A typical fixed-rate installment loan payment covers interest due for the period and pays down some principal. The schedule lays out that process one period at a time, so you can see why the balance declines and how the split between interest and principal changes.
Early in a standard level-payment loan, the balance is relatively high, so the interest portion is larger. As principal is repaid, later interest charges are smaller and more of each regular payment goes toward principal. The payment amount can stay level even though its components change. The final payment may need to be adjusted slightly to clear the balance, especially when amounts are displayed rounded to cents.
A schedule is useful for understanding a loan, checking a proposed repayment pattern, and comparing scenarios. It is an estimate based on the inputs and assumptions you give it, not a replacement for the lender’s official payoff figure or loan documents.
Enter the loan details in Excel
For a simple monthly, fixed-rate example, set up these labels and values in a blank worksheet. The cell locations below are used by the formulas throughout the guide.
- A2: Loan amount; B2: 20000
- A3: Annual interest rate; B3: 6%
- A4: Number of monthly payments; B4: 36
- A5: Monthly payment; B5: calculated with PMT
Enter 6% as a percentage, not as 6. Enter the contractual annual interest rate used to calculate periodic interest, rather than assuming that a loan’s advertised APR is always the right input. APR may reflect fees or other costs and may not be the rate used to calculate each monthly charge. Check the lender’s documents for the applicable rate and payment frequency.
With the loan amount in B2, annual rate in B3, and total number of monthly payments in B4, enter this formula in B5. It returns a positive payment of about $608.44 for the illustrative $20,000, 6%, 36-month loan:
=PMT(B3/12,B4,-B2)
PMT calculates a level payment using a constant rate and number of periods. The negative sign on the loan amount is a cash-flow sign convention: it makes the payment display as a positive number. The calculation assumes payments are made at the end of each period, which is the usual assumption for a standard monthly installment schedule. If the loan has a different payment timing or changing rate, the simple setup may not match it.
Build the payment schedule row by row
Create headers in row 9: A9 Period, B9 Beginning balance, C9 Payment, D9 Interest, E9 Principal, and F9 Ending balance. The first payment is row 10. Use the following formulas, then copy the recurring formulas down for the number of payments. For 36 payments, the schedule runs from row 10 through row 45.
With A10 as the first period number, enter this formula in A10:
=1
With A10 containing period 1, enter this formula in A11 and fill down to number subsequent periods:
=A10+1
With the original principal in B2, enter this formula in B10 to set the first period’s beginning balance:
=$B$2
With the previous row’s ending balance in column F, enter this formula in B11 and fill down:
=F10
With the period’s opening balance in B10 and the annual rate in B3, enter this formula in D10 and fill down to calculate monthly interest:
=B10*$B$3/12
With the scheduled payment in B5, opening balance in B10, and interest in D10, enter this formula in C10 and fill down. It caps the last payment at the remaining balance plus that period’s interest, rather than charging a full payment after the loan is paid off:
=MIN($B$5,B10+D10)
With the payment in C10 and interest in D10, enter this formula in E10 and fill down to calculate principal repaid:
=C10-D10
With the opening balance in B10 and principal repaid in E10, enter this formula in F10 and fill down to calculate the closing balance:
=MAX(0,B10-E10)
For the example, the first month’s interest is $100.00: $20,000 multiplied by 6% and divided by 12. The payment is about $608.44, so roughly $508.44 goes to principal and the displayed ending balance is about $19,491.56. These displayed figures are rounded to cents; Excel retains more precision in the underlying calculations unless you change the workbook’s calculation settings. Do not use the display setting that forces precision to the shown number of decimal places as a substitute for a considered rounding method.
Format and check the worksheet
Format B2 and columns B through F as currency with two decimal places, and format B3 as a percentage. In Excel for Microsoft 365 on Windows, select the relevant cells and use Home > Number to choose an appropriate number format. The same core formulas work in Excel for Mac and Excel for the web, though the ribbon layout can differ slightly. Formatting changes how values appear, not the precision of the stored calculation.
Check that the period numbers match the agreed payment count, the opening balance after the first row equals the original loan amount, and each ending balance becomes the next row’s beginning balance. At the end, the ending balance should be zero or extremely close to zero. A tiny residual can result from floating-point arithmetic; the final-payment cap in the example prevents a small negative balance, but it does not replace reviewing the final amount against the lender’s rules.
You can also check the schedule’s basic logic: payment equals interest plus principal for each row, and the balance falls when principal is paid. If the interest rate is positive, the interest amount should generally decline as the balance declines. The first month’s interest is a particularly helpful manual check because it is easy to calculate independently.
Adding extra principal payments
An extra payment can shorten the modeled payoff period and reduce future interest, but only if it is applied to principal and the lender permits that treatment without a relevant fee or restriction. It is important to separate extra principal from the scheduled payment rather than quietly changing the regular payment amount.
For a basic scenario, add a column called Extra principal in G9 and enter a planned extra amount in each payment row. In a copy of the schedule, change the formulas so the extra amount reduces the balance in addition to the scheduled principal. Make sure the total amount applied in the final period does not exceed the amount needed to clear the balance. Extra-payment timing matters: an amount paid earlier can affect later interest differently from the same amount paid near the end. Some lenders also use a different process for applying additional funds, so verify the treatment before relying on a projection.
If extra payments are irregular, model them in the relevant rows rather than assuming the same extra amount every month. Keep a separate version of the base schedule so you can compare the original repayment plan with the scenario. A spreadsheet projection does not establish that a lender has received or correctly applied an actual payment.
Assumptions that can change the result
The simple example assumes a fixed annual rate, monthly periods, equal end-of-month payments, and interest calculated as the annual rate divided by 12. Real contracts can differ. A variable-rate loan can change its rate during the term; a daily-interest loan can depend on the number of days between payments; and some loans have fees, an initial interest-only period, a balloon payment, or irregular payment dates. A basic PMT schedule will not reproduce those details unless the model is adapted to them.
Payment frequency also matters. For a weekly or biweekly loan, do not simply reuse the monthly divisor and period count. Use the rate per contractual period and the number of contractual payments, following the lender’s calculation method. An annual percentage rate, nominal rate, effective annual rate, and daily periodic rate are not interchangeable labels. When the terms are unclear, ask the lender how interest and payments are calculated rather than guessing.
Rounding can create small differences as well. A lender may round interest each period to cents, while a spreadsheet may retain fractional cents internally and round only what is displayed. A final payment may therefore differ by a few cents, or more if the model’s assumptions are substantially different. For an actual payoff amount, request a payoff quote from the lender that accounts for the payment date and any applicable charges.
Common spreadsheet errors and troubleshooting
- Entering 6 instead of 6%: A rate of 6 entered as a number means 600% in this setup. Enter 6% or 0.06.
- Using years instead of payment periods: B4 in this example is 36 monthly payments, not 3 years. If you use a years input, the period count must be converted consistently.
- Forgetting the rate period: The formula divides the annual rate by 12 because payments are monthly. A mismatch between rate and payment frequency produces an incorrect payment and interest split.
- Using the wrong balance in the interest formula: Each row’s interest is calculated from that period’s beginning balance, not its ending balance.
- Copying formulas without checking references: The dollar signs in references such as $B$3 keep the annual rate fixed when formulas are filled down. References to the current row should change as the formula is copied.
- Assuming the displayed cents are the stored values: Currency formatting can hide fractions of a cent. This can explain a small difference in the final row.
- Mixing fees into principal without a reason: If fees are financed, the loan balance may be larger than the amount received. Model that only when the contract actually includes the fee in the financed balance.
If a payment appears as a negative number, check the signs of the cash-flow inputs to PMT. If the balance grows instead of falling, inspect whether the interest rate is too high, the payment is too small, or principal is being subtracted in the wrong direction.
Spreadsheet versus lender schedule
A self-built spreadsheet is flexible: you can change the term, compare extra-payment scenarios, and see the payment breakdown without manually recalculating every period. A lender’s amortization schedule, however, is tied to the actual contract and servicing rules. It may account for dates, rate changes, fees, payment posting, or rounding practices that the generic worksheet leaves out.
Use the spreadsheet as an explanatory model and planning aid, not as a definitive record of what is owed. Before acting on a payment estimate, payoff amount, or prepayment assumption, check the loan documents and confirm the relevant details with the lender or a qualified professional. This is general spreadsheet information, not financial, tax, investment, or legal advice.
Frequently Asked Questions
Can Excel calculate a loan amortization schedule automatically?
Excel’s PMT function calculates a regular payment, but it does not by itself create every row of an amortization table. Use PMT for the payment and formulas for each period’s interest, principal, and remaining balance, then fill those formulas down.
What formula calculates the monthly loan payment?
With the annual interest rate in B3, number of monthly payments in B4, and loan amount in B2, use =PMT(B3/12,B4,-B2). This assumes a fixed rate and monthly payments at the end of each period.
Why does my amortization schedule not end at exactly zero?
Common causes include displaying rounded values, using a rate or payment frequency that differs from the loan terms, and floating-point precision. The last payment may need an adjustment. Compare the model with the lender’s calculation method and obtain a lender payoff quote for an actual balance.
Does making extra payments always reduce interest?
Extra principal payments generally reduce the balance used to calculate future interest in a standard amortizing loan, but the timing and effect depend on the contract and how the lender applies the money. Check for restrictions, fees, and payment-application rules before using a projection.
Can I use this spreadsheet for a variable-rate loan?
Not unchanged. A fixed-rate PMT schedule assumes the rate stays constant. A variable-rate model needs to reflect when the rate changes and how the payment is recalculated under the loan terms. Confirm those rules with the lender.
Related
- Free Wedding Budget Spreadsheet Australia: What to Include
- PowerPoint Slideshow Loop: Set It to Repeat Automatically
- Free Wedding Budget Spreadsheet: Build and Use Yours
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Analysis: Where to Click and How to PracticeTo run Excel’s Analysis ToolPak on Windows, choose Data > Data Analysis, select a tool, and enter the…
- How to Double Space in Word for the Web: A Quick GuideTo double-space text in Word for the web, select the text, open Home > Line and Paragraph Spacing,…
- Data Validation in Excel: Rules, Lists, and Common FixesIn Excel for Microsoft 365 on Windows, select the cells you want to control, then choose Data >…
- Google Sheets Jira Add-On: Setup, Features, and LimitsYes—Atlassian’s Jira Cloud for Sheets add-on connects Jira Cloud with Google Sheets so you can bring Jira issues…
- Excel vs CSV Row Limits: How Many Rows Can Each Hold?Excel allows up to 1,048,576 rows per worksheet. CSV has no fixed row limit in its format, so…
- Excel Date Conversion Methods for Text and Serial ValuesTo convert dates in Excel, first determine whether the cells contain real date serial numbers or text: format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.