calculate-loan

01 Loan payments

Amortized Interest Formula in Excel

Calculator No. 01-18504 · 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 amortized interest formula in Excel can calculate the interest portion of a loan payment or build a payment-by-payment schedule. Enter the loan amount, annual APR, and term below to estimate the monthly payment and total interest; Excel’s IPMT and CUMIPMT functions can also show interest by period.

Formula

For a fixed-rate loan with equal monthly payments, the payment is calculated as:

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

Here, P is the amount borrowed in dollars, r is the monthly interest rate as a decimal, and n is the number of monthly payments. With an APR entered as a percentage per year, r = APR / 1200. For a term entered in years, n = years × 12.

The total interest estimate uses the rounded monthly payment: Total interest = payment × n − P. This subtracts the original amount borrowed from the sum of scheduled payments. The result can differ slightly from a lender’s exact figures if payment dates, rounding, or the loan’s rate convention differ.

Worked Example

Using the calculator’s defaults: a $20,000 loan, an 8% APR, and a five-year term.

  1. Convert the APR to a monthly decimal rate: 8 / 1200 = 0.0066667.
  2. Find the number of payments: 5 × 12 = 60.
  3. Apply the payment formula: 20,000 × 0.0066667 / (1 − (1 + 0.0066667)−60), which is approximately $405.53 per month when rounded to cents.
  4. Multiply that rounded payment by the number of payments: $405.53 × 60 = $24,331.80.
  5. Subtract the principal: $24,331.80 − $20,000 = $4,331.80 in estimated total interest.

The figures are estimates from the stated inputs and formula. A spreadsheet may show a slightly different result if it keeps unrounded values for intermediate calculations instead of rounding each payment to cents.

Assumptions

  • The interest rate is fixed for the entire term.
  • Payments are equal, monthly, and made at the end of each period.
  • Interest is calculated monthly using APR divided by 12.
  • Fees, taxes, insurance, PMI, and escrow are not included.

Using Excel’s Interest Functions

Excel’s IPMT function returns the interest portion of one payment period. Its general syntax is =IPMT(rate, per, nper, pv, [fv], [type]). For monthly payments, use the monthly rate, the period number, the total number of payments, and the present value (loan principal). For example, if the APR is in B2, the term in years is in B3, and the principal is in B1, the interest for month one can be calculated with:

=IPMT(B2/12, 1, B3*12, -B1)

Excel’s financial functions conventionally treat money paid out and money received as opposite signs. If a result appears negative, that may reflect the cash-flow sign convention rather than a negative interest charge; use consistent signs for the principal and payment.

To total interest across all scheduled payments, use CUMIPMT. For the same cells and a loan paid monthly at period-end, one form is =CUMIPMT(B2/12, B3*12, B1, 1, B3*12, 0). The start and end arguments specify the first and last payment periods to include, and the final zero specifies end-of-period payments. Depending on the signs supplied, the returned cumulative amount may be negative; use the appropriate sign convention when displaying it.

Build a Period-by-Period Schedule

A full amortization schedule shows how each payment is split between interest and principal, and how the balance declines. Set up columns for period, beginning balance, payment, interest, principal repaid, and ending balance. For a monthly fixed-rate schedule, the core calculations are:

  • Interest: beginning balance × monthly rate.
  • Principal repaid: payment − interest.
  • Ending balance: beginning balance − principal repaid.
  • Next period’s beginning balance: the previous period’s ending balance.

For a $20,000 balance at an 8% APR, the first month’s interest before rounding is about $20,000 × 0.08 / 12 = $133.33. With the example payment of $405.53, about $272.20 goes toward principal. In later months, the balance is lower, so the interest portion generally falls while the principal portion rises. The schedule should reach approximately zero at the end, though cent rounding can leave a small residual that requires a final-payment adjustment.

When the Formula Applies—and Its Limits

This calculation fits a standard installment loan with a fixed rate, equal monthly payments, and a fully amortizing term. It can help explain a quoted payment, estimate total scheduled interest, or provide a baseline for a spreadsheet schedule. It does not reproduce every loan contract.

Variable rates, irregular payment dates, daily interest accrual, deferred payments, interest-only periods, balloon payments, fees added to the balance, and extra principal payments change the schedule. For those situations, calculate each period using the actual contract terms and payment dates rather than relying on a single level-payment formula. The APR itself may also be defined or disclosed differently from the simple periodic-rate assumption used here, so use the lender’s stated rate and payment details.

Common Excel Errors

  • Using 8 instead of 8%: If a cell contains the number 8, divide by 1200 to get the monthly decimal rate. If it contains a spreadsheet percentage value such as 8%, divide by 12 instead. Do not apply both conversions.
  • Mixing years and months: The period count for monthly payments must be the term in years multiplied by 12.
  • Inconsistent signs: Excel’s financial functions use cash-flow direction. Keep principal, payment, and future value signs consistent.
  • Rounding every schedule row: Rounding interest and principal at each step can cause a small ending-balance difference. Decide whether the schedule mirrors cent-rounded transactions or retains precision internally.
  • Comparing unlike totals: Total interest is not the same as total payments. Total payments include repayment of principal; subtracting principal isolates estimated interest.

Frequently Asked Questions

What is the Excel formula for monthly loan interest?
Use =IPMT(APR/12, period, years*12, -principal) when APR is stored as a decimal percentage, such as 8%. The period argument is the payment number, starting at 1.

How do I calculate total amortized interest in Excel?
For a level-payment estimate, calculate the monthly payment, multiply it by the number of payments, and subtract principal. You can also use CUMIPMT to sum interest over a selected range of periods.

Why is Excel showing a negative interest amount?
Excel’s financial functions use positive and negative values to represent money received and paid. A negative result often reflects the cash-flow signs; it does not mean the loan earns negative interest.

Is amortized interest the same each month?
No. With a fixed rate and level payment, interest is generally higher earlier because it is applied to a larger outstanding balance. As principal is repaid, the interest portion falls.

Does this formula include fees or extra payments?
No. The calculator assumes scheduled level payments and excludes fees and extra payments. Those items can change the effective cost or payoff schedule and need separate inputs.

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 | $ | 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 →