Microsoft Excel Tutorial

PRESENTER: Hi, guys. Welcome to subjectmoney.com. This is our video series of Excel tips and tricks. And in this video, we're going to go over how to find the present value of multiple future cash flows of different amounts.

Now, I'm already assuming that you pretty much know, have a decent understanding, of present value. But just in case, I'll kind of explain it. Basically, what present value is is most people know that a certain amount of money in the future, say $100 in the future, is not equivalent, does not have the same value as $100 today.

And the reason why is because if you had $100 today, you could invest that money. And it would earn interest. It would give you a return, making it worth more than $100 in the future. So why would a $100 in the future be worth $100 today?

Kind of a simple explanation of it is just to imagine that you had an account that you earned 10% interest on. OK. If you put $100 on in account, one year from today, it would be worth $110. And two years from today, it would be worth $121.

So, basically, what we're saying, if 10% is your discount rate, that's your opportunity cost. $121 two years from today, discounted the present value today, would be worth $100 if your opportunity to invest was 10%. And $110 one year from today, if you had a 10% account that you could invest in, would only be worth $100.

So that's kind of the concept of present value. All right, so what we're going to do, let's just go ahead and move into the purpose of this video. I'm gonna go over how to find the present value using Microsoft Excel.

OK. So you can see here I have a little chart. This right here stands for today, today plus one, which means one year from today, two years from today, three years from today, four years from today, and so on. And for each year, I have an expected cash inflow.

So like one year from today, I'm expecting to receive $100. Seven years from today over here, I'm expecting to receive $1,000. So we want to find the present value of all these cash flows. Because this could be one single investment maybe that's bringing in these cash flows. So what is the present value of this investment?

All right, so first I'll go to the long way to do it. I could go here and just enter in the formula for each cash flow. So that would be equals the cash flow I'm referring to that cell, F8, the cash flow, divided by-- so the form is going to be the cash flow divided by 1 plus the rate of return. That's our opportunity cost or our discount rate.

Now, I'm going to hit F4. Because we don't want this cell. When we drag this formula over, we want this to stay put. And we want to stay locked into that cell. But this, we're not doing it. Because when we drag these over, we need to be referring to each cell.

All right, so we close parentheses, and then I put the power of 1. And that gives a present value of $92.59. I need a new mouse. All right, let me do the Show Formula real quick.

OK. So I'm going to go ahead, and I can drag this over. And this is basically a lump sum present value formula in each one of these. Because we're finding the present value of a single cash flow.

So I can drag this over. Now, what I have to do, though, is this cash flow is two years from today. So it needs to be discounted back two years. So it's going to be the cash flow divided by 1 plus the discount rate to the power of 2, or however many years it is until we receive it.

So over here, it's going to be discounted three years. And we will do that all the way until our final cash flow. And we need to go in here and change this formula four years, five years, six years, and seven years.

All right. Now, so you can see we have the formula for each cash flow. Now, I'm going to get out of the Show Formula mode. And now you can see in this row, we have our present value of each cash flow.

OK. So the present value of multiple future cash flows is going to be the sum of the present values of each cash flow. So =sum, that's the sum formula. And then all we have to do is we're doing the sum of-- and in parentheses, we highlight all of these numbers, the sum of all the present values.

So our present value is $2,260.80. All right, now that was the long way. I'm going to go ahead, and I'm going to show you the short version in Excel. And that's using the Net Present Value mode.

Now, I'm going to go ahead and delete this row, because we don't even need it. So right here, we have all of our cash flows. All we're going to do is we hit =npv, which stands for Net Present Value.

And then right here, you can see it tells us what to do. So it says to enter the rate. So we refer back to our rate. And it says enter a comma. So we hit a comma. And now, we're going to add up all of our cash flows.

Oh, no, no, no. Sorry. We're not going to add them up. Comma-- click them and a comma, and then close the parentheses. And you can see the net present value is $2,260.80, the same as what we had before, except for we didn't have to do all of those calculations.

So again, I'll show you the formula. And you can see it. It =npv, the rate, and then each cash flow to be received. And you cannot skip years.

Even if one of these years doesn't have a-- maybe you don't expect any money. You still have to enter it in. Because say right here I made this 0, it's going to change our net present value. But we still need to have that cell entered into this formula.

Because it's discounting each one of these back. It's discounting this one one year. If we were to skip it, then it wouldn't discount it back the right amount of years.

All right, so that concludes this tutorial of how to find the present value of multiple future cash flows. Make sure to visit our website at subjectmoney.com and to share our videos. And stay tuned, because we have plenty more coming in the future. Thanks, bye.

PRESENTER: Here I'm going to talk to you about the present value, what it is, and two really easy ways that you can get a present value in Microsoft Excel. So firstly, you may or may not know that present value essentially tells you how much to invest today, so that you have a certain amount in the future. So the basic premise of it is in 10 years, I want $50,000. OK, well, how much do I have to invest today and at what interest rate in order to get $50,000? Well, I find the interest rate by the type of investment or security that I'd like to invest, get the average interest rate, and then I can figure out exactly how much I need today in order to have that 50 grand in 10 years.

Now, we're going to talk about, like I said, two ways to do it. This is the mathematical formula right here. The present value equals the future value over 1 plus the interest rate raised to however many periods there are. I've got all the syntax listed right here with what every argument means and does. The important thing to remember is that N or number of periods can be any time frame, right, could be weeks months, days, years, quarters. The only important thing to remember is it all has to stay consistent. I'll talk about what to do when you have to separate it by months later on, but for now, let's just keep our number of periods to years. It will make everything a little bit easier for this.

So this is the basic mathematical formula. We'll talk about-- we'll actually use that in a second. The next thing is the Excel formula. So let me actually hide this real quick. The formula or the function that we're going to be using is the present value function, and it has a number of arguments, rate, number of periods, the payment, the future value, and the type. So let's go ahead and go through those right now.

Now, the rate is going to be the interest rate that you receive on your funds. So let's go with the most basic example, right. I want 50 grand. I've got a bank account. I'm only going to put my money within the bank accounts. So how much do I put in this bank account today to get 50 grand in 10 years? OK, so the rate that my bank is giving me is 3.25%, not so bad, right. The number of periods is going to be 10, because I want to do this for 10 years. Well, the number of payments-- well, this is not an annuity problem. I'm not going to be putting any money in my bank account. I want to know how much I'm going to make solely based on-- or solely based off the income from the interest payments.

The next thing is FV or future value. That is $50,000. How much do I want in the future? I want 50 grand. The last argument is going to be the type. This is similar to the future value function. No, actually, it's exactly the same for type, and that the type simply means whether or not you're going to receive money or input money at the beginning or the end of the period. Now if you leave type blank, it's assumed that any amount of money that's due is going to be at the end of the period. If you put a 1 for type, all of the money is going to be entered in the beginning of the period.

That's really just for an annuity, though, so we don't have to worry about that right now. So let's go ahead and use the Excel formula first. So equals PV, open parenthesis, let's get our rate, well our interest rate is right here, 3.25%, select that, comma, number of periods, 10 years, comma. Our payment, well, are we going to be paying anything into this? Am I going to add $100 every week or $1,000 every month? No. So our payment is 0 comma. Now for the future value, what do I want to do for that? Well, I want 50 grand. So that's all that we need. We don't have to worry about type, close the parenthesis, hit Enter, and you can see that we need $36,313.61.

Now, why is this negative? It's negative, because it's basically saying that you need to pay this much into your bank account today in order to get $50,000 in 10 years. Now the way that we fix that is simply double click the cell and put a negative sign in front of the function. It's not going to hurt anything. It simply makes it a positive number. Now let's go ahead and use the formula.

So this is the formula. Don't forget future value over 1 plus the interest rate raised to the number of periods. We'll do that right here very quick equals future value divided by 1 plus the interest rate raised to the number of periods, 10, close parentheses, hit Enter. And it is exactly the same. So those are the two different ways you can calculate the present value in Excel. And if you're in Excel, really you're only going to be using the present value function. So I will leave you with this.

PRESENTER: Here, I'm going to go through three examples of a typical present value problem or question in a finance class of college level, or something similar to that. Now in a previous tutorial, I've already told you what the present value function is, how to use it, and basically what present value means. So this one, I'm not going to spend too much time on that. So let's go ahead and dive right on in.

The first question is a very basic one. With an interest rate of 6%, what is the current value of $7 million if you will receive it in 15 years? This is sort of a typical example. What's worth more? So much in the future, or so much now? So let's figure out what it's actually worth in today's dollars, or using only interest rate.

So equals, pv, open parentheses. Now our rate, that's very easy. Just our interest rate of 6%. So 0.06. Now remember, when you're doing the interest rate here, you have to do it as a decimal. So 0.06. You can't type in a whole number like 6. It's not going to interpret that correctly.

The number of periods. Well, that's very easy. It's 15 years. So our number of periods-- 15 comma. Payment-- are we going to be paying into this at all, or is anyone going to be paying us at all over these 15 years? No. So payment is 0, because we're only talking about one lump sum in 15 years.

But we do know the future value. So what is the future value? $7 million. Now if you wanted, you could type it in as seven, 7, 7,000, or actually, 7 million. If you're doing this in the real world, you're going to break it down to a smaller number, such as 7, for 7 million.

So now we have the rate, right here. The number of periods-- 15. Payment is 0, because we're not going to be paying into it. It's not an annuity. And the future value is 7 million. Let's close parentheses, and hit Enter.

So it tells us that the present value is just about $3 million. Now why is this red? Why is this negative? Well, because it's assuming that this is your cash outflow. So you're going to put this 3 million in, say, a bank or a bond that pays 6%. And to put your money into something, you have to pay it out.

But to get rid of the red, the negative, simply put a little minus sign right in front of the pv function. Once you do that, you'll notice that it's a positive number once again.

All right, so let's go ahead and go to the second present value problem, on the second tab. What is the present value of putting $500 into an interest bearing account with a 2.75% interest rate for 6 years?

Now this would be considered the basic annuity problem, right? An annuity, a set of equal cash flows that you're investing at an equal rate over a period of time. So the equal cash flows being $500 invested, let's say, once a year for 6 years. So we're going to keep it easy by keeping it at years for now.

So equals, pv, open parentheses. Our rate, very easy-- 2.75%. Remember, put it in decimal form, 0.0275 comma. Number of periods, very easy. We're sticking with years for now, so 6 comma. Now the payment. Well, this time, we are going to be paying into the account every year. So the payment here is going to be 500, because that's how much we're paying in.

Now we don't have to worry about a future value for this problem, because we're not trying to figure out how much one lump sum in the future is worth today. We have many payments into the account. So close the parentheses. We got the percentage for our rate, number of periods, 6, and the payment, which is $500 going in every period.

Simply hit Enter, and we see you will end up with, or in today's dollars, it's $2,731.18. Once again, to make that positive, double-click, put a negative sign in front of it, and hit Enter. And that's it for that problem.

So just remember that since this is an annuity, we do have to fill in the argument for the payment. This one right here-- pmt. So the payment basically is what you're going to do for an annuity, right? Future value, if you want to figure out what one lump sum is worth today.

So let's go on to the third example. It's a little bit different, maybe a little bit trickier, but same premise. So if you know that you can sell something, say, an asset, in 3 years for $170,000, right? And you know that the discount rate for the asset is 4.25% per all of your due diligence and your own research. Well, then, what are you going to pay for the asset now?

So this is a present value. It's a little bit different, but the point is, how much money you going to shell out now so that you can sell it for 170 grand in the future with a 4.25% discount rate? All right, so the way to do this, exactly like before, just a different word problem.

So equals, pv, open parentheses. Once again, our rate. Well, that's easy, right? Discount rate, that's our rate-- 4.25%, so 0.0425. Now how many years would we like to discount this for? Well, we want to discount it for 3 years, so 3 for the number of periods. Number of periods-- 3.

Now for the payment, are we going to have any payments in or out? Well, let's say that this is a non-cash flow generating asset, right? Could be a mainframe for data backup, or something like that. So the payment's going to be 0.

But we do have a future value. The future value is $170,000. If you had it in thousands, you would simply write 170, but I'm going to put the full number here-- 170,000. Close the parentheses and we're done.

So all I did here, the rate is the discount rate this time. It's not called interest rate, but it's the same thing for our purposes, for what we're doing. Number of periods-- 3 years. And there is no payment. It's just a simple lump sum in 3 years, right? It's worth $170,000 in 3 years. So that is the future value of it.

Now what's it worth today? Let's hit Enter and find out. So today, it is worth $150,044.72.

Now once again, this is a negative number. You can see red has the parentheses around it, because you have to pay that much money in order to get this asset, or to gain the asset. So it's considered a cash outflow, right? Negative. But to make it a positive number, simply go before the function, negative sign, Enter, now we have a positive number.

So that's about it for these three examples. I think we've pretty much covered a broad range of things. This was probably the most difficult example. But don't forget, just because the word problems, the wording's a little bit different, the inputs are going to be relatively the same. So that's it for these examples.

PRESENTER: Real quickly, I would like to show you how to use Excel to complete a future value time value of money problem. And let's look here. I put together a little sample problem. Let's read it. You invest $50,000 in a CD that matures after three years and pays 4% interest. How much will the CD grow to after three years?

So if I hadn't already told you it was a future value problem, you could have determined it by this question here. What will be the future value in three years of that particular CD? So that's what we're going to be solving for.

Now all of your time value of money problems start the same way. They all start with filling in the variables that you know and then solving for the unknown. In this case, we know everything else here. What we don't know is the future value.

So present value. As of today, we are going to invest $50,000. So go ahead and enter $50,000. Now I just set up a little chart here. Anyone can do that or can set anything up just as easily by typing in the squares of your Excel.

And then that matures after three years. So n is going to be our number of periods. In this case, it happens to be years. It could be months. If it is months, we have to adjust the interest rate accordingly. Since it's not, in this case, and pays 4% interest, our interest rate is going to be 4%.

And I have a little comment here. You can enter it as a percentage or a decimal place. So it doesn't matter. It's entirely up to you.

Payment, that's a reoccurring type of item. So maybe you were going to invest $50,000 today and then make additional payments every year of $1,000, in which case-- and that $1,000 has to be reoccurring, the same payment continually. In this case, we don't have one. And as I told you, I always fill in what I know.

I'm going to go ahead and enter 0. And then future value is what we're solving for. Come up here. Excel makes it so easy. Go to Formulas, Financial-- looks kind of overwhelming. Future Value right here.

And then we can just plug everything in. You can also skip filling out the chart and come right here and enter in the information. I like to go ahead and enter it in my chart. And then I'll just click on it. Or as I said, you could enter 0.4 or 4% over here.

Number of periods, 3. Payment was 0. Present value, 50,000. The type we can go ahead and leave blank. That is going to be dependent upon when we actually make the timing of the payment. So in this case, we're going to go ahead and assume here that we're making it at the end of the year. Click OK and we've got our answer, 56,243. The reason why that's negative, if we would adjust our present value here as an outflow of cash, making it a negative 50,000, which would be an outflow, then we would receive at the end of the three years, $56,243.

Real quickly, let me show you, if we were going to make it in months, how we would go ahead and do that. And the great thing about Excel, since I entered everything over here, all I have to do is modify these things. So let's say it was 36 months instead of three years. And we would need to adjust our interest rate there as well. So we'll do 4% divided by 12 for making it a monthly rate.

Sorry about that. Make sure it'll run the calculation. And it's slightly higher, because the interest rate would be compounding more frequently.

But as you see, you can adjust anything. Once you get the basic foundation set up, you can adjust any of these numbers and future value will calculate for you automatically. Hope that was helpful. Thank you.