To find a loan balance after a set number of payments in Excel, use the balance formula or Excel’s FV function with the loan’s monthly rate, payment, and number of payments made. Enter the original loan amount, annual APR, term, and payments completed to estimate the remaining principal.
Formula
The balance after k payments is calculated as:
Bk = P(1 + r)k − A × ((1 + r)k − 1) / r
- Bk is the remaining balance in dollars after k payments.
- P is the original principal, in dollars.
- r is the monthly interest rate as a decimal: annual APR ÷ 1200.
- k is the number of monthly payments made.
- A is the fixed monthly payment in dollars.
The calculator uses the standard level-payment formula to estimate A: P × r ÷ (1 − (1 + r)−n), where n is the number of monthly payments in the full loan term. Its headline balance is for zero payments made because the required inputs do not include a payment-count field. In Excel, replace k with the number of payments completed, or use the FV method below.
Worked Example
Using the calculator’s defaults: a $20,000 principal, 8% APR, and a five-year term. The term is 5 × 12 = 60 monthly payments, and the monthly rate is 8 ÷ 1200 = 0.0066667.
- Monthly payment before rounding = 20,000 × 0.0066667 ÷ (1 − (1.0066667)−60), or approximately $405.53.
- The payment rounded to cents is $405.53.
- At k = 0, the balance formula gives $20,000 × 1 − $405.53 × 0, so the balance is $20,000.00.
- Total scheduled payments using the rounded payment are $405.53 × 60 = $24,331.80; estimated total interest is $24,331.80 − $20,000 = $4,331.80.
The $20,000 balance is the correct result for zero payments completed. To calculate after, for example, 12 payments, use k = 12 in the balance formula. The exact result can differ slightly from a lender’s statement because of payment posting dates, rounding, or charges not modelled here.
Using the Formula in Excel
One direct Excel formula is useful when the payment amount is in B2, the monthly rate in B3, the original principal in B4, and payments made in B5:
=B4*(1+B3)^B5-B2*((1+B3)^B5-1)/B3
For example, if APR is in a cell as 8%, calculate the monthly rate with =APR_cell/12. If it is entered as the number 8 rather than a percentage-formatted 8%, use =APR_cell/1200. The number of payments made must be a count, such as 12 for one year of monthly payments.
Excel’s FV function can also return the balance. With the monthly rate in B3, payments made in B5, monthly payment in B2, and initial principal in B4, use:
=-FV(B3,B5,B2,-B4)
Excel’s cash-flow sign convention treats amounts paid and amounts received as opposite signs. The minus signs in this example are arranged to display the remaining balance as a positive number. If your cells use different signs, the result may appear negative even though its magnitude is correct.
When to Use a Loan-Balance Calculation
A balance-after-payments estimate helps answer questions such as how much principal remains after a period of regular payments, how a payoff quote differs from the original amount borrowed, or how much of a payment schedule has reduced principal. It is not the same as adding up unpaid payments: each payment covers interest accrued for that period before reducing principal.
For a fixed-rate amortizing loan with monthly payments, the balance formula assumes each payment arrives on schedule and that interest accrues at the same monthly rate. The first payment usually contains more interest than principal; as the balance falls, the interest portion generally falls and more of each level payment reduces principal.
When using a spreadsheet, keep the full precision of the calculated payment in the formula if that is how the loan is modelled. If the actual payment is rounded to cents, use that rounded amount consistently. Small differences can accumulate across many periods, so a spreadsheet estimate may not match a statement to the cent.
Common Mistakes and Limits
- Confusing APR with a monthly rate: For this monthly model, APR entered as a percentage number is divided by 1200. Using 8 instead of 0.0066667 as the monthly rate produces a meaningless result.
- Counting payments instead of months: Use the number of payments actually made, not the full loan term, unless all scheduled payments have occurred.
- Using the wrong payment sign in FV: Excel’s FV function follows cash-flow sign conventions. Check the signs and compare the result with the direct formula.
- Assuming a balance is a payoff quote: A payoff figure may account for interest through a particular date and other amounts that this basic calculation excludes.
- Applying a monthly model to a different schedule: Weekly, biweekly, irregular, variable-rate, or interest-only arrangements need their own payment timing and interest assumptions.
The calculator’s balance output is deliberately set to zero payments because payment count is not one of its inputs. In a spreadsheet, use the stated formula with your own k value, or add a payment-count input to a customized calculator. Extra payments should be modelled period by period if their timing and amount vary.
Assumptions
- The rate is fixed throughout the term.
- Payments are equal, made monthly, and applied at the end of each monthly period.
- Interest is modelled monthly at APR ÷ 12.
- Fees, taxes, insurance, PMI, and escrow are not included.
Frequently Asked Questions
What is the Excel formula for a loan balance after payments?
Use =P*(1+r)^k-A*((1+r)^k-1)/r, where P is principal, r is the monthly rate, k is payments made, and A is the monthly payment.
Can Excel’s FV function calculate the remaining loan balance?
Yes. For example, use =-FV(monthly_rate,payments_made,monthly_payment,-principal), adjusting signs to match the signs of your cash flows.
How do I convert an annual APR to a monthly rate in Excel?
If APR is entered as a percentage number such as 8, use =APR_cell/1200. If the cell contains 8% as a percentage value, use =APR_cell/12.
Why does my Excel balance differ from my lender’s balance?
The model may use a different payment date, rounding method, interest calculation, or treatment of fees and extra payments. Compare the spreadsheet’s assumptions with the loan statement or lender’s disclosure.
Does the balance formula include extra payments?
No. It assumes equal scheduled payments. For extra payments, calculate each period’s interest and subtract the actual payment from that period’s balance, accounting for when the extra amount is applied.
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 bal | Balance after payments | $ | p * (1 + apr / 1200) ^ 0 - pmt * ((1 + apr / 1200) ^ 0 - 1) / (apr / 1200) | 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
- United Test and Balance: What It Means in Finance
- Up-to-Date Loan Balance Calculator$16,611.20
- Trade Date Balance: Meaning and How It Works
- UBS Trade Date Balance: Meaning and How It Works
- Time Left on Loan Calculator: Estimate Payoff Time60 months
- The Balance in Journaling: Finding a Sustainable Practice