# How to calculate the remaining balance on a loan in Excel

Use FV with the payment from PMT: `=-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)` is what is still owed after `B5` payments.

**Excel:** `=-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Car loan | Value |
| Amount borrowed | 25000 |
| Annual interest rate | 0.065 |
| Term (years) | 5 |
| Payments made | 24 |
| Remaining balance |  |

Formula in B6: `=-FV(B3/12,B5,PMT(B3/12,B4*12,B2),B2)`

Result: $15,959.86

## How it works

FV rolls the loan forward: it grows the amount borrowed by the monthly interest and takes off each of the 24 payments. PMT supplies the payment, as a negative number because payments go out. FV reports the balance still owed as a negative, so the minus sign in front shows it as a positive amount.

## Other ways

- `=PV(B3/12,B4*12-B5,PMT(B3/12,B4*12,B2))`: Another view: the balance is the present value of the 36 payments still to come.
- `=B2-B5*PMT(B3/12,B4*12,-B2)`: Subtracting the payments made ignores the interest in them, so it understates the balance. (Far too low: $13,260.31.)

## Common mistakes

- Payments are part interest, so after 24 of 60 payments you still owe much more than 60% of the loan.
- Extra payments make the real balance lower than this formula shows. Use a payment schedule if you've paid extra.

## Questions

**How much principal have I paid so far?**

Subtract the balance from the loan: `=B2-B6`, or use CUMPRINC in Excel.

**How do I find the balance on a date?**

Count the payments made: `=DATEDIF(start_date,TODAY(),"m")` in B5.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/remaining-balance-on-a-loan