Diminishing Balance Loan Calculator in Excel

A diminishing balance loan calculator in Excel estimates each payment’s interest from the outstanding principal, then shows how the balance falls over time. Enter the loan amount, annual interest rate, and term to calculate a fixed monthly payment and total interest; the schedule below also explains how to build a reducing-balance table in Excel.

Formula

For a standard fixed-rate loan with equal monthly payments, the payment is calculated using the amortization formula:

Payment = P × r ÷ [1 − (1 + r)⁻ⁿ]

• P is the starting loan principal, in dollars.
• r is the monthly interest rate as a decimal: annual APR ÷ 12 ÷ 100.
• n is the number of monthly payments: loan term in years × 12.
• Payment is the level monthly payment, in dollars.

In the calculator block, the APR is entered as a percentage, so its formula uses apr / 1200 for the monthly rate. Total interest is calculated as the rounded monthly payment times the number of payments, minus the original principal. The Excel schedule allocates each payment between interest and principal; because interest is charged on the remaining balance, the interest share generally declines as principal is repaid.

Worked Example

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

1. Monthly rate: 8 ÷ 1,200 = 0.0066667, or about 0.66667%.
2. Number of payments: 5 × 12 = 60.
3. Monthly payment: 20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = $405.53, rounded to cents.
4. Total of scheduled payments: $405.53 × 60 = $24,331.80.
5. Total interest: $24,331.80 − $20,000 = $4,331.80.

These figures assume the payment is the same each month and use the rounded payment for the total. A lender’s final installment may differ slightly because of its actual payment dates, day-count method, rounding, or adjustments.

Build a Diminishing-Balance Schedule in Excel

The calculator gives the payment and overall interest estimate. To see each month’s changing balance, make a table with columns for period, opening balance, payment, interest, principal repaid, and closing balance. For example, put the principal in cell B2 and the annual APR in a separate input cell such as B3. Use a row for each payment period.

• Opening balance: first row equals the original principal; each later row equals the previous row’s closing balance.
• Payment: use the fixed payment calculated with Excel’s PMT function, for example =-PMT(B3/12,60,B2) when B3 contains 8% as a decimal and B2 contains the principal. The minus sign makes the displayed payment positive under Excel’s cash-flow sign convention.
• Interest: opening balance multiplied by the periodic rate, such as =opening_balance*$B$3/12.
• Principal repaid: payment minus that period’s interest.
• Closing balance: opening balance minus principal repaid.

Copy the period formulas down for the number of payments. Excel’s IPMT and PPMT functions can also return the interest and principal portions for a particular period, but check their sign convention and rate and period arguments. Keep the payment schedule consistent with the calculator’s assumed monthly rate and number of payments.

How to Read the Results

The monthly payment is the modelled amount due each period, while total interest is the estimated cost of borrowing over all scheduled payments, excluding the principal returned. In the schedule, interest is usually higher early on because it is calculated on a larger outstanding balance. As principal falls, less interest accrues, so more of a level payment goes toward principal. This is why “diminishing balance” is also called “reducing balance” or “amortizing” in many loan contexts.

The displayed total interest is not the same as the total paid: total paid includes the original amount borrowed. For the example, $24,331.80 is total scheduled payments, and $4,331.80 is interest. A spreadsheet may show a final balance of a few cents above or below zero if calculations retain full precision but displayed values are rounded. Do not round each intermediate cell unless the loan’s actual terms specify that method.

Assumptions

• The rate is fixed for the full term, and the APR input is treated as a nominal annual rate.
• Payments are made monthly in equal amounts, with the first payment one month after the loan starts.
• Interest compounds monthly at APR ÷ 12; the schedule does not model daily accrual or irregular payment dates.
• Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.
• The final payment is assumed equal to the rounded monthly payment; real contracts may adjust it to clear a remaining balance.

Common Excel Errors and Limits

A frequent error is entering “8” where a formula expects a decimal rate of 8%, or dividing a percentage by 100 twice. Excel’s percent formatting stores 8% as 0.08, so a monthly rate formula for that cell is =APR/12; if the input is the number 8, use =APR/1200. Another error is using the annual rate directly in a monthly schedule. The rate period and payment period must match.

Check that the term is converted to the same number of periods used by the payment formula. A five-year monthly loan has 60 payments, not five. Also confirm that the payment exceeds the interest charged in a period; otherwise the balance may not decline. The calculator’s fixed-payment result does not represent every product called a diminishing-balance loan: some contracts use variable rates, different payment intervals, or principal installments that are not equal. Use the lender’s stated payment method and figures when reconciling an actual account.

Frequently Asked Questions

What is a diminishing balance loan?
It is a loan where interest for a period is calculated on the outstanding principal rather than continually on the original amount. As principal is repaid, the balance used to calculate later interest declines.

Is diminishing balance the same as reducing balance?
Generally, the terms describe the same basic idea: interest is based on the remaining loan balance. Contract details still matter, including how often interest is calculated and whether payments are fixed.

How do I calculate a monthly loan payment in Excel?
For a fixed rate and equal monthly payments, use =-PMT(annual_rate/12, years*12, principal) when the rate is stored as a decimal. If you enter APR as a whole percentage number, divide it by 1200 instead.

Why does my Excel balance not reach zero?
Displayed rounding, rounding applied within each formula, or a payment schedule that differs from the loan’s terms can leave a small residual. Check the rate units, number of periods, payment timing, and whether the final payment should be adjusted.

Does a diminishing-balance calculator include fees?
No. This calculator models principal and interest only. Add separate, verified fee inputs to a spreadsheet if you need to examine their effect; do not treat this estimate as a lender’s payoff figure.

This result is an estimate, and the lender’s disclosure gives the exact figures.

Related

• Trade In an Old Laptop for a New One?
• Trade In an Old Phone: Value, Steps, and Alternatives
• Trade In an Old iPhone: Options, Value and Steps

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