Net Present Value Excel Demo

Symon He
A free video tutorial from Symon He
Author | Investor | Entrepreneur | Stanford MBA
4.5 instructor rating • 12 courses • 230,853 students

Lecture description

Demonstration of the Net Present Value calculations through examples using Excel

Learn more from the full course

Intro to Analyzing Rental Income Properties

Learn the fundamentals of investment analysis specifically for rental income property investments

06:20:26 of on-demand video • Updated November 2020

  • Understand key metrics for evaluating rental income properties
  • Confidently evaluate rental income property investment opportunities
  • Use professional-grade investment models to evaluate their own flip or rental deals
  • Understand the core concepts of real estate investing
  • Understand professional real estate investment strategies and techniques
  • Confidently evaluate fix & flip investment opportunities
English [Auto] All right. Now let's see how the NPV and the concept of this count rate is applied in an Excel format so that you could get a better understanding of these concepts. So let's look at the first one right here. First you've got to download this file. This file is part of the file here that you can download and just go to the NPV worksheet here or you can ignore the other ones for now because we're going to use this file again later to look at the internal rate of return. All right this first part here is the one future payment. All right if we have a future payment let's say of a $1000 coming in five years and it's discounted at 10 percent. What is its net present value today. So we see here that it's calculated at 621. So if I click on this right here we see the formula. The formula is saying it is equal to the future payment divided by 1 plus the discount rate which is raised to the power of five years. And if you change any of the assumptions here. So let's say if you increase the value in the future then this will increase or decrease it will decrease as expected right. And then the more years you discount it the lower the net present value of that future payment. So let's say instead of five years is coming in 10 years you're going to see the net present value drop quite a bit. Okay but what if you change the discount rate. So it's it's taking ten years instead of five years. But let's say it's half half that this carrot while it's going to go back up again. Okay. Doesn't exactly completely offset but you see that if you increase the discount rate or the time or changes anything to the rate or the time component you're going to affect the net present value calculation. So you change any of these are going to change that. Just play with the numbers put in different values here and put in different assumptions and put in different discount rates. Easy how the net present value will change based on that. All right. Now let's take a look at what it looks like if we were to evaluate two different options. Okay let's look at an example here. So the example here is you get $750 today or you can get $100 a year for 20 years. All right. So under dollars a year for 20 years which is a better option. Well the $100 a year for 20 years you're going to be cogging to Grann over 20 years whereas the other option you get some under 50 in just today right. And if you're only looking at the total dollar amount and you don't look at the time value of money you're not discounting these future $100 back then of course. Option 2 is better. But as you learn now we need a discounted. So let's forget what it might be if we have a discount rate of 10 percent. Okay. So because this is already in today's value we don't need to touch anything in terms of the option 1 but we need to discount all of these future values for option 2 so that we can compare it apples to apples. All right. So let's let's see what that looks like. So if we calculate here I'm going to use the net present value calculation for each of these payments. So we see here that the hundred dollars in a year is only worth $91 today. And then the further you go out let's go all the way out to 20 years the $100 and 20 years at a 10 percent discount rate is only worth $15 today. So what's the value of all of the discounted net present value as officials payments what we simply sum all this up and when we do we get this calculation of the net present value. So we see here that if you have a discount rate of 10 percent applied to this option then you're better off by going with option 2 because there's net present value is greater than the option 1. So there is some discount rate at which this net present value is actually worth less than that today. So let's see if your discount rate is really high let's say 20 percent. You see that the net present value of that stream of cash flow is only where $487 to you. What that means is instead of looking at this option collecting $100 over this timeframe you're better off by taking this now because you're able to collect 20 percent returns on this. This here. There is some rate of return that once you surpass that your discount rate makes it more worthwhile for you to just take all that money. Option A. What is that. Well there's one way we can do that and it's a really cool tool in Excel that allows us to solve for the discount rate where the option between 1 and 2 basically will be the same in terms of its net present value. And so the way we do that is we take this value here and when we go to the data we go what is and we go to Goal Seek. OK. So the goals seek what it does is it helps us solve for something without having to go repeatedly punching in something a number there to try to see what it is or what we're going to solve is we're going to set this net present value to equal to that value which is 750 by changing the discount rate. OK. And when we do that it's going to automatically calculate a discount rate where it sets the net present here. So what this is telling us now is if your discount rate is higher than eleven point nine percent then you're better off by taking the option one. But if it's lower than eleven point nine percent and for most people it's going to be much lower than you're better off with option 2. Now let's look at a similar example but slightly different again. Option 1 is that the right away but Option 2 now is $200 a year over 10 years. Basically you're getting the two grand in 10 years versus two grand over 20 years. So now you're getting more of the cash coming in earlier which if you had to guess what would that break even discount rate B would it be higher or lower than the previous example here. Should be higher. Right. Because now you're getting more of that money earlier. Let's see what it looks like if it's at the same discount rate. We'll see that the discounted cash flows we calculate it back and when we sum them over all of them we see that net present value at the same discount rate from the previous example. Now it's at 11:35 which is much higher than option 1. So if we do the Goal Seek again we'll see that the indifference discount rate between these two options is going to be much higher. Okay. And let's see what that looks like. Now let's set this one to 750 and then by changing this country. There we go. So we see that wow. In order for the two options to be identical basically your discount rate is going to be twenty three point four percent. So what it's saying is if you have a discount rate or an opportunity cost that is lower than this then it's going to be better for you to take option 2. So when you look at these net present value calculations and these options what is telling you is you should never just look at the nominal value or what the dollar amounts are in option 2 for both examples the total dollar amount is too grand but the timing of it what your opportunity costs would be factoring both of those in will help me to make a better decision about which option is better.