As I reach a need for some number crunching I usually came up with a spread sheet that helps me compute on the fly in an easier manner. It seems that this year I will be doing more of these evaluation, both for myself and my close friends so I decide to put this up, just in case there are people who needs it.
This is not comprehensive, but can be a rather good starting point for many people interested in evaluating properties.
This calculator would help you compute
- Your amount of loans based on % of loan
- Your monthly mortgage, interest schedule
- The total payment and total interest payment
- Whether you are able to meet TDSR and MSR requirement
- The net rental cash flow after expenses
- The consideration expenses
- The net cash flow not covered by rental and needs to come out from take working pay
- Negative Equity Potential Top Up Amount
- Rental yield performance
- Asset Appreciation Performance
This does not factor in
- Stamp duty
You can assess the blue print of the spread sheet here>
To make use of the spread sheet you will require a Google Account.
To make a copy of this spread sheet and start using, go to File > Make a copy and then you can start working.
In most of my spread sheet, the cells with formulas (which you should not be touching) are in light blue and those that requires your input are in light grey.
Purchase and Sale
I decide to walk through this mostly with a couple earning 170k per annum in total looking to purchase a Condo going for $1 million. The condo loan to value are taken from another blogger’s consideration yesterday, which is to borrow 75% of the house value.
The first sheet, which is purchase & sale helps with your home purchase evaluation. With many of the regulations coming in to place, you would need something to help see if you are able to meet the TDSR and MSR.
(click to see larger image)
Maximum Tenure Limit provides a glimpse of roughly how long you can borrow the loan for. This is subjected to change based on the regulations. And for this part it is one that I am quite unsure about (so do check other sources and update accordingly)
You specify the house cost, how much loan to value you wish to borrow, how long you would like to borrow follow by the interest rate. The mortgage, monthly payment, total payment and total interest will be computed.
Play around with the interest rate and loan tenure as well as the loan to value would let you derive a comfortable payment amount.
When it comes to TDSR computation, your fixed pay, variable pay, liquid assets, non-liquid assets, both pledged and unpledged comes into play. For most of the levels based on current rules, you can take a look at the Ref worksheet, where most of the relatively constant data is.
Based on the TDSR computation, there are haircut to your salary, and assets that goes into the TDSR computation. The After deduction section, shows you the amount that comes into consideration.
In the case of this person, who has $100k of liquid assets unpledged, only $30k is inconsideration, and a stock portfolio of $370k unpledged, only $111k is under consideration.
Whether you have existing debts in the form of mortgage payment per month, car loan payment per month, and various debts, they will affect your TDSR as well, and in the section below you can specify accordingly.
The Net Income after deduction provides a monthly review of the combine income in consideration (based on the couple’s pay). The Asset Value after deduction provides a monthly review of any additional asset value pledge and unpledged that goes into the computation. The Net TDSR Income will show after all consideration, what is the couple’s income level for TDSR computation.
In this case, this relatively good earning couple was able to pass both MSR and TDSR. (in the case of MSR, it is based on net income after deduction)
Of course, the Loan Amort sheet provides a break down of how the interest go down over time and the principal monthly payment go up over time.
Rental, Cash Flow, Valuation and Performance
The rental, cash flow, valuation and performance sheet provides the sort of evaluations required if you are looking out about the different permutations based on changing of rents, expenses, taxes to your prospective cash flow.
Note that non of the values in this sheet is linked to other sheets.
The sheet contains probably too much stuff, but lets focus more on the orange highlighted part, which is an evaluation of rental.
The right column provides a cash flow evaluation. You can fill in your monthly rental or prospective monthly rental for renting out the place. In this case I use 3500 which is what the blogger used per month. This looks rather low for a condo, but coincidentally a 900 SQFT Kovan Melody was quoted to be renting $3300. So I am not too far off here ( as a value add a 1291 SQFT in the same place seem to be asking for $4600)
Most of the figures are consolidated to an annual figure. We have to take into account some expenses. Majority of these expenses are tax deductible (conservancy, insurance, property tax, agent fee and maintenance. We will get the EBIT. To this we will deduct the interest expense and income tax (due to the increase in your personal income).
We will arrive at Net Rental Income, the net rental income factors in all the above except the mortgage principal. This is probably the cash flow that you would be interested in.
Of course, usually rental is the way to finance the house. Net Additional Cash Flow Not Covered by Rental is net of the mortgage principal. It shows how much additional finance from the couple’s gross pay they need to finance the house.
To gauge whether the property as a rental proposition is attractive, the rental performance section on the left provides that. The rental yield on house cost shows the yield based on the asking rent commanded. In this case it is 4.21% based on the house cost of 1 million.
The net rental yield on house cost shows the performance after factoring all the expenses that comes with renting. I find this a bit low at 0.94%. I hope i didn’t get anything wrong.
The yield on equity shows the performance when leverage becomes a factor. The numerator used is the net rental income.
Yield on house value provides the yield on current value. (Your house appreciates in value and will deviate from the cost)
Rental evaluation is one thing, roughly how much capital gains after costs over the years is another thing. To me, the couple’s total outlay includes, interest paid, renovation, furnishings and maintenance. Most folks seem to be only looking at how much they paid for the house not these stuff.
I raised the asset value after 30 years to 4 million, just like the blogger said. Factoring all the interest, renovations, the gain per year comes to 3.78%. I have to admit, this is not the best way to compute compounded growth rate, since the interest is paid over the years. The cost approximate to present value will be different. But this is the best I can come up with.
You may not agree with the values, so you can vary them according to the scenario that you would like to evaluate. I am just one man, so I would miss out some stuff, so if you can value add I am all ears.
Have fun playing around and hope you can find or have a great house.