To calculate loan amortization in Excel, enter the loan amount in B1, annual interest rate in B2, and term in years in B3; with those inputs, the monthly payment formula is =-PMT(B2/12,B3*12,B1). Then create a row for each payment, separating interest from principal so the balance declines over time.
What loan amortization means
An amortizing loan is repaid through regular payments that cover both interest and part of the amount borrowed. Interest is calculated on the outstanding balance, so the interest portion is generally larger near the start of a fully amortizing, fixed-rate loan. As the balance shrinks, less interest accrues each period and more of the same payment goes toward principal.
An amortization schedule lays out this process one payment at a time. It commonly shows the payment number, opening balance, total payment, interest, principal repaid, and closing balance. It is useful for understanding how a payment is split, estimating the balance at a future date, and checking how a changed rate or term affects the payment. It is a model, however, not a substitute for the lender’s official payoff statement or payment schedule.
Set up the loan inputs
For a simple monthly, fixed-rate example, enter these values in a blank Microsoft Excel for Microsoft 365 workbook on Windows:
- B1: 20000, the principal borrowed.
- B2: 6%, the annual interest rate used in this example.
- B3: 5, the term in years.
- D2: the calculated monthly payment.
Label the cells in column A so the workbook remains readable: put Loan amount in A1, Annual rate in A2, Term in years in A3, and Monthly payment in C2. Format B1 and D2 as currency and B2 as a percentage if helpful. These are display choices; formatting does not change the underlying calculation.
This example assumes the stated 6% rate is a nominal annual rate divided into 12 equal monthly periods, payments are monthly, the first payment is one month after borrowing, and there are no fees or other charges. Those assumptions make the arithmetic easy to follow, but actual contracts can use different timing or rate conventions.
Calculate the fixed monthly payment
With the loan amount in B1, annual rate in B2, term in years in B3, and monthly payment output in D2, enter =-PMT(B2/12,B3*12,B1). PMT needs the periodic interest rate, total number of periods, and present value of the loan. Dividing the annual rate by 12 gives a monthly rate, and multiplying years by 12 gives the number of monthly payments. The minus sign changes Excel’s cash-flow sign convention into a positive payment amount.
For the illustrative $20,000 loan at 6% for five years, the formula returns approximately $386.66 per month. The calculation uses the full precision of the result, even if the cell displays only two decimal places. Multiplying the displayed rounded amount by 60 gives $23,199.60, but that is not necessarily the exact total produced by the unrounded payment. Avoid using a rounded display value to infer an exact payoff total.
PMT calculates a level payment under its assumptions; it does not automatically include an origination fee, insurance premium, taxes, or a balloon payment. If the loan has an introductory rate, variable rate, irregular first period, or fees included in the financed balance, the simple setup may not match the lender’s figures. Check the agreement or ask the lender which rate, balance, dates, and payment components apply.
Build the amortization schedule
Make headers in A8:F8: Period, Beginning balance, Payment, Interest, Principal, and Ending balance. Use row 9 for the first monthly payment, then row 10 for the next one. The formulas below assume the inputs remain in B1:B3 and the monthly payment is in D2.
With A8:F8 holding the schedule headers and row 9 designated as payment 1, enter =$B$1 in B9; it returns $20,000.00, the starting balance. Enter =$D$2 in C9; it returns about $386.66. With the opening balance in B9 and annual rate in B2, enter =B9*$B$2/12 in D9; it returns $100.00 of first-month interest.
With payment in C9 and interest in D9, enter =C9-D9 in E9; it returns about $286.66 of principal. With opening balance in B9 and principal in E9, enter =B9-E9 in F9; it returns an ending balance of about $19,713.34. The calculation keeps full precision internally; these values are shown rounded to cents.
With the period number 1 in A9, enter =A9+1 in A10; it returns 2. With the prior ending balance in F9, enter =F9 in B10; it returns about $19,713.34 as the next opening balance. With the fixed payment in D2, enter =$D$2 in C10; it returns about $386.66. With the new opening balance in B10 and annual rate in B2, enter =B10*$B$2/12 in D10; it returns about $98.57.
With payment and interest in C10 and D10, enter =C10-D10 in E10; it returns about $288.09 of principal. With opening balance and principal in B10 and E10, enter =B10-E10 in F10; it returns about $19,425.25. Select A10:F10 and fill the formulas down through row 68 to create 60 payment rows, from payment 1 through payment 60. In Windows Excel, dragging the fill handle at the lower-right corner of the selected cells copies the formulas and adjusts relative references while retaining the dollar-sign-locked references.
Read the schedule and check its totals
In the first row, $100 of the payment goes to interest because 6% divided by 12 is 0.5%, and 0.5% of $20,000 is $100. The rest reduces principal. In the second row, interest is lower because it is calculated on the smaller opening balance. This explains why the principal portion grows even though the scheduled payment stays the same.
With the 60 payment amounts in C9:C68, enter =SUM(C9:C68) in a total-payment cell; it returns approximately $23,199.36 using the full-precision payment. With interest amounts in D9:D68, enter =SUM(D9:D68) in a total-interest cell; it returns approximately $3,199.36. The sum of principal paid should be about $20,000, and the final ending balance should be close to zero. Minor fractions of a cent can remain because calculations are carried at greater precision than the displayed currency.
A useful check is to compare the first row’s interest with opening balance multiplied by the monthly rate. Another is to confirm each row’s opening balance equals the previous row’s ending balance, and that payment equals interest plus principal. If any of these relationships fail, inspect cell references, rate conversion, and copied formulas before trusting the schedule.
Model extra payments or a different term
A borrower may want to see how additional principal payments could change a projected payoff. A basic schedule can add a separate Extra principal column, but the extra amount must reduce the balance and should not be treated as interest. The schedule also needs a rule for whether the regular payment remains unchanged or is recalculated. Many loans apply extra payments differently, and some contracts may restrict them or charge a fee, so a spreadsheet scenario is not confirmation of lender treatment.
For a clean comparison, duplicate the worksheet or change one input at a time. Increasing the term generally lowers the required scheduled payment but increases the number of periods over which interest can accrue. A shorter term generally raises the scheduled payment and reduces the total interest under the same rate and assumptions. Changing the annual rate affects both payment and interest. Treat these outputs as estimates under the entered assumptions, not a recommendation about which borrowing option to choose.
Common errors and important limitations
- Using 6 instead of 6% or 0.06: Excel interprets 6 as 600% in the payment calculation, not 6%.
- Mixing annual and monthly units: For monthly payments, divide a nominal annual rate by 12 and multiply years by 12. A different payment frequency requires matching rate and period units.
- Entering the wrong number of periods: A five-year monthly loan has 60 payments, not 5 or 12.
- Confusing APR with the contract rate: APR can reflect costs beyond the interest rate. Do not assume an APR is the periodic rate the lender uses to calculate each installment.
- Rounding every row: Rounding interest and balances to cents inside formulas can create a residual balance. Usually keep full precision in formulas and format the cells to display two decimals.
- Expecting the last payment to match exactly: Actual lenders can round each payment, use daily interest, adjust for payment dates, or handle the final installment differently.
The worksheet models regular equal periods. Real loans can accrue interest daily, start on an unusual date, change rate, defer payments, capitalize charges, or include fees. The lender’s servicing system and contract determine the amount due. Check actual terms with the lender or a qualified professional; this calculation is educational and is not financial, tax, investment, or legal advice.
Other Excel functions and useful alternatives
PMT is convenient when the payment is constant. Excel also includes IPMT and PPMT for calculating the interest and principal portions of a specific period. These can be useful for a compact schedule, but a row-by-row balance table is often easier to audit because every component is visible. Whichever method you use, make sure the period number, rate, and total number of periods refer to the same payment frequency.
You can also build the schedule using the formulas shown above without a loan-specific template. Templates can save setup time, but check their assumptions, formulas, and treatment of extra payments before relying on their results. In Excel for Microsoft 365 on Windows, the basic PMT and arithmetic formulas here work without newer dynamic-array functions. The same formulas are available in Excel for Mac and Excel for the web, although ribbon layout and cell-fill interactions can differ by platform. No special add-in is required.
Frequently Asked Questions
What is the Excel formula for a monthly loan payment?
With annual rate in B2, loan term in years in B3, and principal in B1, use =-PMT(B2/12,B3*12,B1). The negative sign makes the displayed payment positive. Use the lender’s actual rate and period conventions for a meaningful comparison.
How do I calculate only the interest portion of a payment?
For a straightforward monthly schedule, multiply that row’s opening balance by the monthly rate. In the example, the first payment’s interest is $20,000 × 6% ÷ 12, or $100. The amount changes as the balance falls.
Why does my Excel balance not end at exactly zero?
Common causes include displayed values rounded to cents, a wrong number of periods, mismatched annual and monthly rates, or a lender’s rounding and date conventions. Check formulas at full precision and compare the final result with the contract. Do not silently force the last balance to zero if doing so would hide a payment difference.
Can Excel calculate a loan with extra payments?
Yes. Add extra principal as a separate schedule item and subtract it from the balance, while keeping interest calculated according to the loan’s terms. Decide whether the scheduled payment stays fixed or is recalculated, and verify how the lender applies additional payments.
Should I use APR or the interest rate in PMT?
Use the rate and periodic calculation method specified for the loan payment, not automatically the advertised APR. APR may include certain costs and is designed to express borrowing cost in a standardized way; it may not be the rate used to accrue each installment’s interest. Confirm the correct input with the lender.
Related
- Attendance Sheet Excel Template: Setup and Tips
- Attendance Register Template in Excel: Setup and Tips
- Attendance Roster Template in Excel: Setup and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Wrap Text in Google Sheets Without Cutting It OffTo wrap text in Google Sheets, select the cell or cells, then choose Format > Wrapping > Wrap.…
- Personal Budget Spreadsheet Template: Build One in ExcelA personal budget spreadsheet template is a worksheet that compares planned income and expenses with what actually comes…
- Excel Power Query Find and Replace: Steps and TipsTo find and replace data in Excel Power Query, open the query, select the column, then choose Transform…
- Google Sheets: How to Merge Cells in a TableTo merge cells in Google Sheets, select the adjacent cells, then choose Format > Merge cells and pick…
- Conditional Formatting for Duplicates in ExcelIn Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight…
- Out of Office in Outlook: Set Your Status and RepliesTo set out of office in Outlook, turn on Automatic Replies, enter your message, and—if you know your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.