Discounted Cash Flow: An Example

I started a three part series to help people understand discounted cash flows, what it is, and how to think about it. In this post, I’ll try to walk you through how to do it. I hope it helps someone make smarter decisions.

The two main drivers of a discounted cash flow calculation is the cash flow itself, and the discount rate. The cash flow is what it is, the mullah, the cheddar itself and how much of it you expect to be rolling in.

The discount rate is the required interest or return the investor demands on their investment after accounting for risk, inflation, opportunity cost and so on. If you read the prior two post, you’ll get some idea.

Now, we’re going to walk through the steps of discounting cash flow in Excel. It’s relatively simple, but it’s usual to do it a few times before it finally clicks,

Using a quick method, we’ll open excel and enter numbers 1-5 representing the number of years worth of earnings we’re going to discount:

Excel1

Next, we’ll plug in the numbers that represent our friend’s earnings for each of those years. In real life, you’ll probably have the actual numbers for only two out of the five years, and based on the growth rate, earnings power and other qualitative issues, you’ll estimate the rest. But to keep it simple, let’s pretend our friend knows exactly what his numbers are and gave them to us as follows:

Excel2

Okay, since he’s given us the earnings we want to discount to their present value, we will enter our own discount rate under them. People’s discount rate differs but again, if we assume that inflation is 3%, risk free assets have a return of 2% and we want an extra 5% for opportunity cost and so on, we can decide that our discount rate is 10%. It’s somewhat in line with what average investor expects from an equity investment.

So we’ll just enter 10% as our discount rate.

Excel3

Finally, to account for the passage of time, we will have to calculate a discount factor for each year’s cashflow. The formula for that is simply “(1 + the discount rate) raised to the power of the Period number. Or in our case here (1+.10)^1 (or 2, 3, 4, 5 as needed). In Excel, the simply formula would be  = (1+$B$4)^B2

(The dollar sign makes sure when you drag the formula across to the other cells, the discount rate part references the same cell where we entered it. Most excel users know that but I wanted to point it out in case)

So this is what it looks like:

Excel4

Now once we’ve done that, all we have to do is divide our cashflow for the year in line 3, by that year’s discount factor in line 5 to give you the discounted value of that cash flow. So in Excel we’ll do 389.6/1.1, 436.3/1/21 just like that. And here’s what we get in Excel doing that:

Excel5

So now we’ve gotten the present value of all those earnings. If you notice, the cashflow for year 4 and 5 were each larger than the previous year, but when you discount them to the present, they have less value. That’s because they grew at a lower percentage rate than the previous years. This is something you notice in real life, when a business is new, it grows cashflow at a faster rate but as it starts to get bigger, competitors enter and all that, you find out that the growth rate slows.

But anyway, now we simply have to add up all the cash flows.

When you total the cashflows, from line 3, they add up to 2,396.6 or roughly $2.4million.

When we total the present value or the discounted rate in line 6, we get the sum of 1,786.6 or roughly $1.8 million. This is what your friend’s business, based on it’s 5 year earnings, is worth today. 50% of that is $900,000 so if my friend was offering to give me half the company at $500,000 I’d be in there like swimwear.

IF of course I had $500k lying around. Which right now? I don’t. Please epp*

Anyway, I hope this clarifies DCF for someone, and hopefully you go out there and discount everything. It works the same whether we’re talking about ten bucks or ten thousand bucks. Only the assumptions change.

Adios!

*- help, for those of you who don’t know. 🙂

AK.

Advertisements

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s