I was recently faced with a decision on whether we should sell our rental property or hold onto it given it just went cash flow negative due to a special levy and increased maintenance fees. Since the best course of action was immediately obvious, I decided to map out a decision tree in Excel to help me decide.

You can download a copy of the Investment Property – Decision Tree Template and use it or tweak it to your own situation. I’ll walk through how I’ve set it up and used it for my decision.

Firstly, the only decision we are making is between the status quo (continuing to rent the condo) and listing it for sale. We don’t actually get to decide to sell it per se, we only get to decide to list it. Whether it actually sells or not and for what price is not particularly in our control. As such, in the case where we do list it for sale we need to consider a range of possible sales prices as well as the possibility that it doesn’t sell for a reasonable price and we need to continue renting it out.

The spreadsheet is based on a 10-year time horizon for all scenarios. 10 years is a bit arbitrary but is a timeframe I’m comfortable with since we’ve always intended to keep the condo for the long term. Essentially I am comparing the difference in expected future value at the end of the 10 years between the case of selling now and investing the proceeds in equities or other investment versus holding on, continuing to rent it and selling 10 years from now.

If you use the spreadsheet, you can update the values in any of the highlighted cells to suit your own situation and all of the results will be updated accordingly.

## Current values

In the first section of the Excel decision tree template, enter the current or known values for your situation.

*Current market value:* the current assessed or market value of the rental property you own

*Current mortgage balance: *the current remaining balance on your mortgage

*Original purchase price: *the amount you originally paid for the rental property (used to calculate capital gains tax)

*Years owned: *the total number of yours you have owned the rental property

*Years as principal residence: *the number of years (if any) you used the property as your principal residence (used in the capital gains calculation)

*Marginal tax rate: *your current marginal income tax rate (used to calculate capital gains tax)

## Assumptions

In the assumptions section of the decision tree template, enter the values specific to your scenario. After completing the spreadsheet, you can play around with these values to see the impact changes in these assumptions have on the final result.

*Sales transaction costs: *an estimate of the direct costs of selling the rental property. This should include real estate agent commissions and any other closing costs.

*Annualized ROR for non-real estate investments: *what is the average annual rate-of-return you expect to achieve for the next 10 years in the case where you sell the rental property and invest the full proceeds elsewhere.

*Annualized increase of rent: *what is the average annual rent increase you expect to be able to impose over the next 10 years.

*Annualized increase of expenses: *what is the average annual increase in expenses you expect. Expenses include strata fees, maintenance, insurance, property taxes, and any other rental property expenses except for mortgage interest.

*Expected future mortgage rate (low): *the decision tree includes two branches to account for a case where interest rates remain low for the next 10 years versus a case where interest rates rise. You’ll have an opportunity to enter the probability of each scenario later on, but in this cell enter what you expect the mortgage rate to be if rates do stay low.

*Expected future mortgage rate (high): *Enter a mortgage rate that you think is realistic for the case where we do see rising rates over the next 10 years. You’ll have an opportunity elsewhere in the spreadsheet to enter the probability by which you’d expect this to occur.

*Expected future special levy: *Enter an amount that you expect a future special levy could (in total for the 10-year period). You’ll have an opportunity later to enter the probability of this occurring vs the probability of no special levies occurring.

*Tenant turnover costs: *Enter the total costs you would expect to incur during the worst case of tenant turnover (e.g. repairs/updates are needed, advertising costs, and the unit sits empty for a few months)

## Expected future asset value

Complete the table for the calculation of the future value of your rental property. This is used in the scenario where the decision is to hold on and continue renting the property for the next 10 years.

The table supports four different scenarios for modelling. The first line should be what you expect the best case scenario could be. The bottom line should be what you expect the worst case scenario could be. The middle two lines are in between the best case and wort case scenarios. Enter both the probability percentage of how likely you think a particular scenario is to occur as well as the average annual increase in the value of the property.

Make sure your probabilities add up to 100%!

Note that for the worst case scenario, you are entering a total cumulative drop in value rather than an average annual rate.

In my case, I’m assuming there is a 5% chance that the property value will increase by an average of 5% year-over-year. That mind sound conservative for the Vancouver market, but I’m not particularly bullish on the market considering we’ve had such a long run up in prices.

I’m assuming a more likely scenario, with a probability of 50%, is the property value will increase by an average of 3% year-over-year for the next 10 years.

I think there’s actually a decent chance (35%) that values don’t increase at all (or increase and drop back down to today’s levels).

My worst case scenario is that the current market price gets cut in half. I’ve only assigned a 10% probability to that scenario as I think it’s pretty dire. If that actually turned out, we’ll all probably have much bigger problems than worrying about this rental property!

## Rental property net income and cash flow

Next switch over to the second tab in the Investment Property Decision Tree Template. There are a lot of calculated values on this tab and only a few you need to manually enter for your situation (the highlighted cells). This tab is all about the monthly income, cash flows, and mortgage pay down for your rental property over the next 10 years.

*Monthly Rent:* enter the current monthly rent. The future year rents are calculated based on the value you entered in the assumptions section.

*Monthly Expenses: *add up all your expenses including strata fees, maintenance, insurance, property taxes, and any other expenses excluding mortgage interest. For any annual expenses like property taxes, divide the annual amount by 12 months and add to the monthly expenses value. The future year expenses are calculated based on the assumptions you previously entered.

*Monthly Mortgage Payment: *enter the monthly mortgage payment (or the monthly equivalent if your payment frequency is other than monthly). This scenario assumes your mortgage payment amounts are kept constant over the 10 years.

*Mortgage rate: *enter your mortgage rate for the first five years. The remaining mortgage rates are based of your assumptions previously entered. In my case, we are on a five year fixed rate of 2.79% (our primary residence is on a variable rate).

*Special levy: *if you have a special levy that has already been confirmed, enter the monthly portion of it in this cell (e.g. total amount divided by 12 months). If you don’t have a confirmed special levy, leave this cell blank. A future, as yet unknown special levy, will be factored into the results based on the assumptions you entered above and probabilities we will get to further on this exercise.

## Probabilities on the decision tree

Finally! We are now ready to start the fun part and enter the expected probabilities for each section of the decision tree. We first start with the scenario of listing the rental property for sale.

In this table, enter a range of potential sale prices that you think are possible and including the minimum price you would accept. The final option is the case in which you list the property but it does not sell in a reasonable time.

In my case, I’m assuming there is a 20% chance that if we listed it now we would not sell it for our minimum price of $231k in a reasonable period of time (say six weeks). Keeping in mind that we wouldn’t want to leave it sitting on the market too long as our tenants would likely be looking for a new place and we’d be at risk of having it sit empty, increasing our losses.

I think there’s a strong chance (55%) we could sell it at the current market price of $231,000. I think there’s an outside chance (10%) that we could sell it for as much as $270,000.

The *Expected Future Value* column is calculated based on taking the proceeds from the sale and investing them elsewhere for 10 years.

Proceeds from sale = sale price–sales transaction costs – capital gains tax – mortgage balance repayment

In the case where no sale is made, the *Expected Future Value* comes from the next branch in the tree. At this point, we assign probabilities to whether or not the current tenants will decide to stay or will decide to leave. Since we listed the condo for sale in this scenario, I think it’s likely that our stellar tenants of five years would be looking for a new place and would leave even if we didn’t end up selling.

I’m assuming there’s only a 25% chance that our tenants stay in this scenario and a 75% chance that they leave.

Further on the tree, enter the probability that you think mortgage rates will be high or remain low in the future. I expect long rates are here for the long term, so I assigned a 75% probability to low rates.

In the case where the current tenants do leave, enter the probability by which you expect to find new tenants immediately and have virtually no interruption or other costs versus the probability of having a length vacancy and/or expense of updates (painting, cleaning, etc.). In my case, even though there is a low vacancy rate, I’m assuming there is a 75% chance we’d have an expensive tenant turnover. Part of this is due to the fact I know there will be some work required on the unit since flooring and walls haven’t been touched for close to 10 years (actually quite a bit longer than 10 years for the carpets).

The last step is to enter the expected probability of future, as yet unknown, special levies.

In my case, I think there’s a pretty high chance of a special levy (or more than one) during the next 10 years. Substantial concrete repairs are repaired and it’s not yet known if the increased monthly maintenance fees will be enough to cover them. The buildings are now 40 years old too.

## The result

Based on all of the values you have entered in the highlighted cells, you will now see the expected future value you would receive for both options. This is based on all of the values and assumptions you have entered and the balance of probabilities of each scenario playing out.

In my case, taking the decision to list the property for sale would result in an expected future value of $174k versus $184k for sticking with the status quo and continuing to rent it. These numbers of course or based on the balance of probabilities entered. If I were able to sell it for $270k now, the expected future value would be $215k. I have entered only a 10% probability of that scenario playing out though and hence the combined expected value is quite a bit lower than that.

Based on these results I took the decision to not sell the rental property at this time. As you can see though, the results are not that dramatically different. I am also calculating the standard deviation for both options. As you can see from the results above, the difference between the expected future values ($174k versus $184k) is within one standard deviation of each other ($18k or $8k). With these results, there is certainly no clear cut winner between the two decisions. I’ll hold on for now and revisit the decision tree if there are any material changes to the assumptions such as a dramatic increase in prices or higher special levies.