Mortgage Amortization With Extra Payments in Excel: What a Prepayment Really Buys

A landlord in Tampa finally has a rental that cash flows. The tenant is solid, the $240,000 loan at 6.5 percent has 27 years left, and there is an extra $300 a month sitting in the account doing nothing. The obvious move, the one every personal finance blog screams about, is to throw that $300 at the mortgage and "save a fortune in interest." Maybe. But how much exactly, and is it actually the best home for that money when you own rentals? Most owners never find out. They either prepay on faith or sit on the cash out of inertia. Both are guesses, and guesses with five figures attached are expensive.

A mortgage amortization with extra payments Excel model turns the guess into a number. It shows the exact payoff date, the precise interest you avoid, and what the same dollars would buy somewhere else, all on one screen you control. Free online payoff calculators hand you a headline and hide the schedule. When you own property, the schedule is the whole point, because the real decision is not "is paying down debt good," it is "is paying down this particular debt better than the next thing I could do with the cash." Here is how to build the sheet, and how to use it to make that call instead of guessing.

The Question Online Payoff Calculators Refuse to Answer

Drop your loan into any free payoff calculator and it returns something like "pay $300 extra and save $123,000." That number is true and almost useless on its own, for three reasons.

• It hides the amortization schedule. You see a total, not the month-by-month balance, so you cannot test a one-time lump sum in March, a higher payment for two years then back to normal, or what your balance will be if you sell in year seven.
• It ignores the tax treatment. On a rental, mortgage interest is a deductible operating expense. Cutting $123,000 of interest over the life of the loan also cuts $123,000 of deductions, so the real after-tax return on prepaying is lower than the rate printed on your note.
• It pretends the money has no other job. Every dollar you bury in principal is a dollar not in reserves, not in the next down payment, and not earning anything you can reach without a cash-out refinance that costs 2 to 3 percent to execute.

Excel fixes all three because you own every cell. Build the model once and you can underwrite the prepay decision on every door you hold in about a minute each.

Build the Amortization Schedule So Excel Shows the Whole Story

The engine is a standard amortization schedule with one extra column for additional principal. Start with a clean inputs block so every assumption lives in a labeled cell and nothing is buried inside a formula.

The PMT in B6 returns the normal principal-and-interest payment, $1,516.94 a month. Entering the loan amount as -B2 flips the sign so the payment comes back positive and stays easy to read. That $1,516.94 is the contractual minimum. The extra $300 in B5 is the lever you are testing.

The Four Formulas That Drive the Schedule

Lay the schedule out starting in row 11 with five columns: period, beginning balance, interest, principal paid including extra, and ending balance. Four formulas run the entire thing, dragged down for 360 rows.

• Beginning balance (B11): =B2 for the first row, then =E11 in B12 to carry last month's ending balance forward.
• Interest for the period (C11): =B11*$B$3/12. The lender charges interest on the current balance, not the original loan, which is why early payments are almost all interest.
• Principal paid including extra (D11): =MIN($B$6-C11+$B$5,B11). Scheduled principal is the payment minus interest, then you add the extra $300. The MIN against the balance stops the final payment from overshooting into a negative balance.
• Ending balance (E11): =B11-D11.

The first three months of the $240,000 loan with $300 extra look like this:

Notice that the interest column shrinks every month and the principal column grows, even though your total outlay is fixed at $1,816.94. That is the snowball: the more principal you knock down, the less interest accrues, the more of next month's payment attacks principal. Add a payoff flag in column F so the sheet tells you when it is done: =IF(E11<=0,"PAID OFF","").

Two Formulas for the Headline in Five Seconds

If you do not want to drag 360 rows just to see the result, two functions give the answer instantly. The new payoff length in months:

=NPER(B3/12,-(B6+B5),B2) → 232.7 months

And the months you cut off the loan:

=B4-NPER(B3/12,-(B6+B5),B2) → 127 months, about 10.6 years

The loan that was supposed to run 30 years now retires in 19.4. That is the number worth staring at, because a paid-off rental throws off roughly $1,500 a month in fresh cash flow more than ten years earlier than the bank planned.

What $300 a Month Actually Buys

Put the base case and the extra-payment case side by side and the trade becomes concrete.

You spend roughly $69,800 of extra principal over those 19 years and avoid about $123,300 of interest. On the surface that is a fat, guaranteed win. But read it the way an investor has to. The interest you avoid is a 6.5 percent return, and on a rental that interest was deductible, so the after-tax yield on prepaying is closer to =B3*(1-0.24), or about 4.9 percent in a 24 percent bracket. Safe, guaranteed, and completely illiquid. That last word is the catch, and it is why the calculator alone cannot make this decision for you.

Prepay or Redeploy: The Decision the Sheet Hands Back to You

The honest framing is that prepaying a mortgage is a risk-free investment that pays your interest rate, less the tax shield. So the only question is whether $300 a month earns more, on a risk-adjusted basis, somewhere else. Model the three realistic homes for that cash on one tab and compare them directly.

Run the comparison and the rule of thumb falls out. If your next rental genuinely clears a 10 percent cash-on-cash hurdle and you already hold reserves, redeploying beats prepaying a 6.5 percent note by a wide margin, and your tenant is already paying that loan down for you anyway. If you have no deal in the pipeline, the loan rate is 7 percent or higher, or you simply value a free-and-clear property as you approach the point where you stop buying, prepaying is a clean guaranteed return that also lifts monthly cash flow once the loan dies. There is no universally right answer. There is only the right answer for your numbers, which is exactly what the sheet exposes.

Reserves Come First, Every Time

Before a single extra dollar goes to principal, fund reserves. Equity you prepaid into a rental cannot fix a $9,000 HVAC failure or cover a two-month vacancy. You cannot call the bank and ask for last year's extra payments back when the water heater floods the unit. Carry at least six months of PITI per door in cash first. Prepaying while under-reserved is how owners end up force-selling a good property at a bad time to raise money they had already buried in the walls.

Test a Lump Sum Before You Send the Check

The schedule earns its keep when the extra payment is not a tidy $300 every month. Say a flip closes or a tax refund lands and you are weighing a one-time $20,000 against the loan. Drop that $20,000 into the extra-principal cell for a single month, month 12, and leave the rest of the column at $300. The ending-balance column instantly recomputes the new payoff date down the entire schedule.

A $20,000 lump sum applied early in the loan cuts roughly three years off the term and saves close to $50,000 in interest, more than double the cash you put in, because you are killing principal while the balance is still huge and interest-heavy. Send that same $20,000 in year 22 and the saving is a fraction of that, since most of the interest has already been paid. The sheet shows you the exact figure for your loan and your timing, and it shows why early dollars are worth far more than late ones. That is a decision no static online calculator will model for you, and it is the difference between a guess and a plan.

Stop Guessing and Build the Model

Prepaying a rental mortgage is not a math problem you solve once. It is a recurring portfolio decision: every time you have spare cash, you are choosing between a guaranteed 5 percent and whatever your next deal returns. A mortgage amortization with extra payments Excel schedule gives you the guaranteed side of that comparison to the dollar, but you still need the other side, the real cash flow and cash-on-cash return of the property and the next one you would buy.

That is what the SheetCraft Rental Property Analyzer handles out of the box. It already models each door's cash flow, cap rate, depreciation, and loan, so you can see what a paid-off mortgage does to your whole portfolio's monthly income and what the next acquisition would return, side by side, with the amortization built in. Drop in your loan, set the extra payment, and the prepay-or-redeploy answer stops being a gut call and becomes a number you can defend. Build the schedule once, and you will never again wonder whether that extra $300 is working as hard as it could be.