1. Applying Excel Functions in our Model Part 2
Now let's go ahead and use XNPV. Remember, XNPV is maybe the best
way of discounting cash flows. The only trick is you have to have
actual dates. Of course, in this section, we do have actual dates.
First use of XNPV, we're still going to assume the end of period
discounting. So do XNPV. And again, once you have your functions selected,
you can hit tab and it fills everything in. So we first need
to reference our rate. Then we are going to capture the undiscounted cash
flows. Again, the trick with XNPV is that since it doesn't discount the
first cash flow, that's the reason why we hard coded zero in cell
H252. So we select those cash flows, and then the third argument XNPV, again,
in this case, we're assuming the end of the year, we need to
select the fiscal year end dates. Press enter.
And again, it's basically identical to the manual end of period discounting
convention. And on an equity value per share,
also $6.19 as well. Now let's use the XNPV for mid period discounting.
So again, XNPV first argument is our WACC, and we'll take our cash
flows. Again, we need to make sure we
take into account that zero cash flow. And then since we're doing mid
period discounting, we want to use the dates in row 246.
Press enter. Again, the number matches our manual mid period discounting
convention and calculating equity value. Again, we can copy and paste 658,
which also matches the manual mid period discounting convention.
The reason why we do all of these is to show you the
strengths and weaknesses. So you can obviously do manual
discounting, you can use NPV. You just have to be aware that the first
cash flow in an NPV is discounted. You can use XNPV, but the
first cash flow in XNPV is not discounted.
And you also have to make sure that you have actual dates
when you use XNPV. So they all have their
strengths and weaknesses and we wanna show you all of them
at the same time. And finally, let's talk about
calculating the internal rate of return. So let's assume we are investing
$20 million to purchase this company. Since we have a negative cash outflow,
we can calculate the internal rate of return using the IRR function as
well as the XIRR function. So go ahead and calculate the IRR
using, again, the regular IRR function. Use the XIRR function
with end of period discounting, and then use the XIRR function for mid
period discounting, and then we'll walk through this together.
Okay, so we'll start off by using just the regular IRR function,
it equals IRR. And again, the IRR function requires
one negative cash flow and at least one positive cash flow.
So we'll select this entire range. We have a negative cash outflow to
begin with, and then we have the cash inflows, press enter. And we
have a 78.9% IRR, which is an extremely healthy IRR. Now let's use
the XIRR function and we're going to assume the end of period discounting.
So we still capture our cash flows first,
and then we will grab our dates. And again, since this is the
end of period discounting, we use the fiscal year end. So we select
again, our cash flows and then our dates
and we get 78.8. These numbers might look a little familiar based on
the exercise we did earlier. Now let's try XIRR using mid period discounting.
So we'll do XIRR, again, select our values.
And then in this case, we select these midyear dates just like we
did with the XNPV mid period discounting. Press enter and whoa,
all of a sudden we have 101% internal rate of return.
Whether you're doing mid period discounting or end of period discounting
or just using the regular IRR function, these are all extremely healthy
returns, so you would definitely want to make this investment.
And finally, at the bottom, we've already constructed some data tables.
We cover the construction of these in quite a few of our core
courses. As you can see, we are sensitizing the equity value per share
by WACC, by the perpetuity growth rate and by the terminal multiple.
These numbers will feed into our football field chart
when it comes time to build them. But at this point,
you are done with the DCF model. Congratulations.
Now let's talk about relative valuation.
2. Let's practice!