An annual-payment amortization schedule shows how each yearly payment is divided between interest and principal, and how the loan balance changes over time. This calculator estimates the level annual payment from a loan amount, APR, and term; enter those values to see the payment and total interest, then use the schedule guidance below to build a year-by-year table in Excel.
Formula
For a fixed-rate loan with one level payment at the end of each year, the annual payment is:
A = P × r / [1 − (1 + r)−n]
- A is the annual payment in dollars.
- P is the starting loan principal in dollars.
- r is the annual interest rate as a decimal: APR ÷ 100.
- n is the number of annual payments, equal to the loan term in years.
Total interest in this simplified schedule is annual payment × number of payments − original principal. The calculator rounds the payment to cents before calculating total interest, so its total reflects the displayed payment repeated for the full term. Actual contracts may use different payment dates, interest conventions, or rounding rules.
Worked Example
Using the default inputs—$20,000 principal, 8% APR, and five annual payments—the rate per year is 8% ÷ 100 = 0.08. Substitute into the level-payment formula:
A = 20,000 × 0.08 ÷ [1 − (1.08)−5]
(1.08)−5 is approximately 0.680583. Therefore, A is approximately 1,600 ÷ 0.319417, or $5,009.13 after rounding to cents. The calculator’s total-interest result is $5,009.13 × 5 − $20,000 = $5,045.65.
Build an Annual Schedule in Excel
Excel can display the yearly breakdown once you have the payment amount. A transparent table is often easier to audit than a single formula. Put the loan principal, annual rate, number of years, and payment in separate input cells; for example, use B1 through B4 respectively. Enter the first period number, opening balance, interest, principal repaid, payment, and closing balance in a row of labeled columns.
- Set the first year to 1 and its opening balance equal to the original principal.
- Calculate interest as opening balance multiplied by the annual rate. With the example layout, a row-7 interest formula could be
=B7*$B$2if B7 holds opening balance and B2 holds the rate as a decimal. - Calculate principal repaid as payment minus interest. If payment is in B4, use
=$B$4-C7when C7 contains interest. - Calculate closing balance as opening balance minus principal repaid.
- For the next year, link its opening balance to the prior row’s closing balance, then fill the formulas down through the number of payments.
Cell references depend on your chosen layout; use absolute references such as $B$2 for shared inputs, and relative references for values that change each row. Format money cells to two decimal places, but keep full precision in formulas. To obtain the payment directly in Excel, the PMT function can be used as =-PMT(annual_rate, years, principal). PMT ordinarily returns a negative cash-flow amount when the principal is entered as positive, so the minus sign displays the payment as a positive amount.
How to Read the Schedule
At the start of each period, interest is computed on the outstanding balance. The payment first covers that period’s interest; what remains reduces principal. As the balance falls, the interest portion generally falls too, while the principal portion grows, even though the scheduled payment remains level. The closing balance after the last payment should be zero or very close to it.
For a quick check on the example, first-year interest is $20,000 × 8% = $1,600. Of the $5,009.13 payment, $3,409.13 therefore reduces principal, leaving a closing balance of $16,590.87. A later year’s interest is calculated on that reduced balance, not on the original $20,000.
Annual Versus Monthly Payments
Annual payments are not interchangeable with monthly payments. This calculator treats 8% APR as an annual rate applied once per year, with one payment at each year-end. A monthly schedule instead uses monthly periods and a monthly rate convention, commonly APR divided by 12 for a standard estimate, and has twelve payment dates per year. Payment frequency changes the timing of principal reduction and the total interest calculation.
Check the loan documents for the required payment frequency and how interest accrues. If payments are monthly, do not enter a monthly payment into an annual schedule or use the annual formula unchanged. For irregular payments, variable rates, or a first payment date that differs from the regular schedule, the basic level-payment calculation will not reproduce the lender’s exact ledger.
Common Excel Errors and Limits
- Percent entered at the wrong scale: Excel’s percentage format stores 8% as 0.08. If you type 8 into a formula expecting a decimal, it represents 800%, not 8%.
- Wrong sign in PMT: Excel uses cash-flow signs. A positive loan amount often produces a negative payment; reverse one sign or display the payment’s magnitude consistently.
- Rounding each period: Displaying two decimals is not the same as rounding every intermediate calculation. Rounding interest and principal manually in each row can create a small ending balance difference.
- Final-row residue: If a few cents remain because of rounding, a real contract may adjust the final payment. Do not silently change the formula for earlier payments; follow the lender’s stated method when reconciling a real account.
- Unmodelled charges: The calculation excludes fees and other amounts unless they are included in the principal or separately modelled. It also assumes the rate and payment pattern stay fixed.
This schedule is a mathematical illustration, not a record of a specific loan. Confirm the annual rate, payment timing, compounding method, and any payment adjustments from the loan documents before comparing the estimate with an account statement.
Assumptions
- The interest rate is fixed for the full term.
- Payments are equal and made once per year at the end of each annual period.
- Interest compounds annually, using the APR as the annual period rate; this is not monthly compounding.
- Fees, taxes, insurance, PMI, and escrow are not included.
Frequently Asked Questions
Can Excel make an annual amortization schedule?
Yes. Use columns for opening balance, interest, principal repaid, payment, and closing balance, then fill the annual formulas down for the loan term. Excel’s PMT function can calculate the fixed payment.
What formula calculates an annual loan payment?
For a fixed rate and end-of-year payments, use P × r ÷ [1 − (1 + r)−n], where P is principal, r is the annual rate as a decimal, and n is the number of annual payments.
How do I calculate annual interest in an amortization table?
Multiply the opening balance for that year by the annual rate. The interest amount is part of the payment; the rest reduces the balance.
Why does the balance not reach exactly zero in Excel?
Displayed rounding, rounded payments, or a different lender convention can leave a small residual. Keep full precision in intermediate cells and check whether the final payment is adjusted.
Can I use this for a monthly-payment loan?
Not as written. A monthly schedule needs monthly periods, the applicable monthly rate convention, and one row per payment. Confirm the contract’s rate and payment rules before adapting the spreadsheet.
The result is an estimate; the lender’s disclosure gives the exact figures.
Related
Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000 in apr | Interest rate (APR) | % | 8 | 0.01 | 100 in yrs | Loan term | years | 5 | 0.0833 | 50 out pmt | Annual payment | $ | p * (apr / 100) / (1 - (1 + apr / 100) ^ -yrs) | 2 out int | Total interest | $ | pmt * yrs - p | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- Amortization Schedule by Month: Payment and Balance$405.53
- Amortization Schedule by Hand: Formula and Example$405.53
- Amortization Schedule Based on Payment Amount$405.53
- Amortization Schedule Calculator: Payments and Balance$405.53
- Auto Loan Amortization Schedule Calculator$405.53
- Adjustable Rate Amortization Schedule Calculator$405.53