Calculate Loan Balance After n Payments in Excel

To calculate a loan balance after n payments in Excel, use =PV(rate,nper,-pmt) when the loan has level payments, or calculate the balance directly with =loan*(1+rate)^n-pmt*((1+rate)^n-1)/rate. This calculator uses the direct balance formula: enter the original loan, APR, term, and number of payments made to estimate the remaining principal.

Formula

The balance after k payments is the original principal grown by interest for k periods, minus the accumulated value of the payments made:

Bₖ = P(1 + r)ᵏ − M × [(1 + r)ᵏ − 1] / r

• Bₖ is the estimated remaining balance after k payments, in dollars.
• P is the starting loan principal, in dollars.
• r is the periodic interest rate as a decimal. For monthly payments and APR entered as a percentage, r = APR / 1200.
• k is the number of payments already made.
• M is the regular monthly payment, rounded to cents.

The payment is calculated from the loan amount, rate, and full term: M = round(P × r / [1 − (1 + r)−N], 2), where N is the total scheduled number of monthly payments, or years × 12. Rounding the payment to cents matters because real payment schedules normally collect currency in cents; using an unrounded payment can make a small difference in the remaining balance.

Worked Example

With the calculator’s defaults, the loan is $20,000 at 8% APR for five years, and 12 payments have been made. The monthly rate is r = 8 / 1200 = 0.0066666667, and the scheduled payment count is N = 5 × 12 = 60.

1. Calculate the monthly payment: round(20000 × 0.0066666667 / [1 − (1.0066666667)−60], 2) = $405.53.
2. Find the growth factor after 12 payments: (1 + 0.0066666667)12 ≈ 1.082999506.
3. Apply the balance formula: 20000 × 1.082999506 − 405.53 × (1.082999506 − 1) / 0.0066666667 ≈ $16,411.65.

The displayed estimate after 12 payments is $16,411.65. The payment and balance are rounded to cents for display; a lender’s actual schedule can differ by a few cents because of payment dates, daily interest, or its rounding method.

Assumptions

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

Using the Formula in Excel

In an Excel sheet, put the principal in B1, APR as a percentage in B2, term in years in B3, and payments made in B4. For a formula that calculates the monthly payment and then the balance, use:

=B1*(1+B2/12)^B4-ROUND(-PMT(B2/12,B3*12,B1),2)*((1+B2/12)^B4-1)/(B2/12)

Here B2 must be entered as a decimal percentage, such as 8%, not the number 8. Excel’s PMT function returns a negative amount when the loan principal is positive, so the formula uses a minus sign to convert it to the positive payment amount. If APR is entered as the number 8 instead of 8%, use B2/1200 for the monthly rate throughout.

Another approach uses Excel’s PV function to find the present value of the remaining scheduled payments. For a level-rate loan, enter =-PV(monthly_rate, total_payments-payments_made, monthly_payment). For example, if the payment is in B5, the monthly rate is B2/12, and the total payment count is B3*12, the corresponding expression is =-PV(B2/12,B3*12-B4,B5). This is useful as a cross-check, but it relies on the same fixed-rate, equal-payment assumptions. Confirm the sign convention and payment timing when adapting the formula.

Reading the Result and Common Errors

The remaining balance is not the same as the total of payments still due. Future payments include interest that has not yet accrued, while the calculated balance estimates principal outstanding at the point immediately after payment k. It is also not necessarily the exact payoff amount on a particular day: accrued interest since the last payment and any contract charges may change the amount needed to close the loan.

• Mixing percent formats: 8% is 0.08 in an Excel cell; if you type 8 as a plain number, divide it by 1200 to obtain a monthly rate.
• Using the wrong payment count: enter payments actually made, not months elapsed if a payment was missed or made early.
• Rounding too early: round the regular payment to cents before using it in the balance formula if you want to match a cent-based schedule.
• Counting the first payment incorrectly: the formula assumes payment 1 comes at the end of the first period. A payment made at the start of a period changes the result.
• Using the formula for a different loan structure: variable rates, interest-only periods, irregular payments, and additional principal payments require a period-by-period schedule or a formula that accounts for those details.

At 0% interest, the formula containing division by the monthly rate is undefined even though the balance is straightforward: subtract total payments from principal. For a zero-interest loan with level payments, use principal − payment × payments made. This calculator requires a positive APR, so it does not handle that special case.

When This Estimate Is Useful

A balance estimate can help check an amortization schedule, understand how principal declines over time, or compare a spreadsheet’s result with a lender statement for the same date and assumptions. To model extra payments, calculate each period separately: add that period’s interest to the prior balance, then subtract the regular payment and any extra principal. Do not treat an extra payment as though the regular payment or interest rate changed unless the loan terms actually do so.

For a precise figure, use the lender’s payment history and ask for the balance or payoff amount for the relevant date. The spreadsheet formula cannot infer payment delays, rate changes, fees, or the lender’s day-count method from the original loan details alone.

Frequently Asked Questions

What is the Excel formula for a loan balance after n payments?
For monthly payments, use =P*(1+r)^n-M*((1+r)^n-1)/r, where r is the monthly rate and M is the regular payment. Make sure n counts payments already made.

Can I use Excel’s PV function to calculate the remaining balance?
Yes. Use the present value of the remaining payments, such as =-PV(monthly_rate,remaining_payment_count,monthly_payment), with consistent units, signs, and end-of-period timing.

Does the balance include interest?
The formula includes interest accrued through the payment periods modeled, but the displayed balance is an estimated principal balance after payment n. Interest accruing after that point is not included.

Why does my Excel balance differ from my lender’s?
Possible causes include a different posting date, daily rather than monthly interest calculations, payment rounding, missed or extra payments, fees, or a variable rate. Compare the exact dates and transaction history before interpreting a difference.

How do I calculate a balance after extra payments?
Track the balance period by period: multiply the prior balance by one plus the period rate, then subtract the regular payment and extra principal. This direct formula assumes identical scheduled payments and does not account for irregular extras.

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

Related

• Federal Student Loan Interest Rates: How They Work
• Compound Interest on a Loan: How It Works
• Education Loan Interest: Simple or Compound?

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
in k | Payments made | - | 12 | 0 | 600
out bal | Balance after payments | $ | p * (1 + apr / 1200) ^ k - round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) * ((1 + apr / 1200) ^ k - 1) / (apr / 1200) | 2
out pmt | Monthly payment | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out n | Scheduled payments | - | yrs * 12 | 0