This guide shows you how to calculate the remaining loan balance at any point in time using Excel’s built-in financial functions. You’ll need the original loan amount, interest rate, term, and the number of payments already made. By the end, you’ll have a formula that returns the exact outstanding principal, which is useful for tracking your mortgage, auto loan, or personal loan.
Formula
The formula for the remaining balance B after k payments is:
B = P × (1 + r)^k – PMT × [((1 + r)^k – 1) / r]
Where:
- P = original loan amount (principal) in dollars
- r = monthly interest rate (APR divided by 12, expressed as a decimal; e.g., 8% APR → 0.08/12 = 0.0066667)
- k = number of payments already made (months)
- PMT = monthly payment amount in dollars (calculated as P × r / (1 – (1 + r)^(-n)), where n = total number of months)
In Excel, you can use the built-in function FV (Future Value) to compute the same result:
=FV(rate, nper, pmt, [pv], [type])
For a loan balance, use: =FV(r, k, pmt, -P, 0) – note the negative sign on P to get a positive balance.
Worked Example
Using the default values from the calculator: P = $20,000, APR = 8%, term = 5 years (60 months).
Step 1: Calculate the monthly interest rate.
r = 8 / 1200 = 0.0066667
Step 2: Calculate the monthly payment.
PMT = 20000 × 0.0066667 / (1 – (1 + 0.0066667)^(-60))
= 20000 × 0.0066667 / (1 – 0.671216)
= 20000 × 0.0066667 / 0.328784
= 20000 × 0.020276
= $405.53 (rounded)
Step 3: Suppose you want the balance after 24 payments (k = 24).
B = 20000 × (1.0066667)^24 – 405.53 × ((1.0066667)^24 – 1) / 0.0066667
= 20000 × 1.172888 – 405.53 × (0.172888 / 0.0066667)
= 23457.76 – 405.53 × 25.9332
= 23457.76 – 10516.68
= $12,941.08
So after 2 years, you still owe about $12,941.08. The calculator above gives the same result for those inputs.
Assumptions
- Fixed interest rate for the entire term (no rate changes).
- Payments are made at the end of each month (ordinary annuity).
- Interest compounds monthly at APR/12.
- This calculation does not include fees, taxes, insurance, PMI, or escrow amounts.
How to Calculate Loan Balance in Excel – Step-by-Step
- Open Excel and create a new worksheet. Label cells for clarity: A1: “Loan Amount”, A2: “APR”, A3: “Term (years)”, A4: “Payments Made”, A5: “Monthly Payment”, A6: “Remaining Balance”.
- Enter your loan details in column B. For example, B1: 20000, B2: 8% (format as percentage), B3: 5, B4: 24 (the number of payments already made).
- Calculate the monthly payment (if you don’t already have it). In B5, enter:
=ROUND(B1*(B2/12)/(1-(1+B2/12)^(-B3*12)),2)– this gives the fixed monthly payment. - Use the FV function to find the balance. In B6, enter:
=FV(B2/12, B4, B5, -B1, 0). Press Enter. The result is the remaining balance after the specified number of payments. - Format the result as currency by right-clicking B6, selecting Format Cells, and choosing Currency.
- Check the result by comparing it to the calculator above. For the default values, B6 should show $12,941.08 after 24 payments.
- Make it dynamic: change B4 to any number of payments (0 to total months) to see the balance at that point. You can also create a small table with payment numbers and balances to see the amortization schedule.
Common Mistakes
- Using APR instead of monthly rate: Always divide the APR by 12 in the formula. Forgetting this will give a wildly incorrect balance.
- Sign errors in the FV function: The present value (PV) must be negative to get a positive balance. If you get a negative number, check the sign of B1.
- Rounding the payment too early: Use the exact payment from the PMT formula or the ROUND function to 2 decimals. If you round manually, the balance may be off by a few cents.
- Mixing up term and payments made: The term is the total number of months (years × 12), while payments made is how many you’ve already paid. Using the term in the FV function’s nper will give the balance at the end (which should be zero).
- Ignoring payment timing: This formula assumes payments at the end of each month. If your loan has payments at the beginning (rare), the balance will differ slightly.
Tips
- Use Excel’s
PMTfunction to double-check your monthly payment:=PMT(B2/12, B3*12, B1). - To see the full amortization schedule, list payment numbers in a column and use the balance formula for each row.
- If you want to know the balance after a partial period (e.g., mid-month), the formula gets complex; it’s best to use whole months.
- For extra payments, you’ll need to adjust the payment amount and recalculate – the simple formula assumes a constant payment.
- Always format interest rates as percentages in Excel to avoid decimal errors.
Troubleshooting
- #NUM! error: This usually means the interest rate is negative or the term is zero. Check that APR > 0 and term > 0.
- #VALUE! error: Text or empty cells in the formula range. Ensure all referenced cells contain numbers.
- Balance is negative: You likely used a positive PV in the FV function, or you’re looking at the balance after more payments than the term. The balance should be positive and decrease to zero.
- Balance doesn’t match your lender’s statement: Lenders may include fees, round differently, or use a slightly different compounding method. This formula gives the pure principal balance; your lender’s disclosure is authoritative.
- Payment amount seems too high or low: Verify the APR is entered as a percentage (e.g., 8% not 0.08) and the term is in years, not months.
Frequently Asked Questions
What is the FV function in Excel?
FV stands for Future Value. It calculates the value of an investment or loan after a series of payments. For loans, it returns the remaining balance when you input the rate, number of periods, payment, and original amount.
Can I use this for a loan with a variable interest rate?
No, this formula assumes a fixed rate. For variable-rate loans, you’d need to recalculate the balance at each rate change, which is more complex.
Does this include extra payments I make?
No, the formula uses a constant monthly payment. If you make extra payments, you’ll need to adjust the payment amount and possibly the number of periods to get an accurate balance.
Why is my calculated balance slightly different from my lender’s?
Lenders may round differently, include fees, or use daily compounding. This formula gives the standard amortization balance; always refer to your lender’s statement for the exact figure.
How do I calculate the balance after a specific date, not just a number of payments?
You need to know how many payments you’ve made up to that date. Typically, that’s the number of months since the loan started. If you’ve made partial payments, you’ll need to prorate, but that’s rare.
Can I use this for a loan with a different payment frequency (e.g., bi-weekly)?
Yes, but you’ll need to adjust the rate and number of periods. For bi-weekly, use r = APR/26 and n = years × 26. The formula still works.
This calculation provides an estimate for educational purposes. Your lender’s official statement will show the exact remaining balance.
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
- QNB Car Loan Payment Calculator and Guide$405.53
- Car Loan Payment Calculator and Formula$405.53
- VA Down Payment Assistance Calculator and Guide$15,000.00
- Low Down Payment Home Loans: Costs and Trade-Offs$405.53
- Down Payment vs Advance Payment: Key Differences
- DIB Bank Car Loan Calculator and Payment Guide$405.53