Windfall to Pre-Pay Mortgage?

Learn Excel from MrExcel, Podcast Episode 2161: Windfall to Pre-Pay the Mortgage.

Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Today's question is from Karen. Karen gets a windfall of $12,000 and she's having the discussion with her family, should they use some or all of that to pre-pay their mortgage, and the question is how much sooner will our mortgage be paid off if we apply that?

Alright, so, now, you can do this yourself. Just grab your last mortgage statement, figure out what the mortgage balance is, the rate should be on there, and then, for the payment, make sure to get just the principal + interest, not the escrow piece of that, alright, and what we're going to do is figure out how many months you have left to pay. So, NPER, number of periods, and I can never remember all the settings here, so we'll hit the FX button or CTRL+A. So, the rate is going to be that interest rate, but that's an annual interest rate so we'll divide it by 12. The current payment, just principal and interest, is that number, but actually it's a –,  alright, so – that number because it’s money coming out of your bank, and then the present value of the balance of the mortgage is that. So, right now, for this long, 90.65 payments left. Why isn't it an exact number? Well, because we might have sent in some extra principal before, alright, but that's a pretty close number for what's going on. [=NPER(B4/12,-B5,B1)]

Now, I know we just plug in various values here but I want to be able to see all the various items. So, we're going to do this data table. We take this current model, leave that result of the model there in column B cell 7, and then, going down the side, the various possible values that we could use to plug in there to the windfall amount. Alright, select this whole range like this. This is going to be a one-variable data table. On the DATA tab, WHAT-IF ANALYSIS, DATA TABLE -- that really should be called a sensitivity analysis; bad name for it-- there. So, the ROW INPUT CELL, there's nothing across the top row so we leave that blank, but, down the left-hand column, we'll take these numbers in the left-hand column, plug them into that cell one at a time. What it's going to do is it's going to run this little model, this little simple little model, here 1, 2, 3, 4, 5 times, and we'll get to see the results.

Alright. Now, today, I’m recording this on October 21st. If I want to know what the pay-off date is going to be, so, we’re going to use = TODAY + the DATE, how many years, 0 years, how many months, 90.6507 months, and how many days, 1 day, and it will tell us when we’re going to pay that off. Now, that’s the right answer, wrong format. Go to the HOME tab, change from GENERAL to a SHORT DATE, and March 22nd 2025 if we do nothing. If we take the whole amount and plug it in, we will be paying that off in July of 2024. So how much difference is that? DATEDIF from the earlier date to the later date, in just months. Looks like it is 8 months early if we take that $12,000 and pay it. Now, if we had just of course done 8 months of extra payments, you’re looking at $15,514. So, hey, you’re going to save $3,500 if you take that entire windfall and pay it off early. [=TODAY()+DATE(0,B8,1)] [=datedif(C12,C8,“m”)] [=8*B5]

Well, these are the types of things that are in my book, Power Excel With MrExcel. Click that i on the top right-hand corner to learn more about buying the book.

Alright. Topics in this episode: question is, if we make a lump-sum payment against our mortgage, how will the number of payments change? First step: plug in some current values from your mortgage statement. Then use the NPER, number of periods, function -- I forgot how many payments are left -- if you don't make a prepayment, and then a one-variable data table to see the impact if you do pre-pay the whole amount, all $12,000 or any portion of that $12,000, and then there, at the end, use the DATE function with the number of months to go out from today, figure out what the payoff is, and then DateDif, DateDif to figure out the number of months between two dates.

Well, I want to thank Karen for showing up at my seminar in Fort Myers and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.