Hi, everyone and welcome back. In this example, we're going to learn how to use Excel's NPV, net present value function, and we're going to decide if a product is worth pursuing based on an NPV calculation. Please read the problem if you have not done so already, pause the video, see if you can set up the initial given values, and then let's start to model the problem. Ready? Let's begin. A large drug company, Gopher Drugs, is deciding on whether one of its new drugs Iguazu is worth pursuing. Iguazu is in the final stages of development and will be ready to enter the market one year from now. The final cost of development to be incurred at the beginning of Year 1 is 9.3 million. That sounds like an important given piece of information, so let's put that down somewhere. Let's start to write down the givens. Let's call this development cost. We should also probably give our spreadsheet a header. We can title and make all pretty in a little bit. Let's move things down and spread out some columns. What I'll do here is I'll just put down 9.3, but I'll remind myself that my units are in millions, so I do not have to write down all the zeros and we'll see we can work with this going forward. Let's keep reading the givens here. The company estimates that the demand for Iguazu will gradually grow and then decline over its useful lifetime of 20 years. That sounds like another piece of information we want, lifetime 20 years, put our units in the next column. This makes sense, things become popular and they start out growing and growing, and then they slow down and decline over time. These numbers and this assumption seems reasonable. Specifically, the company expects its gross margins, which is revenue minus cost, to be 1.2 million in Year 1. Let's call this year one margin and call it of course, something similar. What you call it doesn't matter, it's more important that it actually gets labeled. Here's million again. Maybe I want to move the cost things together, but we'll figure that out to move things around a little bit. Then I want to increase at an annual rate of 10 percent through Year 8. This is two numbers are coming at me here, 10 percent, so increase through year... This is like it's going to increase through Year 8 by how much? 10 percent, fantastic. We'll label this the rate of increase. Finally, it will decrease at an annual rate of five percent through Year 20. It'll decrease, our rate of decrease will be five percent. Gopher Drugs wants to develop a spreadsheet to model 20-year cash flows, assuming the cash flows other than the initial development cost are incurred at the end of their respective years. Using an annual discount rate of 12 percent, that sounds important, put that down as well. We don't want 12, we want 12 percent. For purposes of calculating NPV, the drug company wants to answer the following questions. Is the drug worth pursuing or should Gopher Drugs abandon it now and not incur the $9.3 million development cost? Here's the question. If you're the manager of the situation, you have a decision to make. It's going to cost you $9.3 million now to develop this drug and it will bring in money, it's 1.2 in the first year, it's going to increase by 10 percent. For eight years, it will decrease over its lifetime, over 20 years. Will I get the money back? Is this something that I should as a manager decide yes on? What we're going to do is build out a table. We want to build out a table of cash flows. I can show everything on the screen. Now that we've read the question, let me slide that over for a second, and let's look at cash flows. We want Year 20. Let's just do end-of-year and we'll go from 1-20. Remember, I don't really want to type 1, 2, 3. Let's highlight one and two. Let's put the mouse on the bottom right corner and then let's drag it down to show 20. I'm not going to sit there and type. Let me shrink the size so that I can show all these numbers. This looks pretty good. There's my end-of-year first table and what I'm after is my gross margin. Remember this is all dollars and in particular, this is millions of dollars. I want to be very careful here that I remind myself this is in millions of dollars, so I'll put it dollar, M to say these are my gross margins. It's a table, so let's try to make it readable. Let's merge and center perhaps the header. Let's put some borders on this thing. All cosmetic pieces. They don't affect the functionality but they certainly don't hurt. Let's figure out what's going on here. In my Year 1, this was given, so let's link over to $1.2 million and there it is. Maybe we'll center some things as we go. It looks a little nicer. We can turn on the formatting for this as well. Let's see. Let's use currency and we'll show one decimal just because. Here's my question. I have to figure out Year 2's cash flow. Now, I was told it's going to increase for eight years at 10 percent. There's two ways to do this. We could break this up into two pieces or do it all at once. You want to pause the video here and see if you can figure out the formula to increase 1.2 million by 10 percent. Pause the video, see if you can figure it out. Ready? First of all, it's a formula, so equals, I'm going to take the 1.2. You want to do this in one shot. You can multiply by parenthesis 1 plus the rate of increase, 1 plus B7. That will give us 1.32. This is perfectly fine. I've seen some folks do this also this way where they take the number and then they add 1.2 times 10 percent. They find 10 percent of the number as a friendly reminder of means multiply, then they add it together and they get 1.2. However you do it, it does not matter as long as you get the right answer. This is a formula, so I'll show the formula text. There it is. Now there's something going on here and hopefully you're yelling at me on the screen here. If I drag this down, watch what happens. Something gets weird. I guess I really only want to go to Year 8. But if I'm supposed to grow by 10 percent every year, I probably shouldn't get 1.55, 1.55 every single time. The dragging, something went wrong. Maybe you see it, maybe you don't. Do you see what's going on? When I drag these cells, remember they're all referenced, so my cell of 10 percent is going to drag down with for example when I click the next cell, it's now B8 instead of B7. I'm actually using the wrong rate. I'm using the five percent rate of decrease to try to increase. Let's not do that. Let's not have the wrong formula when I drag. Let's fix this. How do I fix this? How do I tell Excel to not drag as I drag? We need to put the dollar signs or the absolute referencing on B7. When I do that now I can drag down to eight, and you see this nice, manageable, reasonable 10 percent growth across the board. Fantastic. I just have to be careful to drag it all the way down to eight. I don't want to go past that. It tells me it increases through Year 8, and then after that, for Years 9 through 20, I'm going to decrease. Now I need to start to decrease this thing. Pause the video, see if you can find the formula and be careful of the referencing to decrease my numbers by the given rate of five percent. Ready? Here we go. It's the number before minus 5 percent of the prior number. Mimicking the style of the format that we just did, you can certainly do this all at once and write this as F11 times parentheses 1 minus B8. That is exactly the same. Whichever one you prefer works for me. Once you have the formula, don't forget to put the dollar signs on and then you can drag it down and you can see the numbers start to go up and then they start to fall. We have a nice model of the cash flow following the given rates of 10 percent and five percent, increasing through Year 8 and decreasing after. This is great. We'll use formula texts for the rest of the columns, we'll expand this, make this readable. We have our nice table that models the cash flows. There's one thing in here that I don't particularly love. All the numbers in our given table of information we use, we have our development cost, we'll get that in a second when we compute our NPV. But there's something about this table that I think we can do a little better. We actually never referenced this cell for me it's B6, the year which the increasing changes. If this changes to nine or 10, absolutely nothing happens in our table. This is a missed opportunity. We always want our spreadsheets to be malleable, to be able to be updated easily, to play with different scenarios. I never referenced the cell. What I'd like to do is do a little better. You're one is going to stay the same. There's no change there. Again, that changes the table will update. If I type 1.3 million you see the table update and all the numbers change. Let me put back 1.2. I'd like to know if the year changes, how do I do this? Well, let's think about a better way to do this. Let's take our formula and check if the year is less than Year 8. If I'm less than this given Year 8, then I will decrease. This is going to be a cell that I need to also lock in. Let's do it now. If it's less than Year 8, then I want to increase. I'll use my formula for increase. If I'm greater than Year 8, that's actually decrease. We'll use the formula for decrease. I'll just type it out again. I take last year's number and then I subtract last year's number times the rate of decrease. I have to be very careful to put the dollar signs in all over this. This is a very good exercise in absolute referencing using those dollar signs. Hit "Enter" when you're done, and now the formula get a little more complicated. To use the IF formula, pause this and make sure you understand how IF works. This is a fantastic use of IF. I can now drag this formula down and it's smart enough. Notice the numbers on the table didn't change. I had the right numbers the whole time, I'm just making the spreadsheet smarter, more usable. Now watch this. If I go over to the givens and I change Year 8 to Year 9, be very careful, keep an eye out over here at Year 9, change occurs and I start to increase at Year 8, and then at Year 9, I start to decrease. You can play around with this and really see it's working. Let's pick a much larger number. How about 15? Now I have an increase all the way up to 15 and then I start to decrease. This is where it becomes much more usable as a model. Always try to give your spreadsheet the most functionality. All right, now I actually haven't answered the question, the question is, is the drug worth pursuing? NPV, or Net Present Value is a function in Excel that will give you the price today of a stream of future cashflows. It's like how much should I pay today for money in the future? This is a wonderful metric that managers use to decide if a future project will bring positive value to the firm. Your NPV is positive, means this is the value of the project. Positive usually means good, we should go for it. Negative means it will lose money, so we're looking for a positive NPV and if you have two projects or three to compare, then the project with the higher NPV will be the one that you're interested in. Now, I say this though, I like Excel, that's not true, I love Excel. But this particular function bothers me. When you write NPV, when you talk about net present value, I have to explain this in every class. Net present value always means that it's net of something. Net here means something has been subtracted off. I've taken the present value and I've subtracted something off. When you have your net salary, we compare it versus our gross salary. Taxes have been taken off. This particular function in Excel is inherited in this way, it's really not an NPV, it's not a net present value, it's just a present value but for historical reasons, for compatibility reasons, they've not changed the title. Be careful when you use the NPV, cover up the end with your thumb and just realize this is just a present value calculation. It is not going to subtract off anything, you have to do that manually. That's the last piece for us to do, it's a little annoying that they still call it NPV, I've yelled to everyone I can, but the folks who make Excel don't listen to me, so there we go. All right, let's do it. I'm going to put this a net, I'll put it in parentheses because it's still part of the formula you should know it, but just realize it's just the present value and this will be our new formula, if you haven't used this one already. Slide things over a little bit. This is the NPV formula. Let's start typing equals NPV and open the parentheses and realize what it's asking and in what order. First it needs the discount rate. What rate am I going to discount the dollars back at? That was given to be 12 percent, we just click it there. Then it's asking what are your cashflows? You can highlight the entire table, highlight all 20 values, and hit enter. It is assumed there are other options to do, if you don't have money that occurs at the end of the year, you have to update some options on NPV but here we're told that the values occur at the end of the year. There are other options if you want to explore this function a little further. This is keeping it simple, but this is the present value, which unfortunately Excel calls NPV. Let's show the formula here. Fantastic. Again, this is like revenue, but in today's dollars, this is money made. Then if you want to find your total net present value, your actual net present value, we have to subtract something off. What are we subtracting off? Well, the development costs. We'll just copy that again. We'll grab that number from above from our given table that's the 9.3. Again, please keep in mind here, everything here is in millions, we'll keep the formatting consistent, let's change it all to currency. Why not? I just like a little better. There's the actual present value using your 12 percent discount rate, using our NPV function, unfortunately called NPV and then we use our development cost and now we actually find the true net present value. Again, it will not do this for us, we have to subtract, we just take our money made 11.85 minus our cost, minus our expenses, and we get a very nice positive present value of 2.55, friendly reminder, million dollars. The way to interpret this number is as a manager, if we green-light this project and we say go for it, we will add $2.55 million value to the firm in today's money. This answers the question, is the drug worth pursuing, or should we abandon it now, positive. If the option is to go make 2.55 b=million, I think that's a good, that's a big yes, positive NPV is a good thing. Then of course, how do changes in the model change the answer to the prior question, we added some nice functionality to it. I'll let you guys play around with it, if the lifetime is shorter, if we increase for the years, what would the conditions be that make the net present value negative? Again, once this thing turns negative, we would say no, let's not incur this cost. Have some fun with the model, see if you can even expand it, enhance it, give it even more functionality as you improve on your Excel skills. All right, watch out for that NPV function. Great job on this example. I'll see you next time.