hey everyone Eric here today we're going to discuss how to calculate net present value andn
the internal rate of return and how to use these two metrics to analyze financial investments so
first let's talk about net present value or as we say NPV so would you lend me one thousand dollars
today if I promise to pay you back one thousand five hundred dollars next year well the answer
should depend on how likely you think it is that I pay you back next year this is exactly the type
of situation that NPV helps us analyze it is used to evaluate the risk return profile of investments
so let's look at a few examples in example one we are talking about investing in a private equity
style company these are companies that typically are profitable and are growing 10 20 30 % a year
not super risky companies and companies that also aren't growing at a tremendous growth rate so in
this situation we say that we are going to pay 3 million dollars and then we will get this stream
of dividends and then in year 7 we're going to sell the company for about 5 million dollars with
one last dividend so in order to calculate the net present value you need the cash flows so what
you pay for the investment and what the investment pays you back you also need a discount rate which
is called the working average cost of capital they are synonyms and with those two things you
can calculate the net present value so what is the net present value of these future cash flows
first let's talk about the discount rate so the discount rate is going to change based on every
potential investment the discount rate tells us how risky the investment is how likely we think
it is that we actually get paid back so this is obviously going to depend on some basic economic
things like inflation opportunity costs that could be you know other available investments and then
it's really going to depend on the company in the industry so the company depending on how
much debt or equity the company has that's a big one and then the biggest one is going to be the
overall sort of risk of the business like certain industries are very risky they're very likely to
get regulated or maybe the company has a unproven business model and is growing really fast but for
a company like this we would consider it pretty low risk and in this case we could probably give
it a discount rate of around 10% again there's a formula to do this but for this video this is
going to give us a reasonable result and this is the type of discount rate they would use for
a business like this so let's talk about the NPV formula first so year 2023 we're saying this is
historical numbers so this already happened and 2024 to 2030 this is our forecast these are the
cash flows that we hope to get in the future so the NPV formula is equals you take the future
cash flow in year one then you divide it by one plus the discount rate so that will reduce the
number let's hit F4 and you'll see that we'll anchor the cell that will lock the cell so we
can copy the formula forward and then we're going to raise the denominator to the number of years in
the future we are so in this case we're one year in the future so we could do one but to make it
more automated I'm going to do 2024 minus 2023 and then lock 2023 this is so that we can just
copy this whole formula forward and we don't have to rewrite it so now let's hit enter and you'll
see that in year one we've estimated this future cash flow in today's dollars for the net present
value to be a hundred and thirty six thousand dollars so if we copy this forward you'll see
that seven years out you have five million three hundred and fifty four thousand and this year
we're planning to sell the company so we're saying that the company is going to be worth about five
million dollars and we're going to also receive one final dividend but in today's dollars in the
value of today's dollars it's only worth about 2.7 million because there's so much risk that
far out so the net present value of all of these future cash flows we had we do equals some and we
just add these up we're saying is 3.7 million dollars that's what this investment is worth
today for us there's also an automated Excel formula which calculates the exact same thing
and can make your life a lot easier so this is NPV open parenthesis and all we have to do is
click our discount rate comma and then grab the future cash flows this whole did the exact same
math check this out close parentheses hit enter and you'll see that you will get the exact same
number so if you want to do it manually or you want to use the formula the choice is yours so
this whole thing works without a purchase price so when we're looking at purchase prices we obviously
want the purchase price to be this number or lower so let's say that there is an offer we
have an offer to basically buy this company for three million dollars what is the return of that
what exactly is that so this is where IRR comes in so the formula is IRR internal rate of return
and all we need to do is just grab including the purchase price which needs to be a negative number
obviously because it's cash out the purchase price and then the series of future cash flows comma and
you need to put a guess in this can be really any number it just needs to be some sort of positive
number if you think it's going to be positive so let's say 10% and then Excel will basically figure
it out so equals so in this case we're seeing that the internal rate of return for this investment is
actually thirteen point nine percent so this is the return and we've estimated the risk to be ten
percent so in this case we can see that the return is in excess of the risk and this is proven by the
fact that the current the net present value of future cash flows is higher than the actual price
so this looks like a good investment one other note the internal rate returned this is actually
the discount rate at which our net present value becomes exactly equal to the purchase price check
this out so if I just copy in values you'll see our NPV becomes identical to the purchase price
why because this is the rate at which the return and the risk are identical so if I just go back we
like the return profile to be higher than the risk profile and in this case I would say you
know this is an investment that we should pursue let's look at the second example this is a public
tech company let's say it's a company growing really fast maybe 50 percent a year and a lot
of revenue growth but the industry itself has a lot of competition and is very very likely to get
regulated so there's actually a lot more risks whereas this company is in some sort of a safe
industry this industry is changing extremely fast it's being regulated quickly so the risk
of this company is higher but it's growing 50 percent a year so the returns are way higher so
in this case we're saying okay today one share of this company in the open markets let's say it's a
public stock sells for three hundred dollars we've done the projections and we think that
the price is going to be $1,000 seven years from now should we invest in this well you're probably
thinking yeah of course you know more than three times your money well not so easy first we need to
look at the discount rate so in this case let's say that we've effectively done the analysis on
the whack and it's much higher it's about 17.5 percent because this company has a lot more risk
so in this scenario let's use our automated NPV formula get 17.5% and grab future cash flows of
this company okay what does it look like we're saying the net present value of those cash flows
is 323 so while you know $1000 might sound pretty good seven years from now in today's dollars it's
worth just a little more than the actual price and so that's probably what the market is saying
as well you know it's discounting the risk of the company and saying okay this is only worth $300
because there's a lot of risk what about the internal rate of return IRR look at the values
grab the whole thing let's say we think it's you know 20 percent or something Excel will tell us
okay so it's 19 okay it's 18.8 percent so in this case you can see that the potential return is in
excess of what we've estimated the risk to be so in this case we think that this return actually
does compensate us for the riskiness of the company so in this case again you know 300 is
probably a good price as long as we've done a good job evaluating the risk of the company so in the
third situation let's talk about a pre-revenue startup what does this mean this means that
there's let's say a really experienced team and they've developed a concept for a new product and
they haven't actually built the new product they need a million dollars to launch the company build
the product and start selling it so this is the kind of situation where the risk is going to be
extremely extremely high I mean the probability of this working is much much lower so if you invest
in this you need to get a really really really really really really high potential return to risk
your money so let's say that we've done all of the analysis and we've estimated the discount rate to
be 50 percent so it's you know the probability of us getting paid back is much much lower and so we
need the upside to be really high and in this situation let's say we can buy you know some
percent of the company for a million dollars today and we think seven years from now we're going to
get make 10 times our money but we don't know how likely it is and so we've estimated the risk to
be 50 percent in this situation let's look at the NPV so 50% and then let's grab the future
cash flows and let's see what it is so in this situation we're only evaluating that you know this
chunk of the company they want to sell us is only worth about five hundred and eighty five thousand
dollars today so probably a million dollars is way too much money and we shouldn't be willing to pay
that and let's also look at the IRR this will again give us you know a good estimate of sort
of what do we think the returns could be let's say fifty percent and I really know thirty nine
percent so we think that the compounding internal rate of return is only thirty nine percent but the
actual riskiness per year is is much higher than the return so in this situation probably this
price is not going to work for us we would have to renegotiate and it would be a bad investment based
on this NPV and IRR model okay that's it now you should feel very confident about how to calculate
NPV and IRR and use them to analyze investments if you liked this video and want more check out
some of my Microsoft Excel training courses below hit the like button let me know in the comments
please please please subscribe to my channel and check out some of my other videos thank you