MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Windfall to Pre-Pay Mortgage?

October 24, 2017 - by Bill Jelen

Windfall to Pre-Pay Mortgage?

Karen just inherited $12K. If she makes a single lump-sum payment on her mortgage, how will that affect the number of payments until the mortgage is paid off?

Watch Video

  • If we make a lump-sum payment against our mortgage, how will the number of payments change?
  • First step: plug in current values from your mortage statement.
  • Then: Use NPER function to figure out how many payments are left
  • Use a one-variable Data Table to see the impact on future payments.
  • Using the DATE function with a number of months
  • Using DateDIF function to figure out the number of months between two dates

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2161 windfall to prepay the
  • mortgage hey welcome back to the mr.
  • Cassell NetCast I'm Bill Jelen today's
  • question from Karen Karen gets a
  • windfall of $12,000 and she's having a
  • discussion with her family should they
  • use some or all of that to prepay their
  • mortgage and the question is how much
  • sooner will our mortgage be paid off if
  • we apply that all right 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 the payment
  • make sure to get just the principal plus
  • interest not the escrow piece of that
  • all right and what we're going to do is
  • figure out how many months you have left
  • to pay so n pair 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 gonna be that interest rate
  • but that's an annual interest rate so
  • we'll divide by 12 the current payment
  • just principal and interest is that
  • number ah but that's number it's
  • actually it's a - all right so - that
  • number gives us money coming out of your
  • bank and then the present value the
  • balance of the mortgage is that so right
  • now for this loan ninety point six five
  • payments left why isn't it an exact
  • number well because we might have
  • prepaid some you know send in some extra
  • principal before all right but that's
  • that's a pretty close number for what's
  • going on now I know if you just plug in
  • various values here but I want to be
  • able to see all the various items so
  • we're gonna do this data table we take
  • this current model leave that result of
  • the model there in column B cells 7 and
  • then going down the side the various
  • possible values that we could use to
  • plug in there to the windfall amount all
  • right it's like this whole range like
  • this there's gonna 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 left
  • hand call and plug them into that cell
  • one time what it's going to do is it's
  • gonna run this little model this little
  • simple little model here one two three
  • four five times and we'll get to see the
  • results all right now today I'm
  • recording this on October 21st if I want
  • to know what the
  • payoff date is gonna be so we're gonna
  • use equal today plus the date and how
  • many years zero years how many months 90
  • point six 507 months and how many days
  • one day and it will tell us when we're
  • gonna 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 to twenty second twenty twenty
  • five if we do nothing if we take the
  • whole amount and plug it in we will be
  • paying that off and July of 2024 so how
  • much difference is that date diff from
  • the earlier date to the later dates in
  • just months it looks like it is eight
  • months early if we take that twelve
  • thousand dollars and pay it now if we
  • just of course done eight months of
  • extra payments you're looking at fifteen
  • thousand five fourteen so hey you're
  • gonna save thirty five hundred dollars
  • if you take that entire windfall and pay
  • it off early well these are the types of
  • things that are my book power Excel with
  • MrExcel click that I on the top right
  • hand corner to learn more about buying
  • the book
  • all right topics in this episode
  • question is we make a lump sum payment
  • against our mortgage how will the number
  • of payments change first step plug in
  • some values from your mortgage statement
  • then use the N pair number of periods
  • function forgot how many payments are
  • left if you don't make a prepayment and
  • then one variable data table to see the
  • impact if you do pre pay the whole
  • amount all twelve thousand dollars or
  • any portion of that twelve thousand
  • dollars and then there at the end using
  • the date function with a number of
  • months to go out from today figure out
  • what the payoff is and then date diff
  • diff to figure out the number of months
  • between two days well I want to thank
  • Karen for showing up my seminar in Fort
  • Myers I don't well thank you for
  • stopping by we'll see you next time for
  • another net cast from mister Excel

Download File

Download the sample file here: Podcast2161.xlsm

Title Photo: themusingsofmo / Pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.