calculate-loan

01 Loan payments

Amortization Schedule: Annual Payments in Excel

Calculator No. 01-20766 · Updated · Formula and assumptions shown below

Annual payment $5,009.13
Total interest
$5,045.65
= Annual payment × loan term − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Annual payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

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.

  1. Set the first year to 1 and its opening balance equal to the original principal.
  2. Calculate interest as opening balance multiplied by the annual rate. With the example layout, a row-7 interest formula could be =B7*$B$2 if B7 holds opening balance and B2 holds the rate as a decimal.
  3. Calculate principal repaid as payment minus interest. If payment is in B4, use =$B$4-C7 when C7 contains interest.
  4. Calculate closing balance as opening balance minus principal repaid.
  5. 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

The whole Loan payments section →