1. Solution of NPV & IRR
So let's have a go at filling in the template here,
starting with the IRR function. Equals IRR, and open the bracket and we're
gonna select the value. So from this value all the way across to
the end, hit Enter. We've already got a check box over on the
right hand side. Perfect. Now, let's try the NPV here. Equals NPV,
open the bracket. First thing it's looking for down here is the rate,
so the rate is gonna be the discount rate, put in a comma, now
it's looking for essentially the values. So we're gonna select all of these
values like this, close the bracket, and then hit Enter. You may have
tried this and in fact, you can see over here, we don't have
a check box, so we've done something wrong.
So here's the issue with the NPV function, if we tap F2, notice
that it asked us here about the discount rate, and then it asked
us about the values, but it asked us nothing about the timing,
which means it's making an assumption about the timing, and the assumption
that it's making is it's taking the first cash flow that it encounters,
it's discounting it back one year, but actually we didn't want that one
discounted back one year. We didn't want that one discounted at all because
that was year zero, so we're gonna have to adjust the formula.
What we want is we want this cash flow discounted back one year.
So we're gonna adjust the formula here, tap F2, and essentially instead
of it starting in column D, we wanna change that to an E,
like this, now it's discounting 90 million back one year,
20 million is getting discounted back two years, et cetera, all the way
to the end. Well, what do we do now about that first cash
flow? Well, we go to the beginning over here and we tap F2, and
we just add it in like this and put a plus sign.
So we're grabbing the first cash flow in D7, we're leaving it undiscounted,
and then we're using the NPV function to discount the remaining cash flows
appropriately, let's hit Enter and you can see we've got a check box
over on the right hand side. Nice work.
So we have matching now. So the thing to remember about the NPV
function is the first cash flow that it sees, it always discounts it
back one year, second cash flow discounted two years, et cetera, all the
way through. Now, one thing to note about the IRR function as it
can result in multiple solutions sometimes. So let's look at the cash flow
profile. This is what we would call a normal cash flow profile,
where you have negative cash outflows at the beginning, followed by positive
cash inflows afterwards. So, if we were to graph these cash flows,
they would effectively cross the X axis only once, however, if they were
to cross the x axis again, for instance, if we had a negative
cash flow out here, then we could be into a situation where the
IRR function would generate more than one result. If you ever have a
cash flow profile that crosses the X axis more than once,
then what you wanna do is rely on your NPV function to make
decisions on whether or not you're gonna invest in the project.
2. Let's practice!