Thanks to Part 3 and our crystal ball, we have determined how much FCF our fictional company, WITW, will generate in future years. The next step is to figure out how much that is worth in today's dollars. To do that we need to introduce the concept of the Discount Rate. There are any number of definitions and ways to calculate the discount rate. For a rather in-depth discussion, see this Terminal Value presentation.
For our purposes, let's keep it simple and define the discount rate as the minimum rate of return we hope to obtain. Since 15% is a good rate of return (it beats the historic return rate of the S&P 500, which is somewhere around 11%) we will use that. Time to enter this information in our spreadsheet.
Click on cell D5 and enter the following: Discount Rate:
You will probably need to resize column D to make it wider so that the whole discount rate label is visible.
Click on cell E5 and enter the following: 15%
Make sure the format of cell E5 is Percent, and change it if necessary. Your spreadsheet should now look like Figure 9.
Figure 9: Discount Rate (click for full image)
Now that we have a discount rate to determine the present value of future cash flows (sounds impressive), we can use a nice little formula to calculate that value. Here it is:
Present Value = Future Value / (1 + Discount Rate)^Year
Go ahead and add a header for the Present Value column, also known as PV:
Click on cell G8 and enter the following: PV
Enter the following formulas for each of the PV cells:
|
Cell |
Formula |
|---|---|
|
G9 |
=F9/(1+E5)^D9 |
|
G10 |
=F10/(1+E5)^D10 |
|
G11 |
=F11/(1+E5)^D11 |
|
G12 |
=F12/(1+E5)^D12 |
|
G13 |
=F13/(1+E5)^D13 |
You will probably have to resize the G column to fit the new values. Once you have done that, your spreadsheet should look like Figure 10.
Figure 10: Present Value by year (click for full image)
The formula for the present value of a cash flow at a terminal growth rate is a little bit different than what we have seen. We are actually attempting to figure out what a company is worth today if the cash flow grows at a set rate for the rest of eternity. Here is the formula we will use:
PV = FCF * (1 + Terminal Rate) / (Discount Rate – Terminal Rate)
If you are paying attention, you will note that if the Terminal Rate and Discount Rate are the same, bad things will happen. Just be aware of that, and also note that the most conservative assumption is that the terminal growth rate will be zero (or less). Ok, time to enter the formula in the spreadsheet:
Click on cell F14 and enter the following: F13*(1+E14)/(E5-E14)
Note that this is not really the Future Value in the traditional sense; after all, if the company continues to grow cash flow each year forever, adding up all of those cash flows would produce an infinite amount, worth even more than WalMart (or is it?? ...insert spooky music here...). That is why we used a little math wizardry to compute the present value of a company that grows at a set rate forever.
We now have the present value of the FCF based on the terminal rate, however we have this number not for today, but 5 years in the future since we based our calculation on the FV found in cell F13. Now we need to discount that value back to todays dollars. That means we use the previous formula:
PV = FV / (1 + Discount Rate)^Year
For this case we use the value in cell F14 as FV, and the value in cell D13 as the number of years. Enter the formula in the spreadsheet:
Click on cell G14 and enter the following: F14/(1+E5)^D13
If your spreadsheet looks like Figure 11, then excellent.
Figure 11: Terminal growth rate FV and PV (click for full image)
It is now time to total things up and raise the curtain on the final value.
Take Me To Part 3 | Take Me To Part 5