How to use net present value (NPV)

his presentation we will work a problem related to future value in Excel The information will be up top we're going to enter that information into the blue area down below We do recommend setting up the information in this format whenever working a problem That being the information for the problem up 

top with the text in one cell the numbers in another so that we can refer to the numbers with cell references That being the most efficient way to move forward Remember that when we think about the future value there's a couple of different ways that we can calculate the future value want to keep them all in mind You want to keep them in mind to know that they're all the same thing we're not talking about different animals different things here They're all gonna be the same type of thing Once you know that it's a lot less intimidating

 to see these different functions and see people talking about these different things because you'll know that they're all related and you can use whatever method is best for you to go through and double check these things So we'll take a look at a few of them here The future value of course we could do with a formula that's not what we'll be focusing in on here Most of the time in practice and even in schools and in study we don't often use the formula here And when we go to the annuity we typically don't really need the formula although they could ask you to do it with the formula if they so choose 

Oftentimes in study there's gonna be tables that are gonna be asked to use tables are really useful because they allow us to use a simple calculator for it for school and also not make us go through the calculations of the formula So they're gonna be similar to what we would do in practice which of course would be excel but without excel in essence and then of course with Excel we'll use excel functions in order to do these types of calculations generally So we want to know that they're all related We want to have an idea of course what is happening as we go through these calculations data up top periods we're gonna be two we're going to say they are two years remember that the years it could be something other 

than years In other words we could be talking about months or something like that rate 10 percent remember that the rate typically means the rate per year So if it was a rate for some other time periods such as a month we would have to take that 10 percent and divide it by twelve to get the coinciding rate to the number of periods then we have the amount which is going to be the 200 dollars that's going to be the amount that we are going to be using that is in today's dollars that we're going to future value and see what it will be in future after two years time period at the 10 percent rate Now there's often 

confusion about what the future value means and what this rate means here So what we're talking about here is if we had 200 dollars today what would if the same amount be worth if we didn't get that two hundred dollars in essence two until two years from now In other words how much money would someone have to give us two years from now In order for it to be equivalent to today's money the two hundred There's a couple of things in consideration here one is just gonna be the time value of money 

So you might say well that should be the rate then should be coinciding with the inflation rate because two years from now I'm going to be able to purchase less stuff and therefore it should be somewhere with the inflation rate which you would think would be a normal times three to five percent Or you can think of well I could invest my money right now and the opportunity cost of what I could be investing 

and the rate of return is gonna be the value of the money So given that that if I think I can make 10 percent on it then that to two years from now you'd have to give me something that's worth basically the 10 percent higher for two years in order for it to be of equivalent value given my opportunity cost So this rate whether what we're talking about with this rate could change depending on what our objective is whether it be an investment type of discussion or just basically Time Value of Money type of discussion But in essence we know that generally the dollars today are gonna be worth more than 

dollars in the future So the two hundred dollars today if you're gonna if you weren't gonna give me something till two years from now you're gonna have to give me more than two hundred dollars to equal the value that we could have gotten today with it So given that in mind we're gonna use the table first So using the table we're just going to take the amount which is gonna be of course the two hundred dollars and then we're going to use the table on the right here to to find the rate note we have the periods 

on the left and then across the top we've got the percentages we're looking for 10 percent and we're looking for two time periods So we're looking at the one point to one note when when when we're considering their future value it's going to have to be something that's going to be greater than one because if we're talking about what how much money you would have to have in the future it's got to be something higher than what we have today in order for them to be equivalent 

If I get to if I get two hundred or the equivalent of two hundred two years from now it's gonna have to be some amount in dollar terms that is higher than the amount today So we're gonna say all right here is gonna be the rate we're going to say this is gonna be one point to one and that'll give us our future value The future value which we're going to just multiply out as the two hundred times the two point two one and that's gonna give us If I reformat this cell into a proper format in the 242 we can do a similar 

calculation in excel Couple ways we could do it Let's go with the formula box first So we're gonna go to the formulas up top we're gonna use the insert function You can type in future value and you're looking for the F V future value This is the one description down below if we select Okay we get the input box So this is our function box or arguments that we could have the function arguments the rate is gonna be the 10 percent which is in Cell B 4 and then the number of payments we're just gonna go down to the 

number of periods number of periods is going to be 2 in our case 2 years Now again if this was something other than year like a month you'd have to divide the rate by 12 because if we want to get a monthly rate in that case and then the number of payment payments are going to be zero And this is the tricky factor because we're going to use the same function for both annuities as well as single sums So therefore we're gonna have zero in the payments so that we can use the same function and we want to pick up what the present value is Here's the present value that what we have now that we want a future 

value after a two year time period So there's gonna be our data we're gonna say and here's the result 242 looks correct We're gonna say okay it has a negative I usually flip the side to get a positive number of this function by double clicking on it going before the F and just putting a negative which will in essence take that and multiply times negative 1 to give us that 242 Now let's do the same calculation without the formula box but just with a formula something to delete this and do it again we're going to say negative instead of equals so we flipped the sign where you could put equals and don't have a 

negative number F the future value double click the future value and we're just going to follow our little instructions down here First they want the rate so right it's gonna be up top 10 percent then we say comma to get to the next function That's common Excel functions So then we're gonna say number of periods number of periods up top is gonna be two number of periods is two and then we're just going to say comma to get to the next item and this is gonna be the payment amount This is the tricky one There 

is no payment amount because this is not an annuity So we're gonna say zero That's the tricky piece And then we need the present value up top that's going to be the two hundred And then we could just close this up shifts 9 and enter and there's our two forty two once again Okay So we could go through kind of a check type of figure on this as well This might help us to kind of solidify this in our minds So if we say the check if we say Okay let's start off with the present value of the amount let's just say the at 

present let's call it the present value at present value And we're gonna say this is gonna be the two hundred and then we're just gonna say the rate is equal to the 10 percent and this is gonna be after one year Then there's gonna be an increase after one year of two hundred times ten percent That's gonna give us twenty dollars so the amount then after one year instead of two years or the future value after one year would be equal to the two hundred plus the 20 And then we had we had two years so we're 

gonna do this again The rate is 10 percent so the increase is going to be equal to that to 20 times 10 percent or the twenty two So the amount after at year two is gonna be equal to the two 20 plus the twenty two and there's are 242 So there's just that kind of a calculation you can see how this basically works out If you if you do a check figure kind of calculation it might help to get this stuff going in your mind And now we're gonna look at the goal seek and we're gonna use the function of goal seek Now remember that we're usually looking for the future value with this equation and we know we basically want to memorize that function in Excel but we could ask for something other than the future value 

Now if they ask for present value we'll use the present value function We should know that one Well those are the two we want to know But they might ask us for like the rate the interest rate or the number of periods And And if we could figure that out in a formula by plugging everything else in if we knew everything else But one variable and then solving for it and we could do that same kind of function in Excel and let Excel do the math for us So that's what I would recommend doing here So if they ask us for it say the interest or the number of periods and they give us all other data which could happen in practice we're going to say all right well instead of me learning three different formulas in Excel which I 

could do we can look up three different You know we could figure out but we can learn the main to present value future value And that helps us with the annuities and the single sums and then use something like a goal seek to basically figure out the other factors with regards to those formulas that they could ask So here's scenario 1 if we want to know the rate let's say we don't we don't know the rate but we do know what the future value should be We know that the present value is 200 We know the future value is the 242 We need to know what the rate is Well I can say I you know I have no idea what 

the rate is but let's just plug in a rate then use our present value our future value formula and then ask Excel to figure out the rate that will make the future value formula equivalent to what we know which should be forty two So let's do that we're going to say okay let's just choose a rate let's say I don't know 2 percent 2 percent on the rate and then we'll go down and we'll put in our future value formula hoping to get to 42 No one we won't because we just guessed a rate out of nowhere it's not going to be right but we want to get the function in first so we're going to stay negative future value DoubleClick the future value it's gonna be the rate comma the number of periods and the number of periods should be too So I changed that be aware so too and then comma and then the payment amount was zero That's the tricky 

one comma and then the present value is two hundred and then we're going to close this up again We have all the information to get the future value but that 2 percent is wrong We just guessed So we know the future value isn't going to come up to what it should be 242 Instead it's at 2 0 8 Now we want to change the rate until we get this number to 242 So I could say well what if it was 3 percent It's closer What if it was 4 percent And we can basically ask Excel to do what I'm doing here Until we get to this 

number to be what it should be which is in essence the same as basically looking for the missing variable in a math equation So we'll let Excel do that To do that And again you can put your cursor anywhere to do the goal seek function So we're gonna go to then the goal seek and the data up top we're in the forecast group we're looking for what if analysis and then goal seek goal seek That's the one we're gonna set this cell so we're gonna pick the cell that we want to set we want to set this cell to be what we know it should be 242 it should be that 242 by changing the one that we just guessed randomly the four percent so let's review this again we're saying hey Excel please set the cell B thirty please set that cell 

that 216 to be what we know it should be the 242 Dubai Do that by changing the related cell that is connected through formula That being the interest rate just check all the interest rates you can until you find the one that makes that cell what we know it should be two forty two So we say OK And then it checks all the rates and it finds there it is 10 percent happens to be it That's the one So that's that's an useful tool to use It could be used in much more complicated formats as well Let's do the same thing Let's say we don't know the number of periods So again I know the rate and I know that the present 

value I don't know I know the future value to its to 42 but I'm going to use a formula here to get the future value and then allow Excel to back into the number of periods So I'm gonna say I don't know the number of periods let's I mean let's say it's like 20 I mean I have no idea And then we'll put in the future value and say negative future value double click the future value The rate is 10 percent comma no periods we said 20 We don't know we're just going to say 20 a comma the payment is zero because it's 

not an annuity comma and the present value is going to be 200 Now we're hoping this comes out to what we know the answer to be 242 It won't because we just guessed 20 for the number of periods So we're at This is way off Right And we need to ask too high what if it What if I said 18 periods better 17 

and I can And now I can basically say hey Excel make that whatever it should be to make the future value right How do we do that Goals seek So we're going to cut the data up top We're going to go to the forecast what if analysis goal seek and we want to set this cell to be 242 By changing the cell So again once again what are we saying we're saying hey excel Would you please make this cell be what we know it to be 242 we want you to do that by changing the cell that we don't know until it makes it happen through the functions and formulas that we have entered the present value of future value formula changed that 17 to whatever you have to do