Yes. You can build a Microsoft Excel loan amortization template with a payment formula and a month-by-month schedule showing interest, principal, and the remaining balance. For a fixed-rate loan with monthly payments, enter the principal in B2, annual interest rate in B3, term in years in B4, and first payment date in B5; with those inputs, calculate the monthly payment in B6 using =ROUND(-PMT($B$3/12,$B$4*12,$B$2),2).
What a loan amortization template shows
An amortization schedule divides each payment between interest and principal. Interest is calculated on the balance still owed, so the interest portion is usually larger at the start of a standard fixed-rate loan. As the balance falls, less interest accrues and more of each regular payment goes toward principal. The schedule also tracks each payment date and the balance after payment.
This is useful for understanding the planned repayment path, estimating total interest under the model’s assumptions, and comparing possible payment scenarios. It is not necessarily the lender’s official payoff statement. Actual servicing can reflect fees, escrow, payment timing, rate changes, or other terms that a simple worksheet does not include.
Start with a blank workbook or a template
In Excel for Microsoft 365 on Windows, choose File > New and search for loan amortization or loan payment. Microsoft’s template catalog and results can vary by account, region, and time, so a matching ready-made file may not appear. A blank workbook gives you control over the assumptions and makes it easier to check how the calculations work.
Set up a small input area at the top of the sheet. Use clear labels in column A and the corresponding values in column B:
- B2: loan principal, such as 250000.
- B3: annual interest rate, entered as a percentage, such as 6%.
- B4: loan term in years, such as 30.
- B5: date of the first scheduled payment.
- B6: calculated monthly payment.
Format B2 and B6 as currency, B3 as a percentage, and B5 as a date. On Windows, the Home tab provides number-format controls; the same basic number formats are available in Excel for Mac and Excel for the web, though the ribbon layout can differ. Enter the rate as 6%, not 6, if you mean six percent.
Calculate the monthly payment
Excel’s PMT function calculates the regular payment for a loan with a constant periodic rate and equal payments. Its rate argument must use the rate for one payment period, and its number-of-periods argument must count payments—not years. That is why a monthly schedule divides the annual rate by 12 and multiplies the term in years by 12.
With principal in B2, annual rate in B3, and term in years in B4, enter this in B6: =ROUND(-PMT($B$3/12,$B$4*12,$B$2),2). For a 250,000 loan at 6% annually over 30 years, the modeled payment is 1,498.88 per month, before fees or other charges. PMT ordinarily returns a negative cash-flow value when the loan amount is positive; the minus sign makes the displayed payment positive. ROUND limits it to cents.
This calculation assumes a fixed annual rate applied in equal monthly periods. It does not model an adjustable rate, irregular payment dates, lender fees, or a different day-count method. For a real loan, check the payment amount and calculation conventions in the loan documents or with the lender. This worksheet is for modeling and education, not financial, tax, investment, or legal advice.
Build the payment schedule
Start a schedule around row 11, with headings in A11:G11: Payment number, Payment date, Opening balance, Payment, Interest, Principal, and Ending balance. Put the first payment in row 12. In the assumptions below, the input cells are B2:B5 and the monthly payment is B6.
- With the schedule headings in A11:G11 and the first payment on row 12, enter
=1in A12 for the first payment number. - With the first payment date in B5 and the first schedule date in B12, enter
=$B$5in B12. - With the loan principal in B2 and the first opening balance in C12, enter
=$B$2in C12. - With the opening balance in C12 and annual interest rate in B3, enter
=ROUND(C12*$B$3/12,2)in E12 to calculate the month’s interest. - With the regular payment in B6, opening balance in C12, and interest in E12, enter
=MIN($B$6,C12+E12)in D12. This caps the final payment at the amount still due. - With the payment in D12 and interest in E12, enter
=D12-E12in F12 to calculate principal repaid. - With the opening balance in C12 and principal repaid in F12, enter
=MAX(0,C12-F12)in G12 to prevent a tiny negative balance from rounding.
For the next row, with row 13 following row 12, enter =A12+1 in A13, =EDATE(B12,1) in B13, and =G12 in C13. These respectively increment the payment number, advance the payment date by one month, and carry the previous ending balance forward. EDATE is available in current Excel versions, including Microsoft 365. Copy the formulas in A13:C13 and D12:G12 down for the full term: 360 payment rows for a 30-year monthly loan. Check that the final ending balance is zero.
Check the first payment with a worked example
For the example loan of 250,000 at 6% for 30 years, the monthly rate is 0.5%. The first month’s interest is 250,000 multiplied by 0.005, or 1,250.00. The payment is 1,498.88, so principal repaid in that first month is 248.88 and the ending balance is 249,751.12. Those figures provide a simple check that the schedule’s columns are not reversed or using the annual rate as if it were monthly.
Because the payment and interest are rounded to cents each month, the final payment can differ by a few cents from the regular amount. That is expected in a cent-rounded spreadsheet model. The unadjusted payment multiplied by 360 is about 539,596.80, but the schedule’s exact total can vary slightly because the last payment is capped and rounding is applied period by period. A lender’s statement can differ further if its terms or payment dates differ.
Add totals and optional extra payments
Totals help summarize the schedule, but they are only as reliable as the assumptions and formulas above them. With payment values in D12:D371 for 360 months, enter =SUM(D12:D371) in a total-paid cell. With interest values in E12:E371, enter =SUM(E12:E371) in a total-interest cell. These totals should include the adjusted final payment, not 360 copies of the regular payment.
To model extra principal, add a column for extra payment and enter a planned amount for each month. Update the total-payment formula so it adds that amount to the scheduled payment but never exceeds the balance plus current interest. Then make sure principal repaid equals total payment minus interest, and carry the resulting balance forward. An extra payment can shorten the modeled payoff period and reduce modeled interest, but the lender may apply extra money differently or require instructions to direct it to principal. Confirm the actual rules before relying on a scenario.
You can also compare a few scenarios by changing the rate, term, or extra-payment assumptions in copies of the workbook. Keep an original version and label each scenario clearly; otherwise, it is easy to confuse an estimate with the actual loan terms.
Common spreadsheet errors and how to avoid them
- Using 6 instead of 6%: Excel interprets 6 as 600% when used as a rate. Enter 6% or 0.06.
- Using the annual rate in every monthly row: Divide the annual rate by 12 for this monthly model. A 6% annual rate is 0.5% per month under the stated assumption.
- Using the wrong number of periods: A 30-year monthly loan has 360 payments, not 30.
- Subtracting the payment before interest: Interest is calculated on the opening balance for the period, then subtracted from the payment to find principal.
- Relying on formatting to round calculations: Showing two decimal places does not necessarily round the stored value. ROUND in the formulas makes the model’s cent-rounding rule explicit.
- Expecting every loan to fit the model: Variable-rate loans, skipped payments, fees, and irregular dates need additional logic. Do not treat the basic schedule as an official payoff quote.
Use formula references consistently when copying down: the dollar signs in references such as $B$3 keep the input cell fixed, while references such as C12 adjust for each schedule row. If the final balance is not zero, first check the term and rate units, then confirm that you filled exactly the intended number of rows and that the final-payment cap is present.
When a template is useful—and when to use something else
Excel is a good choice when you want to inspect or modify assumptions, compare scenarios, or add charts. A lender’s online calculator may be quicker for a rough payment estimate, while a lender-issued amortization schedule or payoff statement is the better source for amounts tied to the actual contract. A downloaded template can save setup time, but review its formulas, input cells, and treatment of rounding before entering sensitive or consequential figures.
For a clear visual summary, you can chart the ending balance by payment date or compare the interest and principal columns. Keep the underlying schedule intact: a chart is a presentation of the calculated values, not a separate confirmation that the model matches your lender’s method.
Frequently Asked Questions
Does Excel have a loan amortization template?
Excel’s File > New template search may show loan-related files, but availability varies. You can build a basic fixed-rate monthly schedule in a blank workbook using PMT and the row formulas above.
What Excel formula calculates a loan payment?
For a monthly fixed-rate loan, use PMT with the monthly interest rate and total monthly payment count. In this example, =ROUND(-PMT($B$3/12,$B$4*12,$B$2),2) returns a positive payment rounded to cents.
How do I calculate the interest for each month?
Multiply that month’s opening balance by the monthly rate. With opening balance in C12 and annual rate in B3, =ROUND(C12*$B$3/12,2) returns the first month’s interest in E12.
Can I use the same template for extra payments?
Yes, if you add an extra-payment input and revise the payment and principal formulas so extra money reduces principal and the final payment cannot exceed the amount due. Check how the lender applies additional payments before treating the result as a real payoff projection.
Why does the last payment or balance differ by a few cents?
Rounding interest and payments to cents each month can create a small difference by the end of the schedule. The capped final-payment formula prevents overpayment in the model, but lender timing and contract terms can create additional differences.
Related
- Is Excel on iPad? Features, Limits, and Costs
- Invoice and Packing List Template in Excel: What to Include
- Invoice Order Template Excel: Setup and Key Details
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 Edit Mail Merge Fields in Word Documents Step by StepTo edit a mail merge field in Word, select it in your document and use the Mailings tab…
- Find and Replace Highlight Color in Microsoft WordIn Word for Microsoft 365 on Windows, use Home > Find, then search for the Highlight format and…
- Excel Sum Minus: How to Add and Subtract Numbers in ExcelTo subtract a value from a total in Excel, combine SUM with the minus sign: with numbers in…
- Google Sheets Dropdown Chips: How to Convert ThemTo convert an existing Google Sheets dropdown into a chip, select a cell in the dropdown, open Data…
- How to Copy Multiple Sheets in Excel Without ErrorsTo copy several sheets in Excel for Microsoft 365 on Windows, select their worksheet tabs, right-click one of…
- Will Google Sheets Get Power Query? What to Use InsteadNo—Google Sheets does not currently have a built-in version of Microsoft Power Query, and Google has not announced…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.