calculate-loan

01 Loan payments

Simple Interest Loan Amortization Table in Excel

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

Monthly payment $405.53
Total interest
$4,331.80
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly 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.

A simple-interest loan amortization table in Excel shows each payment, the interest charged for that period, the principal repaid, and the remaining balance. Enter the loan amount, annual interest rate, and term to estimate a fixed monthly payment and total interest; this calculator uses the standard monthly-payment formula, which assumes interest accrues monthly on the outstanding balance.

Formula

For a fully amortizing loan with equal monthly payments, the payment is calculated from the loan balance, monthly interest rate, and number of payments:

Payment = P × r / [1 − (1 + r)−n]

  • P is the starting principal in dollars.
  • r is the monthly interest rate as a decimal, calculated as APR ÷ 1,200. For an 8% APR, r = 0.08 ÷ 12.
  • n is the total number of monthly payments, calculated as years × 12.
  • Payment is the monthly amount in dollars, rounded to the nearest cent.

Total interest in this estimate is the sum of the rounded monthly payments minus the starting principal: Total interest = Payment × n − P. This is an amortizing-balance calculation, not the simple-interest formula that applies interest to the original principal for the entire term.

Worked Example

Using the calculator’s defaults: principal P = $20,000, APR = 8%, and term = 5 years.

  1. Convert the annual rate to a monthly decimal: r = 8 ÷ 1,200 = 0.0066667.
  2. Find the number of payments: n = 5 × 12 = 60.
  3. Substitute into the payment formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)−60] = $405.53, rounded to cents.
  4. Calculate total interest using that rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The displayed estimate is a monthly payment of $405.53 and total interest of $4,331.80. Actual lender schedules can differ because payment dates, rounding, fees, or the contract’s interest method may differ.

Assumptions

  • The interest rate remains fixed throughout the modeled term.
  • Payments are equal, made monthly, and treated as occurring at the end of each period.
  • Interest is modeled monthly at APR ÷ 12 on the unpaid balance.
  • Fees, taxes, insurance, PMI, escrow, late charges, and irregular payment timing are not included.

Build the Amortization Table in Excel

To create a period-by-period schedule, first place the inputs in cells. For example, put the principal in B1, APR as a percentage in B2, term in years in B3, and the monthly payment in B4. In B4, use a payment formula such as =ROUND(-PMT(B2/12,B3*12,B1),2). Excel’s PMT function returns a negative value when the loan amount is entered as positive, so the leading minus sign makes the payment display as a positive amount.

Make headings for payment number, beginning balance, payment, interest, principal paid, and ending balance. In the first schedule row, use payment number 1 and the original principal as the beginning balance. Calculate the period’s interest as beginning balance × APR ÷ 12. Calculate principal paid as payment minus interest, then ending balance as beginning balance minus principal paid. For the next row, carry the prior ending balance forward as the new beginning balance; increase the payment number and copy the formulas down through the planned number of payments.

For example, if the first row’s beginning balance is in B8, payment is in C8, interest is in D8, principal paid is in E8, and ending balance is in F8, the core formulas are =B8*$B$2/12, =$B$4-D8, and =B8-E8. Use absolute references for input cells so they remain fixed when formulas are filled down. Keep full precision in intermediate calculations if matching an actual lender schedule is important; applying rounding to each period can shift later balances by a few cents.

How to Read Each Row

At the beginning of a standard amortizing loan, the balance is largest, so the interest portion of the payment is also relatively large. As principal is repaid, the balance falls, and less interest accrues in later periods. With a fixed payment, more of each later payment therefore goes toward principal. The final balance should be close to zero, but spreadsheet rounding can leave a small residual; a real final payment may be adjusted to clear the outstanding amount.

The payment is not the same as total borrowing cost. Total interest is the amount paid above principal under the assumptions used. A table can also help show how an extra principal payment might change the balance, but this calculator does not model extra payments or calculate a revised payoff date.

Simple Interest Versus Flat Interest

The phrase “simple interest” can refer to more than one method. In a conventional amortizing loan, interest for a period is calculated on the remaining balance, often using a periodic rate derived from the APR. Although the interest calculation for a period may be simple, the payment is not calculated as if the original principal stayed outstanding for the whole term.

A flat-rate calculation instead applies the rate to the original principal for the full duration. A basic version is interest = principal × annual rate × years. For $20,000 at 8% for five years, that would produce $8,000 of interest before dividing repayment into installments. That is not the same calculation as the amortizing example’s $4,331.80 estimate. Do not substitute one method for another: check the loan agreement or disclosure for how interest is calculated and whether the quoted rate is APR, a periodic rate, or a flat rate.

Common Excel Problems and Limits

  • Entering 8 instead of 8%: If a cell is formatted as a percentage, enter 8% or 0.08—not 8, which represents 800%.
  • Mixing years and months: The formula needs 60 periods for a five-year monthly schedule, not 5.
  • Rounding every formula: Rounding each period’s interest and principal can create a small difference from a schedule that retains additional decimal places.
  • Ignoring the contract’s timing: Daily accrual, a first payment date that is not a full month later, variable rates, or fees can change the actual amount.
  • Expecting the final balance to be exactly zero: A cent-level payment and spreadsheet precision may leave a small remainder; inspect the final row rather than assuming the schedule settles perfectly.

This calculator is a fixed-rate estimate, not a reproduction of every lender’s servicing method. It does not determine eligibility, include fees, or establish the amount due on a particular date.

Frequently Asked Questions

How do I make a simple-interest amortization schedule in Excel?
Calculate the fixed payment, then create columns for beginning balance, interest, principal, and ending balance. Each period’s interest is the beginning balance multiplied by the periodic rate; subtract the principal portion from the balance and carry the result to the next row.

What Excel formula calculates the monthly loan payment?
For a fixed monthly rate and term, use =-PMT(APR/12,years*12,principal). To display cents, wrap it in ROUND(...,2). Enter APR as a decimal or percentage consistently with the cell’s formatting.

Is simple interest the same as an amortizing loan?
No. An amortizing loan generally calculates each period’s interest on the remaining balance and repays principal over time. A flat calculation based on the original principal for the entire term produces a different result.

Why does my Excel balance not reach zero?
Common causes include rounding, a payment entered with the wrong sign, a mismatched number of periods, or formulas copied with incorrect cell references. Lenders may also adjust the last payment to account for accrued interest and rounding.

Does this table include fees or extra payments?
No. The estimate covers level monthly payments and modeled interest only. Add separate inputs and formulas for fees or extra principal payments, and verify the treatment of those items against the loan documents.

This result is an estimate, and 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 | Monthly payment | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - 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 →