Assuming you don’t have a balloon payment on your fixed rate 30-year mortgage, these two tables may be helpful. Table1 will tell you when your mortgage will be paid off, given making extra payments each month. Table2 is the reverse of Table1, in that you give it the number of years you want to have your mortgage paid off, and it will tell you how much extra you will have to pay each month.
Pretty straight forward. They can all be done with built in mortgage functions. There were some problems however, I had with Table1 that I will explain after we look at the tables.
After the above information is entered, both tables are updated. Table2 is shown below: The payoff year cells are in yellow, which means you can change them if you wish to use factional years. Using the inputs above, if you wanted to pay off your mortgage in 10 years, you would need to pay an additional $2,730.63, for a total monthly payment of $5,766.63.
Table1 is the opposite of Table2. Here you start at an additional $100 extra payment each month (you can change the $100 and the rest of the extra payments will increment down by that same amount) and the rest of the extra payments go up $100. For example, if you wanted to pay an additional $1,000 a month, your mortgage would be paid off in 16 years and 5 months.
I mentioned before about problems I had with Table1. I wanted any fractional month to round up to the next even month. That way, the last payment will be a partial final payment. The first problem was with Excel’s precision when it comes to formulas. Take the example below. The first formula is Excel’s built in formula for number of payments. I entered the data, and extended the payment past the cents, in order to get the precise number of payments, which was 48. Since the answer is an exact 48 payments, I should have no problems when I want to round up the fraction of a month, which in this case should be zero. When I enclosed the NPER function in the MOD function it gave me a number instead of zero. So when I rounded up, I got the number of months as one instead of zero. Why was that? I expanded 48 month answer and found in the 13th decimal place there was the number 5. Chip Person has an article that describes the reasons why you may experience arithmetic errors with rounding in Excel.
My solution was to first round off the answer to the 5th decimal place and then round up. Rounding to 5 places was arbitrary. I thought it would be sufficient to eliminate any incorrect number past 5 places to the right.
The other problem I ran into was rounding up, if the fractional months was above 11 months. I cannot show, for example, 5 years and 12 months. That is 6 years. The two formulas for year and month below, show how I got around both problem:
Download workbook “Two_Mortgage_Tables” from:
Downloads Written in Excel 2013