Why You Need to know the Time Value of Money Formula (Excel NPV)

Why You Need to know the Time Value of Money Formula (Excel NPV)


A dollar today is worth
more than $1 tomorrow. I’m sure you heard that one before. This is true for any business, but also for your own personal finance. It’s a simple principle
that has a fancy name, the time value of money. For example, you’re selling your product for, let’s say $10,000. One day your customer asks you, if instead of paying the
$10,000 in cash right now, that he can pay $10,800 in
equal payments over four years. What would you say? What’s better for you? Let’s find out. (upbeat music) Before we get started, a brief thanks to Skillshare
for sponsoring today’s video. Skillshare is a learning platform with lots of great courses, but I’m going to chat more
about them towards the end, you’re going to find a link as well in the description of the video. So intuitively, you’ll know that if someone promised you
$1,000 now, it’s a better deal, than let’s say getting the
$1,000 in 10 years, right? But why is that? Well, for one, money has an earning capacity. So you could take the $1,000, and you could invest it, and
hopefully make more out of it. So there may be some lost opportunity to make an additional income. Also, money loses value, because prices are increasing over time. That’s called inflation. And finally, there is a risk factor, who knows what’s going
to happen like 10 years? Will you really get the money then? There’s also that
uncertainty factor involved. The combination of the factors defines the time value of money, and is usually expressed as a
percentage, a discount rate. This discount rate is always specific to your specific situation. So, my discount rate may be
higher or lower than yours, depending on how we
think these three factors are going to impact us
in the coming years. One is, are your opportunity costs high? Because you know of this
other great investment that you could do instead. Do you think inflation will
be 2%, 5%, or even higher? And there it is, do you think the risk of not
getting paid is high or low? Also, the further into
future the payments are, the more opportunity cost, inflation, and risk you’re going to have. That takes away from the value. So when you’re comparing alternatives, always consider the timing of when the payments are going to happen. So, let’s come back to
my initial question, is it better to get the $10,000 today, or get $10,800 over four years. So in order to answer that, we need to compare the
value of both options at the same point in time, meaning today. In other words, we need to calculate
today’s or the present value of the future payments of the $10, 800. So let’s switch to
Excel, and check it out. So here are our two options. Year zero represents today, one means one year from now, and so on. The undiscounted total of $10,800, is obviously higher than
the alternative $10,000 now, By undiscounted, I mean
that the time value of money isn’t considered here. It’s just adding up the payments that we’re going to be
receiving in the future. Because payments in the
future have less value than an equal payment today, we need to discount them. Basically we need to reduce them to get the present value
for these payments. What is $2,700 worth to us right now? Let’s assume our discount rate is 5%. This essentially means that
in each successive year, the same amount of money is
going to be worth 5% less than in the previous year. It’s like the opposite of
earning interest on a deposit, instead of increasing the value, it’s decreasing, the value is worth less. Let’s calculate for year one first. What 2,700 is worth to us right now, basically, what is its present value? First, I’m going to do this
manually using a simple formula. And then we’re actually going
to use an Excel function, to do this whole calculation for us. The manual approach, helps us understand the underlying engine, behind this formula,
behind the calculation. To calculate what this
amount is worth to us now, we’re going to take this number the 2,700, and we’re going to divide it
by one plus the discount rate, close the bracket, press Enter. Now, you might be wondering, why are we dividing this? That’s because we’re moving backwards. We’re going from the
future down to the present. If we were going forward, so let’s assume this
is our starting money, I’m going to put this money in the bank, the bank is going to give
me 5% interest on this. How much is this money in one year? The answer should be this, right. So let’s do the calculation. It’s going to be this number. Instead of dividing, I need to multiply, because I’m going into the future. So I’m going to multiply
this with one plus, this time it’s the interest rate here, close bracket, press
enter, and I get my value. One thing you need to remember, if you’re going backwards from
the future to the present, you need to divide. If you’re going into the
future, you need to multiply. Now if you use the same concept, and calculate the amount
for the second year, we have to change our basis, because our basis is now this. So we’re going to take
this divided by one, plus the discount rate
and I’m going to fix it, because I want to pull this across here. You can see that as the years go by, our 2,700 is worth a lot less,
the amount keeps decreasing. Now, another way of writing this, or a simpler way of writing this, is to use the present
value formula right here. PV stands for present value, CF is the payment in the
future is our cash flow. In the denominator, we
have the discount rate, that’s the r, so that’s
our rate right here. And n stands for the
number of years from now, which we’re going to receive the payment. So it’s just another way
of writing the formula that we just wrote. It’s pretty much the same, so it’s going to take this value, divide it by one plus our discount rate, and I’m going to fix this, but just so that we don’t
have to reference back to the previous year, we can take this to the power
of the period right here. So you need to find this operator. And if you can’t find it, you can also use the
power function in Excel. So with that, I’m going to press enter, and drag this across, and we get the same
numbers like we did before. Okay, so this is just two different ways of doing this manual calculation. Okay, I’m just going to remove this one. Let’s just sum these up
to see what we get, 9,574. Getting 2,700 for four years
is actually worth right now $9,574, which is less than our option one. This means option two is
not a good opportunity. Now the good news is,
if you’re using Excel, we have a great function called the NPV, it’s the net present value, and we can use that to quickly calculate if an opportunity makes sense or not. The first argument here is the rate, which is this one. And then we just need
to give it our value. So this is the cash flow
returns that we’re going to get, which is this range right here. If I press enter, we get the same number that we got here, we just got it in one step. And to see if this opportunity
makes sense or not, I can deduct it from my original option, and I end up with minus 426, which means it’s not a good opportunity, it’s better for me to
get 10,000 right now, instead of getting 10,800 over four years. But all of this was
based on the assumption that the discount rate is 5%. What if the discount
rate was something else? So what if it was 2%? Then things start to change. My net present value here is positive. I end up getting more money, with the discounted version of
option two, than option one. So if the discount rate
was going to be that low, and I could rely on this assumption, I could say yes, 10,800 over four years, is actually a better deal for me. Now, you might be wondering, what’s the correct discount rate to use? Well, that depends on how
you assess the three factors impacting the time value of money. Opportunity, cost, inflation, and risk for your particular situation. So for companies it’s related
to how they get their funds. They use a discount rate, that’s usually an
average of rate of return that their investors expect, and the cost of borrowing money. That’s called their back for weighted average cost of capital. By applying this as the discount rate, any project that delivers a
positive NPV is worth doing. So here’s an example. You want to buy a new
machinery that costs $50,000, you calculated that it would bring productivity savings of $15,000,
for the next four years. Afterwards, the machine is
going to have to be replaced. So if we don’t consider the
return percentage right now, and we just take a look at the cash flow, we’re going to have in
year one, the 15,000. So let’s just fix this with F4, and the same amount
until end of year four. In year zero, our cash flow is negative, because we just spent $50,000 on this. Now, if I just calculate
the sum of this on the side, we’re going to end up
with a profit of $10,000. With this, we might think, oh, that’s actually not a bad investment. And you go to your boss, and you present this
business case to them. Your boss takes a look at this and says, “Okay, that’s great, “but the shareholders expect “at least 8% return on their money.” So he only wants to invest in projects that will give him at
least this kind of return. Is the project worth doing now or not? So let’s see. So let’s put 8% right here, and this time, let’s
immediately take advantage of Excel’s NPV function. Our rate is this one, the values here are right here. If I close bracket and I press enter, this is the present value
of my future cash flows. Now let’s deduct the original
amount that I invested, and since it’s a minus here, I’m just going to add
this to the NPV function, and my net present value
ends up being negative. This means that the project is not going to deliver
the minimum return, the shareholders are requesting. But what if the minimum
return was a little less? What if it was 6%? Then everything starts to look different. The net present value becomes positive. So that’s how this time
value of money is applied in a simple business case. I want you to take away two key things. Always consider the timing
of when payments are done. The further in the
future the payments are, the lower their present value will be. Think of the discount
rate as like a hurdle. The higher the hurdle, the more difficult it’s going to be to get a high present value. If you’re looking for a good
resource on personal finance, or managing your money habits, I can recommend Justin Bridges’ course. It’s called Modern Money Habits: five steps to build the life you want. It’s about taking charge
of your personal finances. Now what I especially liked about it, is how he records
everything in a spreadsheet. So you have a clear
overview of your finances. Taking classes in Skillshare
is really affordable too. An annual subscription
is less than $10 a month, and premium membership
gives you unlimited access, so you can join any class and any topic that interests you right now. Whether that’s freelancing, technical skills, like office skills, or any topic you’d like
to explore more of. Because Skillshare is
sponsoring this video, I have a special link for you in the description of the video, that is going to give you
two months free trial. So make sure you check it out. I hope this video was helpful for you to get familiar with the concept
of the time value of money. And I hope you enjoyed it, if you did, give it a thumbs up, and consider subscribing if
you haven’t done so already. (upbeat music)

43 thoughts on “Why You Need to know the Time Value of Money Formula (Excel NPV)

  1. The most basic concept that should be taught to every child. Still dont know why excel hasn't fixed the issue of giving negative values with PV and FV though.

  2. [15865] Of course i love y'r flyx! (from the first one i watch'd over a year ago – i waz hook'd! &, i have no right to complain; but, i couldn't locate to workbook @ the workbook link?  i really love this Accounting/Economix series

  3. Someone help me understand why would money have less value in the future? Is inflation always a case? Does this apply to Companies or could this work for a friend-to-friend loan? Where do Discount Rates come from?

    This is really cool

  4. So well explained. I used this formula (with a financial calculator) to beat up on my car finance guy and got super low rates. Glad you are explaining this Leila, excellent job once again. ⏳💰

  5. just wondering, are there any plans for accounting excel for udemy… trying to make two bookkeeping for my family business, thank you

  6. When I make a chart, i can insert a the power trend function on that chart.
    Is there a way to find the expected value for the max r^2 by using Excel formulas…

  7. Ms. G, nice job as always. Your knowledge of Excel is the cake; your clear, concise presentation is the icing on that cake. Thank you.

  8. Nice introduction to Present Value. When I teach The Time Value of Money I always start with Future Value as the idea that if I invest a dollar and earn interest I will have a dollar plus. Students tend to understand this better. Then I can explain Present Value by show that I am indifferent to a dollar now or the dollar plus in the future (for the given discount rate).

  9. I love this video! I watch almost every video from you and this is my bread and butter. I have a degree in finance and I work in asset management. TVM of money should be taught in every high school as a mandatory part of a personal finance course. The world would be a lot better if people knew how to manage personal finances.

  10. Great video, the only thing I didn't get is how we ended up with 10800 and why is not considered as changing variable. Why changing only the percentage not the 10800

  11. I appreciate you Leila, you are an amazing teacher. The way that you explain along with visual features, make learning sweet and easy.

  12. Great video, time value of money is a key concept in finance. Could you make a video about how to calculate the discount rate in case we don't deal with a company? Keep up the great work 😀

Leave a Reply

Your email address will not be published. Required fields are marked *