in this lesson I'm going to describe and demonstrate how to use npv irr and mirr and Excel npv or the net present value is the present value of cash flows and inflows and outflows for a stream of cash flows and typically the discount rate that we would use to calculate MPV would be the weighted average cost of capital for a firm the internal rate of return or IR is the discount rate that causes the net present value of a stream of cash flows to be equal to zero in other words it's the rate of return that the project in many cases is going to earn for the firm the calculation of irr assumes that cash inflows are reinvested at the calculated irr in other words if we calculate an irr of let's say 12% the calculation itself assumes that any cash inflows we receive during the life of a project would also be reinvested at 12% mirr or the modified internal rate of return is the discount rate that causes the npv of a stream of cash flows to be equal to zero just like the internal rate of return the difference between the two though is that Mir does not assume that cash inlows will be reinvested at the irr that's calculated for M we can actually put in our own assumed Finance rate in other words the the cost of the money and the assumed reinvestment rate the return we expect to receive from reinvesting cash flows we receive from the project we oftentimes we use Mir when we get answers that are conflicting between npv and irr for non-mutually exclusive or for mutually exclusive projects or when we get multiple IRS which is what I'll demonstrate today so I'm going to open up the Excel workbook titled Financial functions and I'm in the tab npv IR R and M and as you can see I've set up two graphs to demonstrate the effect on npv of a change in the weighted average cost of capital for our first example we're looking at a a normal cash flow stream what this usually is is a cash outflow at Time Zero representing some investment and then cash inflows based on that investment for the life of a project to calculate an NP npv in Excel it's a little different than if you're using a calculator Excel assumes that the First Cash Flow put in the npv function occurs a year from today or a year from now we don't want to make that assumption um typically we we assume with projects is that we make our first investment today so we're going to have to adjust adust the the function and the formula for npv based on that so to calculate MPV for this project we want to put in equals and then highlight the time zero cash flow because that's not going to go in the npv function and then put in plus npv our rate is the discount rate that we're using again it's typically the weighted average cost of capital in this case it's 10% and then comma and then you have value one value two and so on you can actually just highlight the remaining cash flows for the project you close parenthesis and we get a net present value of $811 for this project the internal rate of return is calculated as equals IR in this case we don't have to separate the time zero cash flow we highlight the C cash flows and in this case we get an internal rate of return of 10.35% this makes sense it's slightly higher than the 10% discount rate and we have a slightly positive npv the modified internal rate of return is calculated as equals m again we highlight our cash flows and then we're asked for two values the finance rate so this is essentially the cost of money or the required rate of return on the money for most of these types of problems we would use the weighted average cost of capital for a firm but there may be cases where you want to use a different rate so I'm going to set this as 10% and the reinvestment rate is the rate at which I expect to reinvest these cash flows so at year one my company's going to receive $200 what's the rate I think I'm going to get for for the return on these cash flows and that's the the rate i' want to put in here for our purposes we'll just set this at 10% as well you'll notice that the Mir is slightly different than the irr because the irr assumes that the cash inflows are reinvested at 10.35% and Mir assumes the cash over flows are reinvested at 10% to the right hand side I I've set up a a table and a graph to demonstrate the effect of a change in the weighted average cost of capital so that's what I have here weighted average cost of capital on the Net Present Value what I want to use here is a data table and we'll use these quite a bit for sensitivity analysis so here I'm going to put equals and select the npv don't just type in the number or it won't do anything it won't work you actually want to set it equal to the number that you want to see changed and then we're going to highlight the rest of the the table here then we go to data what if analysis and then go to data table so it provides us two options a row input cell and a column input cell this data table only has one variable that changes and that's the weighted average cost of capital and it's in a column so we our numbers are in a column here so we're going to ignore the row input cell in this case and just change and just put in a variable for the column input cell and that variable is the discount rate so once I've done that I press okay and this gives me the npv for each weighted average cost of capital I have here and what Excel does here is that it goes in and it changes this number to whatever number is in this column and it provides the result that we have in this column in this cell as we can see when we get to that internal rate of return of 10.35% the weighted average cost of capital Falls below Zer which is what we would expect so that's our normal cash flow stream our non-normal cash flow stream looks like this we have a large cash outflow at time zero and then another large cash outflow at the end of the Project's Life non-normal cash flow streams typically include more than one cash outflow during the life of the project including the cash flow at Time Zero we can get multiple irrs as a result because the percent at which those future cash outflows are discounted back can affect the npv of the project overall so calculating the MPV we do that just like we did it above put in equals highlight the cash flow Time Zero add to that npv the rate at 10% and then the rest of the cash flows and note that I don't put in 0.10 here I actually highlight 10% that's important for the calculation of the data table so we get an npv of $ 41.3 now we have our first irr and our second irr again I highlight these cash flows I get 3.4% for our first irr and then I don't know what I'm going to get for my second irr so here I'm just going to put equals irr and then you'll see it says guess and that guess is in there because of the chance of a multiply where you may have more than one answer that gives us one discount rate that gives us an npv equal to zero so in this case I'm just going to put in 020 kind of because I know the outcome and it gives me an IR second irr of 24.5 6% so now I'll calculate the Mir here I'll put equals m my values and then I'm going to choose 10% my discount rate for the finance rate and the reinvestment rate and I get a modified internal rate of return of 10.41% which would be the actual correct rate of return for my project so now I'm going to graph both the npv and the Mir for each level of the discount rate or the weighted average cost of capital again I'm going to highlight the MPV and I'm going to highlight the Mi so the only thing I've added here is a column for the Mr that's the only thing I need to Cho change I go to what if analysis again so this is under the data tab if you're not there go to data table my column input sell again is the discount rate and then I press okay and you can see here at different weighted average cost of capital we get a different inpv we can see our first IR is equal to 3.4 row and that's where it crosses across that zero line and then our second is at 24.5 6 or again it crosses over that zero line we can see the relationship between ir and the discount rate and it's not unexpected so as the discount rate increases the modified internal rate of return increases because we're increasing the rate at which the cash flows are reinvested for the firm