[SOUND] Alex's next task is to use Excel functions to calculate the Net Present Value of two investment opportunities. As we heard in the discussion videos, Net Present Value is the difference between the Present Value of cash inflows and the Present Value of cash outflows. And so Net Present Value can be used to analyze the profitability of projected investment of project. For example, our investment opportunity one. If we invest $2 million up front, we get the following expected cash flows, $600,000 at the end of the first year 800,000 at the end of the second year, and 1.1 million at the end of the third year. We would like to see what that means in today's terms. Now, to do this, we're going to need to work out the present value of each of our cash flows, bearing in mind that $1 in the future is going to be worth less than $1 today. We want to see what $600,000 in a year's time is worth in today's money. And we do that using the following rather scary looking calculation. But let's see how it works. So P is our cash flow, I is our interest rate, also known as a discount rate. And t represents our time, or the number of periods. So clicking in cell C16, we're going to type =, then take our cash flow and divide that by 1+ our discount rate, which is 4.5%, to the power, I'm going to use a little caret for this. Our time or, in this case, our number of elapsed years, and we're going to click Enter. So in today's money, that first cash flow will actually be worth $574,000. Let's copy that down to get the rest of our cash flows as the Present Value. Now, to work out our Net Present Value, we're going to sum all those, and then very importantly, net of our original investment. And that is our net present value. Now, that was the manual way of doing it. The good news is, Excel gives us a function to automate this process for us. And it's called NPV, so let's see how much easier that is. We're going to click in cell C 11 and type =NPV. The first argument it requires is the interest rate, also known as a discount rate, and then a comma. We now need to specify all our cash flows. The NPV functions assumes that the periods are constant and that the cash flows occurs with the same periodicity as the interest rate. However the cash flows do not need to be equal. The NPV function assumes that the first cashflow occurs at the end first period. Therefore, one thing to be aware of, the NPV function does not automatically offset the initial investment as it usually occurs at the beginning of the first period. If the initial investment comes at the end of the first period, you can include it as a cash flow. But in this case we are paying it up front. So we're not going to include it in the cash flow of the NPV function. We're just going to select our cash flows, and then once again, we do need to manually net off our initial investment. And that is one of the common mistakes made with the NPV function. Thus, if your initial cash flow is upfront, i.e., at the beginning of the first period, you will need to manually net it off. When we click Enter, we have now got exactly the same result. Now, let's go and do the Net Present Value of our second investment option. So we're going to click in cell G11 and type =NPV. Our rate is the same, so 4.5%, and once again we are going to select our cash flows. And the net of our initial investment. So at first glance it looks like we're getting a much better return on our first investment option. However this is hard to compare as the initial investments are not the same and of course neither are the cash flows. So while this is one measure, it doesn't tell us what our actual rate of return is. Another way we can compare two investment opportunities is using the internal rate of return. This is the rate of growth a project is expected to generate. And while the actual rate of return will often differ from this estimate, the project with substantially higher internal rate of return or IRR will be preferred of another project with a lower IRR. Assuming the company's cost of capital is equivalent in both projects. Let's see how we can calculate IRR. We're going to click into cell C12 and type =IR and press Tab. We're now going to select the cash flows, and once again, the IRR function does expect the periods to be constant. It also requires that all the cash flows, both the initial investment, and subsequent cash flows, are stored together in the workbook. So that is one factor to be aware of when designing your workbooks. We're now going to select all of our values, including our initial investment. And then we have the very curious argument called guess. It is optional, and generally you won't need to supply this. But it is possible that the internal rate of return would not be able to solve the problem. If this is the case, you can give it a sort of starting point. So you can say, we think the internal rate of return will be around this, and that can help Excel get to your solution. In this case we don't need to supply it, so we're going to close the brackets and press Enter. Now, what we've actually calculated here, this IRR, is the rate at which our present value will return zero. This means that another investment opportunity that has the same cost of capital would have to get a rate of over 10.85% to be an improvement on this option. Let's have a look at our option two. So once again =IRR, select all your cashflows, and Enter. And interestingly enough you'll see that option two actually has a higher internal rate of return. And is therefore a better investment option, assuming the cost of capital football projects are equal. So we've now looked at how we can use Excel's MPV function and IRR function to estimate the rate of growth of different investment opportunities.
