# FIN500 Mod06 P2a NPV and IRR in Excel 2010

0 (0 Likes / 0 Dislikes)

♪ Music playing in background ♪ Text on screen: NPV and IRR in Excel 2010
Hello and welcome! In this screencast we'll take a look at net present value
and internal rate of return, both very important concepts
in introductory finance. So let's get started with
the simple spreadsheet here, that gives you
a bunch of cash flows that occur in
various years. Years 0, 1, 2, 3, and 4
the cash flows are $350 dollars going out
and so it's negative, it's a negative value,
and its in a parenthesis because that's how accountants like to show negative values,
So a $350 dollar cash flow is going out.
In the 0 year that is at the very start of
our time interval of interest.
At the end of that first year
you have a cash flow of a $100 dollars coming in.
At the end of the second year you have a cash flow of $200 dollars coming in.
And at the end of the third year, you have a cash flow of $150 dollars coming in.
And fourth year, $75 dollars.
So how do you calculate the net present value of all these cash flows
subject to an interest rate of 5%?
So, in other words, what we're asking is
if the cost of our money is 5% that is if we were to put this money in the bank
we'd get a 5% return interest rate on this.
Now given that opportunity cost that our money can earn,
what if we put this money of $350 dollars
inside this venture that it yielded $100 dollars, $200 dollars and so on,
over the four years?
Which is better? Is it better to put money in the bank—and at 5%
or is it better to money in this venture and earn this return?
We need a way to compare that. So that's what we're trying to find out
with the net present value. If the net present value
of these cash flows had this hurdle interest rate or
had this cost of capital.
If the net present value is positive, then it's better to invest in this.
If the net present value is negative it's better to put money in the bank
and earn 5% interest rate. So to calculate net present value
I'm first going to do it the longer way,
which is to say, that I will calculate the present value of each of these cash flows.
The present value is the cash flow divided by
the discount rate (the discount rate is the interest rate)
raised to the power of how many, how much time
periods away I will be getting the money from.
So, in this case, it is zero because we're immediately paying off
the money. So I'm just going to press enter, and I also want to enter F4
so the F1 cell here becomes $F$1
so that as I copy this formula down, the interest rate cell remains F1.
And I copy this down to all the other cells.
So now you have the present value of $100 dollars
one year from now, is $95.24.
The present value of $200 dollars that you will get
in 2 years from now is worth, today, $181.41.
And so on for these two. And the net present value is nothing but
the sum of all these cash flows, the negative one in the beginning
and the positive ones later on. And that is $117.92.
Excel provides an NPV function, and we ill now use that function
to calculate the net present value. To use NPV function
this is how we will do it. I would first add up the initial negative cash flow
plus NPV with a parenthesis
the interest rate and all the values
(not the first one, the negative one, but all the other values)
and that's exactly $117.92.
So if we were to put $350 dollars in this venture
which will yield $100 dollars in the first year,
$200 dollars in the second year, and $150 dollars in the third year,
and $75 in the fourth year, then we will be better off
by $117.92.
So that means that is our gain
out of the venture, and we should probably put money in this venture
as opposed to putting the money in the bank.
That brings us to the concept of the internal rate of return.
As you can increase this interest rate,
let me just show you this, supposing instead of having 5%
if this was 7%. The net present value has just decreased.
If the interest rate was instead 10% the net present value has decreased even further.
So if you find, if you can find an alternative investment that pays higher interest rates
than this particular investment appears less, and less attractive
because its net present value is lesser and lesser.
So what is that value of this interest rate?
Right? Is there as sufficiently high value of this interest rate
at which the net present value becomes zero?
That interest rate is the internal rate of return.
So let me calculate that using the excel formula.
equals IRR with a parenthesis, here are all the values
(now I'm selecting the first one as as all the others)
and I have a guess interest rate
(this can be any number it doesn't have to anything at all)
I could just put say 3% here or something, some reasonable interest number, interest rate number.
Or I can just choose this number here as well, let me just say 6%
so totally random number, I could choose any number here within range.
(it should be a reasonable interest rate) and just press enter.
And the internal rate of return is 19%.
Let me actually make this show the decimals.
So this is the internal rate of return, so at this interest rate, if this interest rate
was 19.25614% then the net present value would be zero.
Let me see if there is a case,
I'm just going to say 19.25614
and you can see that the net present value has become 0.
So if your alternative investment
can yield something greater than 19.25614%, so if it can yield 20%,
then you're actually losing money
by putting it in this venture because the alternative investment can yield more.
And now your net present value is actually negative,
so that is the significance of the internal rate of return.
How do company's use internal rate of return? They evaluate their projects based on
what is the internal rate of return that this project has.
So if the internal rate of return is sufficiently high,
say 20%, 25%, then you know that the project is probably worth
doing because it's going to result in a net, in a positive
net present value, or NPV, and you proceed with the project.
If the internal rate of return is low, with 3 or 4%, then it's more risky
to do that project because it could be higher, projects that yield higher
returns. So that was a brief discussion of net present value
and internal rate of return and how the two are related.
I just want to leave you with one part that is the NPV function in Excel
does not calculate net present value as understood by the finance community.
In the finance committee, in finance textbooks, net present value
includes the initial amount that you put in
and the present value is not in that, whereas in fiance
you do not include the initial value. We have to add the initial value
explicitly in the beginning.
So I hope you found it useful and thanks for watching and check out more
videos on codible.com. Have a great day.
♪ Music playing in background ♪