Mortgage Recast Calculator in Excel or Google Sheets

Put four numbers in cells (balance, rate, years left, lump sum) and use PMT twice: once on the old balance, once on the balance after the lump sum. On $300,000 at 4.5% with 25 years left, a $50,000 lump sum takes the payment from $1,667.50 to $1,389.58.

The layout below works in Excel and in Google Sheets. Check your sheet against the calculator further down this page.

The layout, cell by cell

Type the labels in column A and the values or formulas in column B.

CellLabelType thisExample result
B1Current balance300000$300,000
B2Yearly rate4.5%4.5%
B3Years left2525
B4Lump sum50000$50,000
B5Payment now=-PMT(B2/12, B3*12, B1)$1,667.50
B6Balance after lump sum=B1-B4$250,000
B7Payment after recast=-PMT(B2/12, B3*12, B6)$1,389.58
B8Saved each month=B5-B7$277.92

Microsoft says to keep the units the same: a yearly rate divided by 12 goes with the number of months. Here the months are the years times 12. Mixing yearly and monthly units is a common reason a sheet disagrees with a calculator.

Interest saved, and the fee break-even

Add these cells to compare total interest if you pay every month to the end of the loan. They leave out escrow.

CellLabelType thisExample result
B9Interest, old balance=B5*B3*12-B1$200,249
B10Interest after recast=B7*B3*12-B6$166,874
B11Interest saved=B9-B10$33,375
B12Recast fee (your quote)250$250
B13Months to cover the fee=ROUNDUP(B12/B8, 0)1

The $250 fee here is an example, not a quote. Ask your servicer for the real one, then see how much a recast costs for the break-even on other lump sums.

Interest for the first year

Microsoft documents CUMIPMT as the interest paid on a loan between two periods. To see how much of the first 12 payments is interest, use it with a minus sign. For the example loan this is $13,364 on the old balance and $11,137 after the recast.

=-CUMIPMT(B2/12, B3*12, B6, 1, 12, 0)

Change 1 and 12 to 13 and 24 for the second year. The last argument, 0, means payments are made at the end of each month. The recast amortization schedule guide shows balance and interest by year for the same loan.

Check your sheet against the calculator

Enter the same four numbers below. The new payment should match B7 to the cent. If it does not, check the rate cell first, then the years.

$
%
years
$

What the sheet will not tell you

  • Your servicer's exact figure. It depends on your exact balance and months left on the recast date.
  • Your escrow. Taxes and insurance are outside a principal and interest sheet.
  • Eligibility and fees. Ask your servicer, or see the recast fees and rules by lender.

For the formula behind PMT, see how to calculate a recast payment. For a ready-made check, see what to check in any recast calculator.

Frequently asked questions

Is there a built-in recast function in Excel?

No. A recast is a new payment on a smaller balance, so you build it with the payment function (PMT) on the balance after your lump sum.

Why does PMT give me a negative number?

PMT treats the loan as money you receive and the payment as money you pay out. Put a minus sign in front of PMT, as in the layout above, and the payment shows as a positive number.

Do the same formulas work in Google Sheets?

Yes. PMT and CUMIPMT take the same arguments in Google Sheets as in Excel, so the layout above works in either one.

What if my rate is typed as 4.5 instead of 4.5%?

Then the sheet reads 450%. Format the rate cell as a percentage and type 4.5%, or divide by 100 in the formula.

Does the sheet include escrow or the recast fee?

Escrow is not in it, because a recast changes principal and interest only. The fee is its own cell and is not taken from the balance.

Sources

Every figure on this page comes from the pages below. Checked 2026-10-20. Rules change, so confirm with your own servicer.

More recast guides