| Home | Numbers | I Like |

Monday 9 March 2020

The Higher Grade Bond Calculator

Smashing a Home Loan 101
I should be ashamed of myself!

Towards the end of last year I released a calculator that allowed you to track the progress of paying off your bond, and estimate the date that the home loan would be squashed, based on the monthly outstanding balance.

However there was a pretty big oversight on my part…

Almost immediately after I released the spreadsheet calculator, I started receiving requests from people – please could I make a version that would accommodate extra repayments going forward.

Poor form Stealthy! 

You need to remember that people who read your blog are either properly on top of their finances, or will be soon, and so obviously they have realised the ugly side of home loans and plan on making extra payments into their bonds!

And now that I have chastised myself in the third person, I present to you a peace offering in the form of a brand new, shiny, home loan tracking spreadsheet – The Higher Grade Bond Calculator!

This one factors in extra monthly payments going forward so you can really see how to properly f-up your home loan!

And if you don't have a home loan, but want to just use the spreadsheet as a bond calculator which factors in extra payments,it does a pretty good job of that too.

There will be a link to download the calculator a little later on, but first let’s check out some action shots of the spreadsheet doing it’s thing!

Let’s say you had a R1 Million home loan taken in January 2020 over 20 years @9.75%. This results in a monthly payment of R9485.17 and an estimated end date of December 2039.

Yawn – a run of the mill 20 year pay off that many South Africans happily accept.

But let’s say you have been dropping some extra cash into the loan, and so in March 2020, you find your outstanding balance is R990,000 (versus the original loan schedule amount of R995,886.25).

The payoff chart now looks like this (click for a larger image):

The time to payoff the loan has been reduced by 4 months. Nice!

Okay, and now, since you are in the Higher Grade personal finance class, you have decided that going forward you are going to be making an extra R500 monthly payment towards the loan. Time to smash some interest!

The payoff input now looks like this:

And the loan projection graph now looks like this (click for larger image):

Smashing a home loan!

The loan will be paid off almost 3 years earlier, saving you over R320k! Some people might call this WINNING!

Right, as promised, here is the link to download the spreadsheet : The Higher Grade Home Loan Tracker. (It has also been added to the Spreadsheet Page so it can mingle with all the other spreadsheets I've released)

And here is how you use the calculator…
  1. On the Summary Tab, enter the parameters for your home loan. These are shaded grey, and include the original loan amount, the starting month of the loan, and the interest rate you signed the loan at.
  2. The original calculated end date is then shown (shaded in orange)
  3. Switch over to the “Actuals” tab, and you will see there is a row for every month of the home loan duration. The shaded grey column is where you can insert your actual outstanding balance for the given month.
  4. If you already have a home loan that is a few months/years old, then you do not need to enter all the balances for all the preceding months. The spreadsheet understands your dilemma, and you can just enter your last known actual outstanding balance at the appropriate month in the “Actuals” tab and the spreadsheet will sort itself out (it’s a pleasure, if we ever cross paths, you can buy me a beer ;))
  5. After you have inserted one or more actual values, you can switch back to the “Summary” tab and you will see an updated chart which reflects your actual balance progression (in a solid blue line) and this will then be projected forward (dashed blue line). The shaded blue cells will show the projected end date, and how many months sooner you will be home loan free (based on the projection).
  6. This updated spreadsheet now also has a row marked in green, which you can use to enter the additional monthly payment you plan on making from now going forward. It is kinda fun making the dashed blue line dance around by changing the amount, and really inspiring to see how even smallish monthly amounts can pull the loan pay off date many years forward. Leave this at a value which you plan on using going forward.
  7. Then, for each passing month, insert your actual outstanding balance in the "Actuals" tab to see if you are staying on track. If you get a raise, or manage to cut out some expenses, increase the monthly additional payment into your bond and see how the calculator pulls your end date closer and closer. I am hopeful that tracking your progress towards your “home loan freedom date” will spur you on even more!

Have fun!

Till next time, Stay Stealthy!
 - ~ - ~

If you enjoyed this post, it has been scientifically proven that you have a 96.78% chance of liking future posts.
Don’t argue with statistics, sign up to the mailing list and get the newest stuff delivered to your inbox!