Excel Formula for Balance Remaining: Simple Examples

Formulas & functions · Updated November 18, 2026

The basic Excel formula for balance remaining is =B2-C2: subtract the amount paid in C2 from the original amount in B2. For example, if the total is $850 and payments so far are $300, the remaining balance is $550; use a running-balance formula instead when you need to track several transactions row by row.

The basic balance-remaining formula

A remaining balance is usually the starting amount less what has already been paid, credited, or otherwise applied. Put the original total in one cell and the amount paid to date in another, then subtract the latter from the former. The result is the amount still outstanding, assuming both numbers use the same currency and refer to the same account or item.

With the original amount in B2 and the total paid so far in C2, enter this formula in D2:

=B2-C2

If B2 contains 850 and C2 contains 300, D2 returns 550. You can replace the cell references with numbers, but references are easier to maintain: change either input and Excel recalculates the balance automatically. Give the cells clear labels such as Original amount, Paid to date, and Remaining so another person can understand the calculation.

The order matters. Subtract payments from the amount owed, not the other way around, unless you deliberately want a negative number to show a credit or overpayment. Also confirm that the payment figure is a total-to-date figure. If C2 contains just the latest payment, subtracting it from the original amount will not account for earlier payments.

Subtracting several payments

If each payment has its own cell, you can subtract their sum from the original total. This is useful for a bill, project, or deposit that is paid in installments. Keep the starting amount in one cell and enter each payment in a single column, with one payment per row.

With the original amount in B2 and payments in C2:C6, enter this formula in D2:

=B2-SUM(C2:C6)

For example, if B2 is 850 and C2:C6 contains 100, 75, 125, and two blank cells, SUM adds the three entered payments to 300, and the formula returns 550. Blank cells in the range are ignored by SUM. When you add another payment within the specified range, the balance updates without changing the formula.

Make the payment range large enough for expected entries, or use an Excel Table if the list will grow frequently. A Table can expand as new rows are added, making formulas and ranges easier to manage. Avoid including a total row in the payment range: summing both the individual payments and their total would count the payments twice.

Tracking a running balance in a ledger

A ledger is a better fit when transactions include both charges and payments. Instead of calculating one final amount, it shows the balance after each row. Use separate columns for the transaction date, charge, payment, and balance. Choose and apply one sign convention consistently; for an amount owed, adding charges and subtracting payments is straightforward.

With the opening balance in D1, a new charge in B2, and a payment in C2, enter this formula in D2:

=D1+B2-C2

If the opening balance is 100, the row has a charge of 40, and the payment is 25, the new balance is 115. For the next transaction, with that row’s balance in D2, its charge in B3, and its payment in C3, enter this formula in D3:

=D2+B3-C3

Fill the second formula down the balance column for later transactions. In Microsoft 365 for Windows, select the formula cell and drag its fill handle down, or copy and paste it into the rows below. Excel adjusts relative references as the formula moves. On Mac, the fill handle and copy-and-paste approach work similarly. If a row is a payment, enter the amount in the payment column and leave the charge cell blank; if it is a charge, do the reverse.

At the end of the ledger, the last balance is the amount outstanding under this convention. A negative value means payments or credits exceed charges. That may represent a credit, but if you want to prevent negative balances, use a capped formula for a simple total instead; do not hide a genuine credit if it matters to your records.

Calculating a balance for one item in a list

When a workbook has multiple customers, projects, or invoices, you may want the remaining amount for just one selected item. A practical layout has a name or ID in column A, invoice amounts in B, payments in C, and the item you want to summarize in E2. Use the same consistent spelling or ID in the records and the selection cell.

With names or IDs in A2:A10, invoice amounts in B2:B10, paid amounts in C2:C10, and the item to summarize in E2, enter this formula in F2:

=SUMIF(A2:A10,E2,B2:B10)-SUMIF(A2:A10,E2,C2:C10)

Suppose Oak appears on two rows, with invoices of 1,200 and 800 and payments of 500 and 800. If E2 contains Oak, the formula adds Oak’s invoices to get 2,000, adds its payments to get 1,300, and returns 700 remaining. SUMIF adds values for rows whose matching cells meet the selected criterion. This approach is useful for a small summary without manually filtering and adding the records.

Check that each payment is recorded against the correct customer or project and that the ranges cover the same rows. If an invoice and its payment are on separate records, the layout or formula needs to account for that structure; otherwise a payment might not be matched to the right item. For more complex reporting, an Excel PivotTable or a carefully structured transaction table may be easier to audit.

Balances on loans and installment plans

A simple subtraction formula works for a loan balance only when the amount being subtracted is the portion that reduces principal. A regular loan payment can include interest and fees as well as principal. Subtracting the entire payment from the original principal will therefore understate the amount still owed whenever part of the payment goes to interest or other charges.

With the starting principal in B2 and cumulative principal repaid in C2, enter this formula in D2:

=B2-C2

If the starting principal is 20,000 and principal repaid to date is 4,500, the calculated principal remaining is 15,500. That example does not include interest, fees, payment timing, or other loan terms. For the lender’s current payoff amount or an amortization schedule, use the lender’s figures and check the actual agreement or ask the lender or a qualified professional. This spreadsheet example is arithmetic, not financial advice.

Handling blanks, overpayments, and negative results

With a basic subtraction formula, empty input cells are treated as zero. That can be convenient, but a blank total may then display as zero and look like a completed calculation. If you want the result to remain blank until an original amount is entered, use an IF test.

With the original amount in B2 and payments to date in C2, enter this formula in D2:

=IF(B2="","",MAX(0,B2-C2))

If B2 is blank, the result is blank. If B2 contains 850 and C2 contains 900, the subtraction would be -50, but MAX returns 0. This is suitable when the display should never show a negative amount due. It is not suitable when you need to identify overpayments: use the uncapped subtraction formula for that purpose, or add a separate column to flag credits so the information is not lost.

For a ledger, avoid capping every row at zero unless that is truly the accounting rule you need. A capped balance can conceal a credit or an incorrectly recorded transaction. Decide first whether a negative value means an error, a refund due, or a credit balance, then choose a formula and label that reflects the meaning.

Formatting and checking the result

Excel stores the balance as a number; currency formatting changes how it looks, not the underlying calculation. Select the balance cells and use Home > Number to choose a currency or accounting format. The same general ribbon route is available in Excel for Mac, though the ribbon layout can vary slightly by version. Match the currency and decimal places to the source amounts, especially if the workbook combines imported data.

Before relying on a balance, check these details:

  • Confirm the starting total and payment entries came from the right source and use the same units.
  • Check that payments are entered once only and are not also included in a separate total.
  • Verify the formula references the intended cells and the full payment or transaction range.
  • Test the calculation with a small example you can work out by hand, then compare the result.
  • Keep input cells distinct from formula cells so a later edit does not overwrite the calculation.

If a result does not change after editing an input, make sure the cell contains a formula rather than a typed number. Also check whether calculation is set to automatic: on Windows, use Formulas > Calculation Options > Automatic. Excel for Mac has calculation options under Formulas as well. If the result displays many decimal places or appears without a currency symbol, adjust its number format rather than changing the arithmetic.

Common mistakes to avoid

A frequent mistake is subtracting only the latest payment from the original amount. If the original amount is 850 and there have been payments of 100, 75, and 125, subtracting only 125 gives 725, not the correct 550. Use a cumulative paid-to-date value or sum all payment entries.

Another issue is mixing transaction types. In a ledger, payments reduce an amount owed, while charges increase it. If both are entered in one column without a consistent sign convention, a formula may add a payment when it should subtract it. Separate charge and payment columns, or document a clear positive/negative convention.

Finally, distinguish a balance remaining from a payoff quote. A basic formula does not automatically include pending transactions, accrued interest, fees, refunds, or timing rules. It is only as complete as the inputs and logic in the sheet. For a record that others will use, label whether the result is principal remaining, unpaid invoice total, or a ledger balance, and note the date through which transactions have been included.

Frequently Asked Questions

What is the simplest formula for balance remaining in Excel?
Subtract the amount paid from the original amount. If the total is in B2 and payments to date are in C2, use =B2-C2. The result is the unpaid amount.

How do I subtract multiple payments?
Put each payment in its own cell and subtract the sum of the payment range from the original total. For instance, if the total is in B2 and payments are in C2:C6, use =B2-SUM(C2:C6).

How do I calculate a running balance?
Add each new charge to the previous balance and subtract payments on that row. With the previous balance in D1, charge in B2, and payment in C2, use =D1+B2-C2, then fill the formula down.

Why is my remaining balance negative?
A negative result means the amount subtracted is greater than the starting amount, or payments and charges may have been entered with the wrong sign. It can indicate an overpayment or credit. Check the inputs before deciding whether to cap the displayed result at zero.

Does this formula calculate a loan payoff amount?
No. Simple subtraction can show principal remaining if you subtract principal repaid, but it does not calculate interest, fees, or a lender’s payoff figure. Check the lender’s current terms and figures for an actual payoff amount.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.