It’s incredible how far the world of finance and investing has come in such a short time. With new apps and services like Acorns and Betterment breaking into the market all the time, investing is becoming far more accessible to a much larger segment of the population. Budgeting and financial planning software has also come a long way. YNAB and Mint are excellent examples of affordable (or even free) options to assist with achieving your future financial goals.
Working in the tech industry as a member of a finance team, I’ve developed a love for many new FinTech innovations. Personally, I’m a huge fan of Mint when it comes to building a budget and building a spending plan. With that being said, I have a confession to make. My absolute favourite budget prep program is actually Microsoft Excel.
I know! You’re thinking, “What’s wrong with this guy? There’s so many options on the market for building a budget. Why after trying Mint would he ever use Excel to build a budget?”
My answer is pretty simple. While services like Mint can build you a custom budget and present it in an easy-to-use platform, they're still limited in what they can do. In Excel you have much more freedom to design your budget and customize it to your unique situation. So why don’t more people use Excel for budget planning? Because services like Mint are so much easier to learn. Mint does most of the work for you! Excel’s weakness is its learning curve, but its strength is that if you know how to use it there’s so much you can do with it.
I still use Excel to build basic month-over-month budgets as well as to plan for some of my more substantial financial goals.
I recently had my eyes on a condo that was for sale. I knew I couldn’t afford it right away, but I decided that I could probably afford a condo of around the same price within a year or two. I decided to build a spreadsheet to help me calculate three things:
(1) How much I’d need to save for a down payment
(2) What the monthly costs would be once I had taken ownership
(3) What my monthly budget would look like with the addition of the new monthly costs
Regarding the down payment, I needed to determine how much I’d need to save in order to put 10% down on the home. When it came to the monthly costs, I had to determine what my monthly net income would need to be to afford the monthly mortgage payments and condo fees. I'll be taking you through the process of building a spreadsheet to determine the amount you’ll need to save for the initial purchase of a big ticket item. I'll also demonstrate how to calculate what the associated monthly costs will be after the initial purchase.
Throughout this tutorial I’m going to be using various formulas. Some will be simple, some a bit more complex. I’ll add in notes for each formula so that you can replicate them yourself in your own spreadsheet.
Step One: Determine the Initial Cost
The first thing you'll need to do is determine what the cost of purchasing the big ticket item will be. In my case, the condo I was eyeing was posted for $350,000.00. The price may have been negotiable, but I decided to be conservative and assume the seller would stand firm at the asking price. I also figured there would be additional costs incurred by purchasing the condo, e.g. closing costs which I estimated at 1.5% of the price of the condo + $500 (for any unexpected costs).
(In Cell B7)
Likewise I added in the cost of purchasing new furniture ($10,000) and the cost of renting a moving truck ($500). You can see these costs in the orange box on the left.
I calculated the down payment in the green box on the right. In my province in Canada there is a government program for first-time home buyers allowing for a loan of up to 5% of your down payment. With that in mind I calculated my contribution to the down payment as 5% of the price of the condo, with the other 5% being contributed by the government. This brought my portion of the down payment to $17,500.
(In Cell G5)
This brought the total initial purchase cost of the condo to $33,750.
(In Cell B11)
Step Two: Calculate your Monthly Mortgage Payment
Now that we’ve determined that I’ll need a minimum of $33,750 for the initial purchase of the condo, I have left to determine what the cost will be each month after the purchase is made. The monthly cost will be an estimation rather than an exact number. Your mortgage payments are likely to change over the course of the time you own the home, but this calculation should serve you well for at least the first few years of ownership. Let’s start by calculating the monthly mortgage payment.
So as you can see, in order to calculate my monthly mortgage payment I will need 3 pieces of information: the total amount mortgaged, the number of years over which the mortgage will be amortized, and the rate of interest on the mortgage.
To calculate the monthly payment I started by taking the initial purchase cost of $350,000 and subtracted the down payment of $35,000 (my portion + the government’s portion). I then assumed my mortgage would amortize over a 25 year period, and I ran a quick simulation via my bank’s online mortgage calculator to figure out my mortgage rate (2.59%). I then used the following formula to calculate my monthly mortgage payment:
(In Cell G17)
This formula is a bit more complicated and warrants an explanation. I used the PMT formula for my calculation. I started by inserting a minus sign so that the formula would return a positive number. I then divided the mortgage rate in G16 by 12 because the 2.59% interest is for the full 12 months of the year. Subsequently I multiplied the amount in G15 by 12 because there will be 12 payment periods each year (one per month). I then used the total amount mortgaged in box G14 as the present value of my purchase, but you will notice that I left the space between the next two commas blank. This is because the next spot in the formula is for the future value of the purchase which is not relevant to our calculation. Finally, the 1 at the end of the formula simply signifies that payments will be at the beginning of each payment period. If you want the payments to be at the end of each period, use a 0.
If that was all gibberish to you, that’s fine! Simply use the formula I’ve listed above and change the numbers as needed.
Step Three: Calculate the Total Monthly Costs associated with your purchase
Finally, let’s calculate my total monthly costs once I own the condo. We’ll start by pulling in the monthly mortgage payment that we calculated in Step Two above. Next I’ve added in the monthly payments on the government loan I’d be receiving for the purchase. If you recall, the government in my province will pitch in 5% of the down payment on my first home purchase. That loan must be repaid within 10 years, so I’ve simply divided the total loan from the government ($17,500) by 10 years and then again by 12 months to get $145.83 per month.
(In Cell B15)
If you are buying a house and not a condo you can ignore the line for condo fees. In my case I estimated about $300 per month for condo fees and $200 a month for utilities. This amount may be higher or lower depending on the home you purchase.
The last amount we need to calculate is the property tax. You may have to do a bit of research to determine what the average property tax rate is in your district. In my area the property tax is roughly 7% for every $1,000 assessed. The calculation is a bit confusing, but the city divides the property value ($350,000) by 1,000 and then multiplies that number by 7. So for a $350,000 home, the annual property tax ends up being $2,450 (350,000 / 1000 * 7). Divide that by 12 and you have the estimated monthly cost of your property tax.
(In Cell B18)
And there you have it! Use the SUM function to get the total monthly cost of owning your home after the initial purchase.
(In Cell B19)
So in addition to the initial payment of $33,750 we calculated in Step 1, I will also have to make payments of $2,274.39 each month. As I stated above, this number is likely to change over time, but it’s a good place to start. Your monthly cost should not vary too much from the calculated amount for at least the first few years of ownership.
Thanks so much for taking the time to read through my tutorial. I hope you enjoyed it! Leave me a comment and let me know what you think. Do you feel that I could have improved my spreadsheet in some way? Would you like me to elaborate on the way I calculated certain numbers, or the formulas I used? Leave me a question and I’ll try my best to answer.