Loans and mortgages Updated November 2026

Remaining loan balance

The balance of an amortizing loan after k payments equals the present value of the remaining n - k payments: B_k = PMT x (1 - (1 + r)^-(n - k)) / r. In Excel: =PV(rate, n-k, -PMT), or =FV(rate, k, PMT, -P) from the original loan.

Syntax

B_k = PMT x (1 - (1 + r)^-(n - k)) / r

Part	What it is
`PMT`	The fixed periodic payment
`r`	Rate per period
`n`	Total number of payments
`k`	Payments already made

Examples

200,000 mortgage at 6%, 30 years, after 60 payments
PMT = 1,199.10, remaining 300 payments
B = 1,199.10 x (1 - 1.005^-300) / 0.005

Result: B = 186,108.71

Excel: =PV(6%/12, 300, -1199.10) or =FV(6%/12, 60, 1199.10, -200000)

20,000 car loan at 7%, 60 months, after 24 payments
PMT = 396.02
B = 396.02 x (1 - (1 + 0.07/12)^-36) / (0.07/12)

Result: B = 12,825.82

300,000 at 5%, 30 years, after 10 years (120 payments)
B = 1,610.46 x (1 - (1 + 0.05/12)^-240) / (0.05/12)

Result: B = 244,026.19

After a third of the term, 81% of the loan is still owed

Common mistakes

Subtracting k x PMT from the original loan; most of the early payments are interest, so the balance falls far more slowly than that.
Using the unrounded payment in one place and the rounded one in another, which gives balances that differ by a few cents from the lender's statement.
Forgetting that the payoff amount includes interest accrued since the last payment, so it is slightly higher than the scheduled balance.

Practice Remaining loan balance

These drills check your answer on live data, free, no account needed.

Balance after k paymentsA $105,000, 30-year mortgage at 4.25% APR has a monthly payment of $516.54. What is the balance right after payment 180?
Principal part of payment kA $105,000, 30-year mortgage at 4.25% APR has a monthly payment of $516.54. How much of payment number 127 reduces the principal?

Questions

How much principal have I paid after k payments?

Principal paid = P - B_k. On the 200,000 mortgage above, after 5 years only 13,891.29 of principal has been repaid, while about 58,055 has gone to interest.

Can I use CUMPRINC in Excel?

Yes: =CUMPRINC(rate, n, P, 1, k, 0) returns the cumulative principal paid in payments 1 to k (as a negative number). The balance is P plus that result.

Amortization scheduleAn amortization schedule splits each payment into interest and principal and tracks the balance. Worked schedule for a 10,000 loan, Excel IPMT and PPMT.
Mortgage payment formulaHow to calculate a monthly mortgage payment with M = P x r / (1 - (1 + r)^-n). Examples for 200,000 at 6% and 300,000 at 5%, Excel PMT and common mistakes.
Total interest paid on a loanTotal interest on an amortizing loan is the payment times the number of payments minus the principal. Examples for 30-year and 15-year loans, Excel CUMIPMT.
Balloon paymentA balloon loan has smaller regular payments and a large final lump sum. How to find the payment given a balloon, or the balloon given a payment, with Excel.