# 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)

Nice video! Keep it up! Would you like to be YouTube friends? :]

U r right mam

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.

Beautiful as usual

awesomely epic

[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

i don't get it, 5% from 2700 is 135 mean it should be 2565 but your calculation makes it 2571

In transport economics we have "money value of time" 😊

Beautiful

❤❤

Another fantastic learning opportunity, great Video!

Very useful for personal finance thanks

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

Could you break down the XNPV function as you did here?

Great video as always!👍

One of the greatest functions ever : )

I always wanted to know how to use that formula and what discount rate meant. Beautifully explained. Thanks

Important stuff they don’t teach in school…

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. ⏳💰

Very nice! Including IRR would have rounded this out nicely.

Another great video. Thanks!

Love the clarity and simplicity and variety of this NPV video.TᕼᗩᑎK YOᑌ!great explanation

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

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…

I enjoyed it

Hi

I was wondering what would you like to see ms team add to Excel?

Or do you think Excel has reached its limit

Thanks

brilliant explanation

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.

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).

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.

💲

How can I show on charts ❓ is it possible..

Just when you need the information 🤩

You are good in explanation!

This video is very informative. It is too important in Financial Management.

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

This is part of project management and management accounts part of mba finance and bbm (economics honors)

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

Huh should be a dollar today is worth less than a dollar tomorrow.

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 😀

Danke Liebe Leila, die schönste Erklärung des Kapitalwerts, Viele Liebe Grüsse 🤗

Thanks mam

Tell about employee daily attandence for the excel sheet vlookup formula