1. DCF Using Excel Functions
So this exercise is really about using the various NPV and IRR functions.
However, we will start out by discounting cash flows using the manual method.
Notice that we use periods for discounting in this method. Of course,
we could make these periods more dynamic, but you get the picture.
Also, notice that these cash flows occur at the end of the year.
To make sure that we don't discount the first year,
we set the exponent to zero. Since we have all of the cash
flows and the timing, let's go ahead and discount those cash flows.
Again, this is the manual method. So you've seen this several times at
this point. Again, the first year won't be discounted because the exponent
is zero. So we have a present value of negative $20,000,
which is proper because we do not want to discount that in this
case. And we'll select our range, and then we'll just do Control+R to
fill right, and then we can sum up all of those
discounted cash flows, and we arrive at effectively $100,000.
Now we will apply the NPV, XNPV, IRR and XIRR functions.
Notice that the cash flows are still at the end of the year.
We'll start with NPV. Since NPV discounts all cash flows,
we must exclude that first cash flow from the actual NPV function.
Again, so basically, we are assuming the $20,000 cash outflow happens today
at time zero. We'll do equals NPV, first argument is your discount rate,
then we will select the range of cash flows,
but we need to account for that initial cash outflow,
again, since NPV discounts all cash flows, and we specifically do not want
that first cash flow discounted because it happens today.
We'll just add it outside of our NPV calculation.
And you can see our numbers match the
DCF that we calculated previously. So we had to exclude that first cash
flow when using the NPV function. We do not have to do that
with XNPV, since XNPV does not discount that first cash flow.
So let's enter our XNPV function. Again, it starts with the rate,
then we select our cash flows. Again, XNPV does not discount that first
cash flow, but there is a third argument in XNPV that makes the
calculation a little bit more accurate, and that is, we select a range
of dates, close bracket, and we get $99,968. So it's pretty close to
NPV, but it's a little bit more accurate.
The reason being is because we're using dates and it's taking into account
leap years, but they are fairly close because all of the cash flows
occur at the end of the year and are evenly spaced.
Now, let's go ahead and calculate the IRR and the XIRR. So IRR,
again, in order to use the IRR function, we need a cash outflow
and a cash inflow. So we have our initial cash outflow,
which we select, and we'll select all of the cash flows,
and we derive an IRR of almost 79%. XIRR is similar to XNPV in
that it takes into account the days. So we still select our values
and then we will reference the actual dates, just like XNPV. And again,
you can see the IRR and the XIRR are really, really pretty close,
the XIRR being slightly more accurate, again, because of leap years.
And we can check these IRRs to make sure they are correct.
Remember, IRR is the discount rate that sets NPV to zero.
So if I copy the XIRR and just paste as a value in
our discount rate cell, you can see XNPV zero. So 78.8% IRR does
set XNPV to zero. Do Control+Z to undo that,
and we'll test IRR as well. So copy IRR, paste special values,
Alt+E+S+V, and you can see now NPV is set to zero.
So the IRR calculations work perfectly. So in this last example,
we changed the dates to make sure they were not at the end
of the period and relatively sporadic. So in this case, once we fill
in the functions, you can see how XNPV and XIRR diverge from NPV
and IRR. Do NPV first, again, reference the
rate, select the cash flows, and then capture that initial cash outflow.
So there's our a $100,000 NPV, like in the other examples.
But if we do XNPV, we still have to reference the rate,
but in this case, we can capture all of the cash flows,
then we select all of the dates, and now we have an XNPV of
$102,000. Okay? It's not that much different, but there is a difference.
You do the same thing with IRR. So we'll do IRR,
select our cash flows, and we get the 78.9%
IRR that we did above. Now, when we use XIRR, select our values
and select our dates, and you could see there's a pretty decent size difference
between XIRR and IRR. So bottom line, XNPV and XIRR are a little
bit better compared to NPV and IRR. Just make sure that if you
want to use XNPV and/or XIRR, you must have properly formatted dates.
In other words, you can't just use a year number like 2023 or
2024. You actually have to use the month, the day, and the year.
2. Let's practice!