I hope you all enjoyed last month’s article on Capitalisation. If you haven’t had a chance to read it, I suggest you start with Part 1 before continuing below. This month, I thought we’d look at Discounted Cash Flow. It’s an area of valuation that most people are terrified of because it seems too mathematical, but I’m going to show you how simple and powerful it can be. I’ll even help you build a basic Microsoft Excel spreadsheet that you can adapt for any property you’re looking at, so let’s begin…
Discounted Cash Flow (DCF)
The Discounted Cash Flow method of valuing is based on the following understanding:
The market value of any investment property is simply the present value of all future income that is expected to be realised during the anticipated term of ownership, or investment horizon.
The same can be said about equities or companies. You might be familiar with Warren Buffett’s comment, “Intrinsic value is an all-important concept that offers the only logical approach to evaluating the relative attractiveness of investments and businesses. Intrinsic value can be defined simply: It is the discounted value of the cash that can be taken out of a business during its remaining life.”
So, with this in mind, we need to consider the future expected cashflow of a property. I’ve underlined ‘expected’, because this is one of the biggest criticisms of DCF: you are required to make assumptions, whereas with the Capitalisation method the ‘numbers’ are largely known. Where DCF becomes valuable, is that we can now:
- Price in and compare properties with different tenant covenants.
- Add assumptions about growth, market reviews, and terminal values.
- Look at projects that don’t have current data to capitalise, such as developments, refurbishments, or other value add opportunities.
- Evaluate properties with minimal comparable evidence.
- Compare commercial property investments against other asset classes.
When we looked at the Capitalisation method, we really just considered a snap-shot in time. i.e. we considered the current or passing net income and the current yield in the market and then calculated the market value. To recap, net income divided by Cap.Rate (yield) equals value (Net Income / Cap.Rate = Value). For example, a property currently renting at $1,000,000 per annum net, with the market yield running at 7% is worth: $14,285,714. (1,000,000 / 0.07 = 14,285,714).
If the Capitalisation method is a snap-shot or photograph, then the DCF approach is a movie. It has a start, story and an end. i.e. it considers the investment over a much longer time frame, which is very important, because you’ll no-doubt own the property for a long time.
Timeframe: We would normally consider the DCF timeframe to match our projected ownership, which is normally 10 years, although some owners only consider a 5-year horizon, because forecasts further out than this become less reliable, and cashflow beyond this is less valuable.
Discount Rate: You’ll often hear this referred to as the Hurdle Rate, but all we’re really defining is how much we need to earn from this investment to make it worthwhile. Remember, last month when we discussed the Risk-Free Rate of Return and the Risk Premium; the same concept applies here: We said that a proxy for the Risk-Free Rate of Return was the RBA Cash Rate (currently 1.5%). We then add the Risk Premium on of say 5-8% and we get our hurdle rate of 6.5 – 9.5% in this example.
Another, or perhaps more suitable method for DCF, would be to use the 10-year government rate as the Risk-Free Rate of Return, because this matches our investment horizon and DCF model. The current 10-year bond rate in Australia is around 2.5%. We then add our 5-8% risk premium on and the hurdle rate we need to take the risk of investing is 7.5-10.5%.
In reality, you can adopt any hurdle rate you like. It’s up to you to decide how much return you really need to achieve to take on the risk. You might also apply different Discount Rates to different investments. i.e. A higher rate for what you perceive as more risky investments.
The other variables you‘ll need to construct a DCF model are:
- The purchase price (or market value). You could use your capitalisation method for this.
- Purchase and Selling costs.
- The starting net income (actual or forecast).
- The annual increases in rent (CPI or fixed usually) and outgoings, etc.
- Any factored contingencies, such as vacancy, capital expenditure, incentives, refurbishment etc.
- You’ll also need to factor in the Terminal Value of the building (or market value) at the end of the DCF timeframe. To do this we’ll need to adopt a Terminal Yield. This is very simply the cap.rate that you expect at the end of the DCF model. It’s a guess, but all I’ll say is you should be conservative, rather than overly optimistic here. This may be different to the purchase yield if the current market is different to the market you expect in 5 or 10 year’s time. Maybe it’s too hard to estimate so you apply the purchase yield to keep it consistent, or maybe you’ll apply the long-term average yield of commercial property (which may be above or below the purchase yield).
Now, you may not need all of these items or factors and it can be as simple as you like or as complex as you need. That’s the beauty of DCF: you can factor in a lot more than a simple Capitalisation approach. These factors can then be priced into what you’re willing to pay for the asset. Investors often get caught out when they make an offer using a simple capitalisation rate only to find that the valuer applies some pretty significant impacts to your DCF cashflow along the way, which affect your overall valuation.
Let’s look at a simple example. Here are the details:
| Term of DCF Cashflow | 5 years |
| Rent | Assume annually in arrears |
| Purchase Yield | 7.0% |
| Purchase price | $14,285,000 |
| Purchase costs
(assume) |
6% (Stamp Duty, legals, consultants etc)
$857,100 |
| Initial Net Rent (Year 1) | $1,000,000 |
| Annual Increases | 3% |
| Terminal Yield | 8% (assuming the long-term yield is above the current market) |
| Terminal Value | TBD based on expected rent at end of DCF |
| Selling Costs | 2.75% |
| Discount Rate | 10% |
From our Capitalisation Rate, we determined the building was worth around $14,285,000. Let’s see if our DCF model confirms that.
Our simple DCF spreadsheet looks like this:
A couple of points to note:
- The purchase price and costs are shown as a negative in year 0. i.e. we have to pay money out to purchase the property.
- Income over the 5 years is increased 3% each year.
- The terminal value of the building is based on the commencing year-6 rent (increased 3%), rather than the end of year 5 rent. This is because we’re selling after the end of year 5.
- Year 5 includes the rent received that year, plus the net proceeds from the sale of the asset (terminal value less selling costs).
Net Present Value:
“A bird in the hand is worth two in the bush.”
The basic premise here is that the value of receiving a sum of money in the future is worth less than that same amount of money in your hand right now. This is where we “discount” the future cashflow to work out how much it is worth right now to us. In the table below, I have shown the value of each year’s cashflow discounted back to present value at the Hurdle Rate of 10% per annum. For example, $1,060,900 to be received in year 3 is only worth $797,070 to us right now.
A quick side note for maths geeks. This is essentially using a derivative of the compound interest formula (in reverse). The normal compound interest formula is:
Amount = Principal x (1 + Interest Rate) Time Period
For example, if my principle was $2,000,000 and I invested it for 3 years at 10% compounded the formula would be:
Amount = $2,000,000 x (1 + 0.10) 3
The amount is worth $2,662,000.
The discount factor we’re after here is the part in brackets: (1 + Hurdle Rate) Time Period
In our example above: (1.1)3 = 1.331.
Therefore, if we wanted to discount the net income above in year three ($1,060,900) back three years at a Hurdle Rate of 10% per annum, we could just divide the net income by the discount factor: $1,060,900 / 1.331 = $797,070.
As a different example, a five-year factor at 15% would be: (1.15)5 = 2.011. You can see why NPVs of cashflow greater than 5 years becomes less important, particularly if the Discount Rate is high. A 15% Hurdle Rate over a 10-year period would be a factor of = 4.04. i.e. $2,000,000 in 10-year’s time would only be worth $495,050 today.
Ok, say what’s the current valuation? As I’ve broken it down for each year above, the Current NPV is simply the sum of each year’s NPV. NB: we don’t include the purchase price expense in this NPV calculation, but the sale price is included. The valuation above is: $12,752,888.
Let a Spreadsheet do the work
Now that you know how it works, let’s jump to the simple Excel tool. Although the NPV amount above of $12,752,888 is the sum of the NPV from each year, there is a much simpler way to arrive at the exact same number. In Excel the formula is:
= NPV ([Hurdle Rate], [Number 1], [Number 2], [Number 3], [Number 4], [Number 5])
Please see the screen shot below and note that I am using the actual net income for each year, not the discounted amount (excel works that out for you). It’s a very simple formula.
This can actually be simplified even more in Excel, with the range of numbers being selected, instead of each number individually in sequence. This is written with a colon between the first and last number, meaning each number in the sequence:
= NPV ([Hurdle Rate], [Start_Number]:[End_Number])
See below:
Monthly DCF
If you wanted to be more precise and do the DCF monthly, please remember that the time period and the hurdle rate have to be consistent. i.e. if the time period is monthly instead of annually, then the hurdle rate needs to be monthly too. For example, if you wanted to calculate the NPV of a monthly cashflow for three years at 10%, then the Time Period would be 36 months, and the monthly Hurdle Rate would be 0.8333% (0.10 / 12 = 0.008333). In the manual calculation the discount factor would be: (1+0.008333)36 = 1.348. Remember, on an annual basis we came up with 1.331 as this factor. The different factor shows the impact of compounding monthly versus annually. NB; a monthly DCF would reflect a lower NPV right now (due to a higher factor).
Why would we bother doing it monthly? Well, we might construct a DCF model on a monthly basis because there are numerous tenants on different lease profiles, expiry, vacancy periods, incentives, and different annual increases, so we need to break it all down to get an accurate picture. I’ll come back to this later, but let’s examine the DCF NPV a little more.
You may note above that the NPV only came to $12,752,888, which is quite a bit less than our Capitalisation Rate value of $14,285,000. There are a couple of considerations to be aware of. Firstly, we’ve included purchase and selling costs, which we didn’t factor in with the capitalisation valuation. Secondly, we assumed that the Terminal Yield was 8%, not our purchase cap.rate of 7% (i.e. we’ve factored in that the market yields have gone up and therefore our value would be less than at purchase if the rent remained constant). This reduces the overall return and therefore the calculated NPV.
Sensitivity Matrix
The other thing to remember is that the Hurdle Rate (Discount Rate) and the Terminal Yield have a huge impact on what the NPV will show. It’s normally worth completing a basic NPV Sensitivity Matrix to see how variable the outcome can be.
We normally start with our assumed DCF variables in the centre and then go above and below in Discount Rate and Terminal Yield. Remember, Terminal Yield is a guess that is five or ten years into the future. We may be completely wrong, so we need to see how much we’ll be impacted by the yield being higher or lower. The Discount Rate is also subjective so we need to stress test a range of outcomes. Remember, if the Risk Free Rate of Return changes, our expected Hurdle rate would also change, even though our Risk Premium remains the same.
You can see above that our assumptions for Hurdle Rate at 10% Discount Rate range from a current value of $10.95m through to $15.75m. From bottom right to top left, the NPV ranges from $9.8m to $17.8m. You can also see in green that if our terminal yield remained the same as our purchase price cap.rate (7%) and our Discount or Hurdle rate was slightly lower at 9%, then we’d be happy with our purchase price of $14,285,000, because the NPV is showing $14,613,743. So, do we really require a 10% Discount Rate in the current market or is 9% acceptable? This Hurdle Rate would reflect a Risk Premium of around 6.5% above the 10-year government bond rate of 2.5%.
Alternatively in orange, we could determine that we need to reduce the purchase price to $14,038,000. We might think this is reasonable, because it’s going to be hard to argue with the vendor that the asset is worth less now (NPV) because of what we think the Terminal Yield will be in 5 years time. This way, we’ve used the same yield as purchase, and still have our 10% Hurdle Rate achieved.
You also might find that your valuer averages the two valuation methods to determine a mid-point valuation for his estimate. For example, the average between the Capitalised value of $14.285m and a DCF Value of $14.038 would be $14.16m.
Internal Rate of Return
The other number that’s easy to generate in Excel (which may be of interest), is your Internal Rate of Return (IRR). This is really an analytical process rather than a valuation tool. It’s a way of finding the return (yield) that is generated from the investment if all cash flows are equated back to the cost of the investment. i.e. NPV = 0. Note that the cashflow includes the purchase price and sale. This is a very simple process in Excel. The formula is:
= IRR ([Start_Number]:[End_Number])
This includes the purchase price, net rent series, and sale price. See below:
A complicated DCF Model
Let’s look at the same model but add in more detail to get a better idea of what this property is really worth. Here are some realistic assumptions for 5 tenants:
| Term of Cashflow | 5 years |
| Rent | Assume monthly in arrears |
| Purchase Yield | 7.0% |
| Purchase price | $14,285,000 |
| Purchase costs
(assume) |
6% (Stamp Duty, legals, consultants etc)
$840,000 |
| Terminal Yield | 8% (assuming the long term yield is above the current market) |
| Terminal Value | TBD |
| Selling Costs | 2.75% |
| Discount Rate | 10% |
| Tenant 1 | Annual Rent: $350,000
Remaining Term: 84 months Annual Increases: CPI (assume 2%) Market Review after month: 36 (start of month 37) Market Review Estimate: 10% increase |
| Tenant 2 | Annual Rent: $250,000
Remaining Term: 60 months Annual Increases: 3% Fixed Market Review: Nil |
| Tenant 3 | Annual Rent: $200,000
Remaining Term: 24 months Option: 3 years Annual Increases: 4% Fixed Market Review: No increase. Assume 3 month incentive |
| Tenant 4 | Annual Rent: $120,000
Remaining Term: 30 months Option: Nil Annual Increases: 3.5% Fixed Vacancy Period: 3 months Incentive: 6 months rent-free |
| Tenant 5 | Annual Rent: $80,000
Remaining Term: 48 months Option: Nil Annual Increases: 3.75% Fixed Vacancy Period: 2 months Incentive: 3 months rent-free |
The monthly DCF spreadsheet below includes the mix of annual increases, market reviews, vacancy and incentive periods. You’ll note that I have used a 9% Hurdle Rate and a Terminal Yield of 7% (which is the same as purchase).
You will also note below, that our model still works for a DCF Valuation NPV of around $14.1m.
I hope the above helps explain the DCF Model and also shows why it’s so important to gain a full understanding of an asset’s valuation. You will also have noted that the model can cope with vacancy, incentives, different tenant covenants, varying terminal valuations and hurdle rates to match our desired risk profile. Next month, we’ll look at Direct Comparison, which is much simpler. We’ll also look at tying it all together. Have a play with a simple DCF Spreadsheet. It’s really not as hard as it looks. Happy investing!
Pure Property – Commercial Management Re-Defined