When’s the payoff?

I put my home mortgage in an Excel spreadsheet so I could run scenarios:  if we pay $200 extra per month, how long is it till payoff?  What if we do $300 extra per month?  No  extra per month?  Each time I changed the pay-extra amount though, I’d have to scroll down to see what month the payoff would be.

Finding the row

First I needed to somehow search the “Balance” column for the first cell whose value was zero.  This was accomplished with the following function:

=MATCH(0,B:B,0)

Explanation of the parameters:

  1. 0 – the value we’re looking for
  2. B:B – the column we’re searching
  3. 0 – the match type (we need match type 0 because the other search types require that the values be in ascending order but our balance values are in descending order (the same reason we can’t use LOOKUP or VLOOKUP))

This formula gives a value like 117 (the first row where there’s a zero balance).  Now we need to turn that into a date…

Displaying the payoff date

For this we’ll use the INDIRECT function, which takes a cell address as a string.  In my spreadsheet, the balance amounts are in column B and the month/year dates are beside them in column A, so if the row were 117, the formula for showing the month would be =INDIRECT(“A117”) .  Of course, if we knew it was in cell A117, we’d just use A117 instead of INDIRECT(“A117”), but we can’t hardcode the row – we need to get it from the match calculation.  The address of the cell containing our payoff date, then, is:

"A"&MATCH(0,B:B,0)

Final formula

Putting it all together, here is the formula we’ll put near the top of the spreadsheet so that we can quickly see the payoff date as we change the amount of extra paid:

=INDIRECT("A"&MATCH(0,B:B,0))

(Thanks to Aladin for his post to the MrExcel message board, which got me started toward this solution.)

Advertisements

,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s