1. Solution of XNPV-XIRR
So, let's start here on the YEARFRAC tab, in fact, the name of
the tab YEARFRAC was a little hint. We actually want you to use
the YEARFRAC function in here, 'cause it's designed to figure out the distance
between two dates. Let's type in equals YEARFRAC. Now we can use the
up and down arrows. You wanna go down to YEARFRAC and hit tab
and it completes the function name. It's looking for the start date and
then the end date. So the start date will be up here,
the valuation date, we're gonna tap F4 to lock that cell,
put in a comma and the end date is gonna be right here,
let's hit Enter. So now we can discount that cash flow,
so put in an equal sign, grab the cash flow divided by,
open bracket, one plus the discount rate here, and we're gonna tap F4 to
lock that discount rate down, close the bracket and put this to the
power of a quarter of a year, like that, and we can hit
Enter. So all the way to do now is grab these two figures,
highlight across with the Shift key, hit Ctrl+R for fill right,
we get the check boxes for both of them. Now that these cash
flows are already discounted, getting the MPV is easy, we just use a
SUM function here, and we add up these discounted cash flows and hit Enter,
and we're all set. So let's pop over to the next tab now
with Ctrl+PageDown, for the XNPV, XIRR tab down here, we're gonna use that
function equals XNPV, we hit tab, it's looking for the rate,
which is right there, put in a comma, you can see now it's
looking for the values, okay, well, let's grab all of the cash flows
here, put in a comma, and now you can see it's asking us
about the dates, and this is where we select across here,
we start with a valuation date and go all the way to the
end and hit Enter. So putting the XIRR in here should also be
relatively simple, equals XIRR, as soon as we see the name there,
we can hit tab, it's looking for the values first stop,
so there's the values there, put in a comma and now it wants
the dates, so we select from here all the way across,
hit Enter, and we've got all the check boxes. Now let's look at
project B down below right here. So let's go down here and use
the XNPV function again, equals XN, hit the tab key, we're gonna select
the rate of 15%, comma, let's grab the value, starting with this empty
gray cell here all the way across to the end,
put in a comma and now grab the dates from the valuation date
all the way to the end here and hit Enter.
Now, if you have a value error in here that's normal,
in the sense that we did this on purpose just to highlight that
you absolutely need... You can't leave this empty, you need to put a
zero in here if there's no cash flow, so let's enter zero in
there, hit Enter and this corrects itself. So, essentially, if this is the
valuation date here, then Excel needs you to at least put a zero
in for the cash flow on that valuation date. Let's try the XIRR now.
Equals XIRR, hit the tab key, it's asking for the values here,
and then a comma, and then the dates across like this,
and let's hit Enter. Now, instinctively, we should know that that is incorrect
because if we're generating a positive NPV, that would mean that the IRR
should be higher than the discount rate of 15,
so this is definitely incorrect. Let's see what's going wrong in here,
if we tap the F2 key, we can see that actually what's going
wrong here is that we need a negative cash flow as the first
cash flow in the profile. So if we move this blue box over
like that, move the red box over like that, it should work,
'cause now it's starting with a negative cash flow, which is one of
the limitations of the IRR and the XIRR function. If we hit Enter
now, we get the correct IRR of 19%.
So, you definitely need to know how to use XNPV and XIRR because
you're gonna run into situations where you have uneven spacing between your
cash flows. Definitely watch out for some of the limitations of these functions,
namely, your valuation date needs to at least have a zero input there
for the XNPV function. And for the XIRR and the IRR function,
you need to start with a negative cash flow as your first cashflow.
2. Let's practice!