Reducing Balance Loan Calculator Excel: Formula & Example

A reducing balance loan calculator in Excel estimates a fixed monthly payment and shows how each payment is divided between interest and principal as the balance falls. Enter the loan amount, annual interest rate, and term; the calculator below returns the monthly payment and total interest, using a standard fixed-rate amortization formula.

Formula

The calculator uses the fixed-payment loan formula. It calculates the payment from the principal, periodic interest rate, and number of monthly payments, then subtracts the original principal from the sum of the rounded payments to estimate total interest.

Monthly payment: M = round[P × r ÷ (1 − (1 + r)⁻ⁿ), 2]. Total interest: I = M × n − P.

• P is the starting loan principal, in dollars.
• APR is the annual interest rate entered as a percentage.
• r is the monthly rate, calculated as APR ÷ 1,200.
• n is the number of monthly payments, calculated as years × 12.
• M is the monthly payment, rounded to the nearest cent.
• I is estimated total interest, in dollars, based on that rounded payment.

For an Excel worksheet, put the principal in B1, APR percentage in B2, and term in years in B3. The payment formula is =ROUND(B1*(B2/1200)/(1-(1+B2/1200)^(-B3*12)),2). If that result is in B4, estimated total interest is =B4*B3*12-B1. These formulas assume the APR is entered as a number such as 8 for 8%, not as the decimal 0.08.

Worked Example

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

1. Convert the APR to a monthly rate: r = 8 ÷ 1,200 = 0.0066667, or about 0.66667% per month.
2. Find the number of payments: n = 5 × 12 = 60.
3. Substitute into the payment formula: M = round[20,000 × 0.0066667 ÷ (1 − (1.0066667)⁻⁶⁰), 2] = $405.53 per month.
4. Calculate interest using the rounded monthly payment: I = $405.53 × 60 − $20,000 = $4,331.80.

The displayed payment and total interest follow the calculator formula, which uses the rounded payment for every month. A real loan’s final payment may be adjusted by the lender to clear a small remaining balance caused by rounding.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are equal monthly payments, made at the end of each monthly period.
• Interest accrues monthly using the APR divided by 12; the APR is treated as a nominal annual rate for this calculation.
• The estimate excludes fees, taxes, insurance, PMI, and escrow, as well as late charges or other account-specific adjustments.

How a Reducing Balance Loan Works

With a reducing balance method, interest for a period is calculated on the outstanding principal, not on the original loan amount for the entire term. For a monthly schedule, a basic estimate is opening balance × monthly rate. The payment first covers that period’s interest; whatever remains reduces principal.

At the start of the example, estimated first-month interest is $20,000 × 0.0066667, or about $133.33. From a $405.53 payment, roughly $272.20 goes toward principal, leaving about $19,727.80. In later months, interest is calculated on that lower balance. As a result, the interest portion generally declines while the principal portion rises, even though the scheduled payment stays level.

This differs from a flat-rate calculation, which may calculate interest against the original principal throughout the term. The words used in loan documents can vary, so check the lender’s stated method and repayment schedule rather than assuming a label alone describes the calculation.

Building an Excel Amortization Schedule

The two formulas above provide a payment and estimated total interest, but a month-by-month schedule makes the changing balance easier to inspect. Add columns for payment number, opening balance, payment, interest, principal repaid, and closing balance.

1. Enter the initial principal as the first opening balance.
2. Calculate monthly interest as opening balance multiplied by APR/1200.
3. Set principal repaid to payment minus interest.
4. Calculate closing balance as opening balance minus principal repaid.
5. For the following row, use the prior closing balance as the new opening balance, then fill the formulas down for the full term.

For a spreadsheet, keep the rate and payment cells fixed in formulas when copying them down, using absolute references such as $B$2 and $B$4. Carry full precision in intermediate calculations and display cents with cell formatting. Rounding interest and balances to cents in every row can make the final balance differ slightly; a lender may handle the last payment differently.

When to Use the Estimate and Its Limits

This calculator is useful for understanding the approximate scheduled payment and interest under a simple fixed-rate arrangement, or for checking whether a spreadsheet’s basic formula is set up correctly. It is not a complete payoff quote or a substitute for the lender’s amortization schedule.

It does not model extra payments, missed or late payments, a changing rate, payment dates that do not fall monthly, fees added to the balance, or a different compounding convention. Extra principal payments, for example, change later balances and interest; simply multiplying the original fixed payment by the term will not describe that altered schedule. To estimate such cases, make a separate schedule that applies each actual payment and any extra principal to the outstanding balance.

Use the actual principal, rate, and term shown in the lender’s documents. The rate input is an assumption, not a current market quote, and APR disclosures can include items that this simplified monthly formula does not model. The result is an estimate; the lender’s disclosure gives the exact figures.

Frequently Asked Questions

What is the Excel formula for a reducing balance loan payment?
For a fixed monthly rate, use =ROUND(P*(APR/1200)/(1-(1+APR/1200)^(-years*12)),2), where APR is entered as a percentage number such as 8. The formula returns a level monthly payment rounded to cents.

Is reducing balance the same as amortization?
They are closely related but not identical terms. Reducing balance describes calculating interest on the outstanding principal; amortization describes paying down a debt over time through scheduled payments. A standard amortizing loan commonly uses a reducing balance calculation.

Why does the principal portion increase over time?
When the balance falls, the next period’s interest charge is generally lower. If the payment remains level, more of that payment can then go toward principal.

Does the calculator include fees or extra payments?
No. It assumes the stated principal is the full amount financed and excludes fees and additional payments. Add those items to a separate schedule only when you know how the lender applies them.

Why might my Excel result differ from a lender’s figure?
Differences can come from rounding, the exact payment dates, the lender’s rate and compounding conventions, fees, or a final adjusted payment. Compare the assumptions and use the lender’s disclosure for the exact repayment figures.

Related

• Balance Transfer Loan Calculator and How It Works
• Balance With Age? Estimate Your Loan Balance Over Time
• Balance of Payments Formula for JC Business

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