calculate-loan

01 Loan payments

Excel Simple Interest Amortization Table: Formulas

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

Monthly payment $405.53
Total interest
$4,331.67
= 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.

An Excel simple interest amortization table shows how each payment is divided between principal and interest, and how the balance changes over time. This calculator estimates the fixed monthly payment and total interest for a simple-interest loan; enter the amount, annual interest rate, and term to see the results.

Formula

This calculator uses the standard fixed-payment, monthly amortization formula. It calculates a level payment using a monthly rate, then subtracts the original principal from all scheduled payments to estimate total interest.

  • Monthly payment: M = P × r ÷ [1 − (1 + r)−n]
  • Total interest: I = M × n − P
  • Monthly rate: r = APR ÷ 1,200
  • Number of payments: n = years × 12

Here, P is the loan principal in dollars, APR is the annual percentage rate entered as a percentage, M is the monthly payment in dollars, I is total interest in dollars, and n is the number of monthly payments. The calculator rounds the monthly payment to cents, then uses that rounded amount to calculate total interest.

Although the page concerns an Excel simple-interest amortization table, the payment formula above models a conventional fixed-rate loan with interest accruing on the outstanding balance and monthly payments. It is not the same as every contract described as “simple interest.” In a daily simple-interest contract, for example, interest may accrue based on the balance and the number of days between payments, so payment dates can affect the schedule. Check the loan documents for the accrual method before treating an Excel estimate as a contract-specific result.

Worked Example

Using the calculator’s default inputs—$20,000 principal, 8% annual rate, and a five-year term:

  1. Convert the annual rate to a monthly decimal rate: r = 8 ÷ 1,200 = 0.0066667.
  2. Convert the term to payments: n = 5 × 12 = 60.
  3. Substitute into the payment formula: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60].
  4. The monthly payment rounds to $405.53.
  5. Total interest, using the rounded payment, is $405.53 × 60 − $20,000 = $4,331.80.

The final scheduled payment on a real loan can differ slightly if the lender adjusts it to clear a small remaining balance caused by rounding or uses different payment dates or accrual rules.

Assumptions

  • The interest rate stays fixed for the full term.
  • There are equal monthly payments, made at the end of each monthly period.
  • Interest is compounded monthly at APR divided by 12. This is an amortizing-loan estimate, not a daily-interest calculation.
  • Fees, taxes, insurance, PMI, escrow, and extra payments are not included.

Build the Table in Excel

To create the schedule, put the loan amount, annual rate, and number of years in separate input cells. For example, use B1 for principal, B2 for APR as a decimal (8% entered as 0.08), and B3 for the term in years. In B4, calculate the monthly payment with Excel’s PMT function: =-PMT(B2/12,B3*12,B1). The minus sign makes the displayed payment positive because Excel treats money paid out as a negative cash flow.

Create headings for payment number, beginning balance, payment, interest, principal, and ending balance. In the first schedule row, use these formulas, assuming row 8 is the first payment:

  • Payment number: 1; in the next row, use =A8+1.
  • Beginning balance: =$B$1; in later rows, link it to the preceding ending balance.
  • Payment: =$B$4.
  • Interest: =B8*$B$2/12, where B8 is the beginning balance.
  • Principal repaid: =C8-D8, where C8 is the payment and D8 is interest.
  • Ending balance: =B8-E8, where E8 is principal repaid.

Copy the formulas down for the number of monthly payments. Use cell references that match your own layout, and format money cells as currency. If the final balance is a few cents below or above zero, that can result from rounding each displayed line. For a schedule intended to match a lender statement, follow the statement’s payment dates, rate convention, and final-payment adjustment rather than forcing the displayed balance to zero.

How to Read an Amortization Schedule

Each payment usually contains interest for that period plus principal repayment. At the start, the balance is larger, so a fixed rate typically produces more interest in each payment than it does later. As the balance falls, the interest portion tends to shrink and more of the same payment goes toward principal. The payment itself remains level in this simplified fixed-rate example.

The ending balance should equal the beginning balance minus principal repaid, not minus the whole payment. Interest is the cost for that period; it does not reduce principal. Summing the interest column provides a schedule-based total, though it may vary by cents from this calculator if the spreadsheet rounds interest or payment amounts on each row.

Simple Interest, Excel Functions, and Common Errors

“Simple interest” can refer to interest calculated only on principal, often using interest = principal × annual rate × time. A basic-interest calculation of that kind does not, by itself, describe a level-payment amortization schedule: it does not specify how outstanding principal changes after each payment. A loan contract may instead calculate interest periodically on the unpaid balance. Confirm the contract’s definition before choosing spreadsheet formulas.

Excel’s PMT function is appropriate for a fixed payment with a fixed periodic rate and a set number of periods. The formula in this calculator is its mathematical equivalent, with the rate converted from annual percent to monthly decimal and the years converted to months. PMT does not automatically model irregular payment timing, daily accrual, a changing rate, fees financed into the loan, or extra principal payments.

  • Entering 8 instead of 0.08 in PMT: Excel’s PMT rate argument is a decimal periodic rate, so an 8% APR becomes 0.08/12, not 8/12.
  • Using years as the payment count: For monthly payments, the number of periods is years × 12.
  • Confusing payment with principal: The full payment is not principal repayment; subtract that period’s interest first.
  • Rounding every intermediate value: Displaying two decimals is different from rounding every calculation. Choose and document a rounding policy, since small differences accumulate.
  • Assuming the APR explains every charge: The estimate excludes contract-specific costs and does not recreate a lender’s disclosure.

Frequently Asked Questions

How do I make a simple interest amortization table in Excel?
Set up columns for payment number, beginning balance, payment, interest, principal, and ending balance. Calculate each period’s interest according to the loan’s actual accrual method, subtract it from the payment to find principal repaid, then subtract principal from the beginning balance.

Can I use Excel’s PMT function for a simple-interest loan?
PMT calculates equal payments for a fixed periodic rate and term. It can estimate a loan that accrues interest monthly on the remaining balance, but it may not match a daily simple-interest contract or a loan with irregular payment dates.

Why does my Excel balance not reach exactly zero?
Small residual balances commonly come from rounding or from using a fixed payment that differs slightly from the lender’s final payment. Check the rate conversion, number of periods, and whether the sheet rounds intermediate amounts.

Does a simple-interest amortization table include extra payments?
Not in the formulas shown. To model an extra payment, add it to the amount applied to principal for that period, subject to the contract’s rules, and calculate later interest on the resulting lower balance.

Is simple interest the same as an amortizing loan?
No. Simple interest describes how interest is calculated; amortization describes how scheduled payments reduce a balance over time. A loan’s documents determine how those ideas apply together.

This 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 | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 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 →