An annual loan amortization Excel template lists each year’s opening balance, payment, interest, principal repaid, and closing balance. In Microsoft Excel for Microsoft 365 on Windows, put the loan amount, annual interest rate, and number of annual payments in cells B2:B4; with those inputs in place, the payment formula in B5 is =-PMT($B$3,$B$4,$B$2). The minus sign displays the payment as a positive number. Build the schedule beneath it using the steps and formulas below.
What an annual amortization schedule shows
An amortization schedule explains how each payment reduces a loan over time. In a standard fixed-rate loan with payments at the end of each year, interest for a period is calculated on that period’s opening balance. The payment first covers that interest; whatever remains reduces principal. As the balance falls, the interest portion usually gets smaller and the principal portion gets larger.
“Annual” describes the payment periods in the schedule, not merely the way figures are displayed. A loan with one payment each year needs a yearly schedule. A loan with monthly payments needs 12 monthly periods per year, even if you later add a summary showing yearly totals. Treating a monthly-payment loan as if it had one annual payment can produce the wrong interest and balance figures.
A schedule is useful for checking how a quoted payment breaks down, estimating the remaining balance after a payment, and seeing the total interest implied by the assumptions. It is a model, however—not a replacement for the lender’s statement or contract. Actual payment timing, fees, rate changes, and lender rounding can change the result.
Set up the loan inputs
Start with a small input block in column A and enter the corresponding values in column B. Use a consistent unit: the rate must be the rate for each payment period, and the term must count those periods.
- A2: Loan amount; enter the original principal in B2, such as 10000.
- A3: Annual interest rate; enter 6% in B3 for a 6% rate per year.
- A4: Number of annual payments; enter 5 in B4 for five yearly payments.
- A5: Annual payment; calculate this in B5 using the PMT formula.
In Excel, enter a percentage as 6% or as 0.06; both represent six percent. Format B2 and B5 as currency and B3 as a percentage so the inputs are easy to read. On Windows, select the cells and use Home > Number to choose a number format. Excel for Mac has corresponding number-format controls on the Home tab, although the ribbon layout can vary slightly with the window size and update channel.
Build the schedule columns
In row 9, add these headings: Year, Opening balance, Payment, Interest, Principal, and Closing balance. Put the first payment period in row 10. One row then represents one annual payment. You can type 1 in A10 and enter formulas for the first period; copy the next row’s formulas down until the final payment year.
With B2:B5 holding the inputs described above, calculate the annual payment in B5 with =-PMT($B$3,$B$4,$B$2). PMT uses the rate per period, number of periods, and present value. The negative sign reverses Excel’s usual cash-flow sign so the payment appears positive when the loan amount is positive.
With A9:F9 as the schedule headings, B2 as the original loan amount, and the first payment in row 10, set B10 to the opening balance with =$B$2. Enter 1 in A10 as a label for the first year. With the same layout, calculate the payment in C10 using =$B$5.
With B10 as the first year’s opening balance and B3 as the annual rate, calculate interest in D10 with =B10*$B$3. With C10 as the payment and D10 as interest, calculate principal repaid in E10 with =C10-D10. With B10 as opening balance and E10 as principal repaid, calculate the closing balance in F10 with =B10-E10.
For the second schedule row, keep the headings in row 9 and use row 11 for the next annual payment. With A10 containing the prior year number, enter =A10+1 in A11. With F10 as the prior year’s closing balance, enter =F10 in B11. With B5 as the fixed annual payment, enter =$B$5 in C11. Then, with B11 as the opening balance and B3 as the annual rate, enter =B11*$B$3 in D11; with C11 and D11 as payment and interest, enter =C11-D11 in E11; with B11 and E11 as opening balance and principal, enter =B11-E11 in F11. Copy A11:F11 down for the remaining payment years. The dollar signs keep references to the input cells fixed when formulas are copied.
Worked example: a five-year loan
Suppose the illustrative loan is $10,000, the annual rate is 6%, and there are five end-of-year payments. The PMT formula returns an annual payment of about $2,373.96. In the first year, interest is $10,000 multiplied by 6%, or $600. The remainder of the payment—about $1,773.96—reduces principal, leaving an opening balance for year two of about $8,226.04.
| Year | Opening balance | Payment | Interest | Principal | Closing balance |
|---|---|---|---|---|---|
| 1 | $10,000.00 | $2,373.96 | $600.00 | $1,773.96 | $8,226.04 |
| 2 | $8,226.04 | $2,373.96 | $493.56 | $1,880.40 | $6,345.63 |
| 3 | $6,345.63 | $2,373.96 | $380.74 | $1,993.23 | $4,352.41 |
| 4 | $4,352.41 | $2,373.96 | $261.14 | $2,112.82 | $2,239.59 |
| 5 | $2,239.59 | $2,373.96 | $134.38 | $2,239.59 | $0.00 |
The figures are displayed to cents, while Excel retains more precision in the underlying calculations. That is why adding the displayed amounts can occasionally differ by a cent from a total calculated using the full-precision values. In this example, total payments are about $11,869.82 and total interest is about $1,869.82. The closing balance reaches approximately zero when the full-precision PMT result is used. These are illustrative calculations, not a quote for a real loan.
Annual payments versus monthly payments
Check the contract’s payment frequency before using the template. For one payment each year, use the annual rate and the number of years as the PMT rate and period count, as in the example. For a fixed monthly-payment loan with a nominal annual rate, a basic periodic setup generally uses the annual rate divided by 12 and the number of years multiplied by 12. That is a different schedule with monthly rows, not an annual-payment schedule.
If you want annual totals for a monthly loan, first calculate the monthly amortization schedule according to the loan’s terms. Then summarize the 12 monthly payments, interest amounts, and principal amounts in each calendar or loan year. The end-of-year balance is the balance after the final monthly payment in that year. Simply multiplying an annual payment by 12 will not produce a valid monthly schedule when the payment amount, dates, or interest calculation differ.
Also check what “annual rate” means in the lender’s documents. A nominal rate, an effective annual rate, and a rate applied using a particular day-count convention do not necessarily produce identical interest. Match the lender’s period and timing assumptions as closely as the available information allows.
Adjustments for real loan terms
The basic template assumes a fixed rate, equal payments, no extra payments, no fees added to the balance, and payments at the end of each year. These assumptions make the standard PMT calculation appropriate as a starting point, but real agreements may not follow them.
- Different first payment date: A short or long first period can make the first interest charge different from later years.
- Variable rate: Recalculate each period’s interest using the applicable rate, and confirm whether the payment is recalculated or stays fixed under the contract.
- Extra principal: Record the additional amount separately from the scheduled payment and reduce the balance by the extra principal only if the lender applies it that way.
- Balloon payment: A loan that leaves principal due at maturity is not fully paid off by equal payments; represent the final amount explicitly rather than assuming a zero balance.
- Fees or financed charges: Determine whether they are paid separately or included in the amount being amortized. Do not silently treat a fee as principal.
When terms change by period, use the contract’s payment and interest rules rather than relying on one constant PMT result. If you need the schedule to match a lender statement, confirm how it handles dates, day counts, payment application, rounding, and any fees.
Common spreadsheet errors and checks
A frequent mistake is entering 6 instead of 6% or 0.06 for the annual rate. That makes the rate 600% in Excel and greatly inflates the interest. Another is using the number of years as the period count for a monthly loan, or dividing the interest rate by 12 while still using only five periods for a five-year monthly loan. Rate and period count must refer to the same interval.
Use these checks before relying on the schedule:
- For each row, payment should equal interest plus principal, apart from any separately modeled fees or adjustments.
- Each closing balance should equal the opening balance less principal repaid.
- The next period’s opening balance should match the previous period’s closing balance.
- For a fully amortizing fixed-rate example, the final balance should be approximately zero, allowing for displayed rounding.
- Total principal repaid across the schedule should approximately equal the original principal.
Do not round the interest or principal formulas to cents at every step unless the loan’s rules specifically require that method. Formatting values to show two decimal places is different from changing the underlying calculation with a rounding formula. If a final payment must be an exact amount in cents, follow the lender’s stated rounding and payoff procedure; a generic spreadsheet should not decide the contractual adjustment.
Choosing or adapting a template
A useful template should make its assumptions visible, separate inputs from calculations, and show both interest and principal for every payment period. Look for editable rate and term fields, a clearly identified payment frequency, a final balance check, and a way to add fees or extra payments without overwriting the original formulas. Protecting formula cells can reduce accidental edits, but keep an editable copy and document any changes to the assumptions.
You can create the schedule directly in Excel using the formulas above or adapt a workbook supplied by a lender or another source. Before entering sensitive financial information into a downloaded file, consider whether you trust its source and inspect formulas for unexplained external links, hidden sheets, and unexpected macros. A plain formula-based workbook does not need macros for this basic schedule. Never enable macros solely because a template says they are required unless the source and purpose are trusted.
This spreadsheet is for organizing calculations and understanding assumptions, not financial, tax, investment, or legal advice. For an actual borrowing decision, payment amount, payoff figure, or tax treatment, check the loan documents and ask the lender or a qualified professional when appropriate.
Frequently Asked Questions
Can Excel calculate an annual loan payment?
Yes. The PMT function calculates a level payment when you provide the rate per payment period, number of periods, and loan amount. For annual payments, use an annual rate per period and a period count equal to the number of annual payments, subject to the loan’s actual terms.
What is the difference between an annual and monthly amortization schedule?
An annual schedule has one row and one payment period per year. A monthly schedule has 12 periods per year and calculates interest and principal for each monthly payment. A yearly summary of a monthly loan is not the same as a loan with annual payments.
Why does my final balance show a few cents remaining?
Displayed values may be rounded to two decimals even when the formulas retain additional precision. Other causes include rounding each period, using a payment rounded before calculating the schedule, or entering a rate or term that differs from the loan agreement. Check the unrounded cells and the lender’s rounding rules.
Should I use the stated annual rate or divide it by 12?
Use the rate that matches the schedule’s payment period and the lender’s definition of the rate. An annual-payment schedule typically uses an annual rate per annual period. A monthly schedule may use an annual nominal rate divided by 12, but the loan documents can specify different conventions.
Can I use this template for extra payments or a variable rate?
Yes, but the basic fixed-payment example does not automatically model those terms. Add explicit columns or period-specific formulas that reflect how extra amounts are applied and how the rate or payment changes. Verify the method against the lender’s rules rather than assuming every extra payment immediately reduces principal.
Related
- Excel Portfolio Analysis: Track Risk and Returns
- How to Hide a Column in Excel: Steps and Shortcuts
- Excel Print Area: How to Set, Change, and Clear It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Workbook Sheet Limit: What Sets the Maximum?Modern Microsoft Excel for Microsoft 365 on Windows has no fixed maximum number of sheets in a workbook:…
- Excel Solver Two-Variable Example and SetupFor a two-variable Excel Solver example, maximize profit from chairs and tables by changing the number of each…
- How to Find a Table in Excel: Search and NavigationTo find a table in Excel, search for a word or value you expect it to contain, then…
- Creating Rules in Microsoft Outlook: A Practical GuideTo create a rule in Outlook for Microsoft 365 on Windows, select a message and choose Home >…
- Excel Shortcut to Select Every Cell in a Column on WindowsIn Excel for Microsoft 365 on Windows, press Ctrl+Space while a cell in the column is active to…
- Can You Use Excel to Randomize a List? Yes, Here’s HowYes. In Excel for Microsoft 365, you can randomize a list by assigning each item a random number…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.