calculate-loan

01 Loan payments

Current Balance in Excel: Formula and Calculator

Calculator No. 01-15570 · Updated · Formula and assumptions shown below

Monthly payment $405.53
Total interest
$4,331.80
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

Current balance in Excel is the amount remaining at a chosen point in a loan’s repayment schedule. Enter the starting loan amount, annual APR, and term to estimate the monthly payment, then enter the number of payments made to calculate the balance left.

Formula

For a fixed-rate loan with equal monthly payments, the balance after k payments is calculated as:

B = P(1 + r)^k − M × ((1 + r)^k − 1) / r

  • B is the remaining balance in dollars.
  • P is the original principal, in dollars.
  • r is the monthly interest rate as a decimal, calculated as APR ÷ 1200 when APR is entered as a percentage per year.
  • k is the number of monthly payments already made.
  • M is the monthly payment in dollars.

The calculator above establishes a level monthly payment from the entered principal, APR, and term. Its payment formula is M = round(P × r / (1 − (1 + r)^−n), 2), where n is the total number of payments (years × 12). Total interest is estimated as the rounded monthly payment multiplied by the number of payments, minus the original principal.

Worked Example

Using the calculator’s default values: principal is $20,000, APR is 8%, and the loan term is 5 years. The monthly rate is 0.08 ÷ 12, or 0.0066667, and the number of monthly payments is 5 × 12 = 60.

  1. Monthly payment before rounding: $20,000 × 0.0066667 ÷ (1 − (1 + 0.0066667)−60) ≈ $405.53.
  2. Rounded monthly payment: $405.53.
  3. Total of scheduled payments: $405.53 × 60 = $24,331.80.
  4. Estimated total interest: $24,331.80 − $20,000 = $4,331.80.

The calculator’s outputs are the monthly payment and total interest for these defaults. To get a balance at a particular point, apply the balance formula above with the relevant payment count. For example, after 12 payments, set k to 12 and use the rounded payment of $405.53; the estimated balance is about $16,500. The exact result can differ slightly because payment dates, rounding, and loan servicing calculations may vary.

Assumptions

  • The rate is fixed for the full term.
  • Payments are equal, made monthly at the end of each period, and begin on schedule.
  • Interest compounds monthly at APR ÷ 12.
  • The estimate excludes fees, taxes, insurance, PMI, escrow, and any extra or missed payments.

Using a Current-Balance Formula in Excel

Excel can calculate a balance directly if the loan details are placed in cells. For example, put the original principal in B1, annual APR as a decimal in B2, total payment count in B3, and payments made in B4. Put the monthly payment in B5. A formula for the remaining balance is:

=B1*(1+B2/12)^B4-B5*((1+B2/12)^B4-1)/(B2/12)

If APR is entered as a percentage number such as 8 rather than as 0.08, use B2/1200 in place of B2/12. Keep the input convention consistent; mixing these formats changes the rate by a factor of 100. The payment in B5 should be the actual scheduled payment, rounded to cents, if the goal is to match a statement or amortization schedule.

Excel’s FV function is another way to model the balance, but its sign convention can look confusing: cash paid out and money owed are often represented with opposite signs. A direct formula can be easier to audit because the principal, rate, payment count, and payment are visible. Whichever method you use, verify the first-period and final-period results against the schedule.

How to Read and Check the Result

A current balance is not necessarily the same as the sum of future payments. Each payment generally covers interest accrued for that period and reduces principal by the amount left over. Early in a level-payment loan, interest is calculated on a larger principal balance, so a larger share of a payment can go toward interest. As principal falls, the interest portion usually falls too, while the principal portion rises.

  • At k = 0, the formula should return the original principal.
  • As payments are made, the balance should generally decrease, provided payments cover interest and are made as assumed.
  • After all scheduled payments, the calculated balance should be approximately zero; small discrepancies can result from cent rounding.
  • A lender’s payoff amount may include accrued interest through a particular date or other contract-specific amounts, so it may not equal a simple scheduled balance.

For a spreadsheet schedule, calculate each month’s interest as the opening balance times the monthly rate, then subtract that interest from the payment to find principal repaid. Subtract principal repaid from the opening balance to get the next month’s opening balance. This row-by-row approach makes it easier to include extra payments or irregular dates, although timing conventions must be represented accurately.

Common Errors and Formula Limits

The most common Excel mistakes are using APR as a decimal in one place and a percentage number in another, using years where the formula expects months, and counting payments incorrectly. If 18 payments have been made, use k = 18, not the number of years elapsed. Check whether a spreadsheet’s APR cell contains 8% (stored numerically as 0.08) or the number 8 before choosing the divisor.

The formula describes a fixed-rate loan with regular, equal payments. It does not automatically account for variable rates, payment holidays, late payments, fees added to principal, daily interest calculations, or extra principal payments. For those cases, model each period from the actual dates, rates, and transactions. A statement balance can also use a different “as of” date than the date of the latest payment, so compare like dates when reconciling figures.

Rounding matters over many installments. This calculator rounds the scheduled payment to cents and uses that rounded payment to estimate total interest. A lender may calculate interest using its own rounding and payment-posting rules. Use the lender’s statement or disclosure for an exact contractual figure rather than treating a spreadsheet estimate as a payoff quote.

Frequently Asked Questions

What is the Excel formula for current loan balance?
For fixed monthly payments, use =P*(1+r)^k-M*((1+r)^k-1)/r, where r is the monthly rate, k is payments made, and M is the payment. With APR entered as 8, monthly r is 8/1200.

Can Excel calculate a loan balance after a set number of payments?
Yes. Enter the number of completed payments as k in the balance formula. Make sure the payment count, monthly rate, and payment amount all use the same schedule.

Is current balance the same as payoff amount?
Not always. A balance calculated from scheduled payments may not include interest accrued since the last payment or amounts specific to the loan agreement. Ask the lender for a dated payoff figure when one is needed.

Why does my Excel balance become negative?
Common causes include an incorrect rate conversion, too many payments counted, an overstated payment, or a payment entered with the wrong sign in a financial function. Review the units and check the balance after the first and final scheduled payments.

Can I include extra payments?
Yes, but the level-payment formula alone does not model them. Add each extra principal payment to a month-by-month amortization schedule and calculate the next balance from the actual payment and interest for that period.

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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 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

The whole Loan payments section →