UNEVEN CASH FLOWS IN EXCEL

Microsoft Excel as a Financial Calculator Part III

In the previous section we looked at using the basic time value of money functions to calculate present and future value of annuities (even cash flows). In this section we will take a look at how to use Excel to calculate the present and future values of uneven cash flow streams. We will also see how to calculate net present value (NPV), internal rate of return (IRR), and the modified internal rate of return (MIRR).

Example 3 — Present Value of Uneven Cash Flows

This is where Excel really shines in comparison to financial calculators. For all intents and purposes there are no limits on the number of cash flows that you can have in your functions. Financial calculators do have a limit on the number of uneven cash flows. Furthermore, Excel makes it very easy to change your cash flows to answer "What if?" questions, or if you made a data entry error.

To find the present value of an uneven stream of cash flows, we need to use the NPV (net present value) function. This function is defined as:

NPV(Rate,Cash Flow 1,Cash Flow 2,Cash Flow 3, ...)

Note that we don't generally list each cash flow separately. Typically, the cash flows will be in a contiguous range on the worksheet and we simply give the address of the range for Cash Flow 1.

Suppose that you are offered an investment that will pay the following cash flows at the end of each of the next five years:

Period	Cash Flow
0	0
1	100
2	200
3	300
4	400
5	500

How much would you be willing to pay for this investment if your required rate of return is 12% per year?

We could solve this problem by finding the present value of each of these cash flows individually and then summing the results. However, that is the hard way. Instead, we'll use the NPV function. Set up a worksheet as shown below:

Now, select B11 and type: =NPV(B1,B5:B9) and you will see that the answer is $1,000.18. Make a special note of the fact that we did not include the period 0 cash flow in the function. The NPV function has no way of knowing when a cash flow occurs, so it assumes that the first cash flow in the range occurs one period in the future. If we had included a period 0 cash flow, then the function would have given us the present value as of one period ago (i.e., period -1). Please see my blog post for more on this topic.

As before, you can now change any of the numbers in the worksheet and immediately see the result. For example, if you change B1 to 10% you will find that the answer is now $1,065.26. If you change B9 to 1,000 then the present value (still at a 10% interest rate) will change to $1,375.72. Reset the interest rate to 12% and B9 to 500 before continuing.

Example 3.1 — Future Value of Uneven Cash Flows

Now suppose that we wanted to find the future value of these cash flows instead of the present value. There is no function to do this so we need to use the principal of value additivity. That means that we find the future value of each of the cash flows, individually, and then add them all together.

In the picture above, you can see that the future value (at period 5) of the $100 cash flow in year 1 is $157.35 (C5). That cash flow needs to be taken four periods forward (moved from period 1 to 5) so the formula in C5 is: =FV($B$1,$A$9-A5,0,-B5). Notice that I calculated NPer by taking the period of the last cash flow (5, in A9) minus the period of the current cash flow (1, in A5). Also, note that the dollar signs in the cell addresses serve to freeze the reference so that when I copy the formula down those addresses won't change (i.e., they are absolute references). Copy and then paste that formula into A6:A9 and your spreadsheet should look like the one in the picture. Now, to find the future value of the cash flows in B11, use the formula: =SUM(C5:C9). The future value is $1,762.66.

That's not too difficult, but I find it a little sloppy to use a helper column when it isn't absolutely necessary. There is another way, as seen in the picture below (note that I have eliminated the calculations in column C).

Realize that one way to find the future value of any set of cash flows is to first find the present value of those cash flows. Next, find the future value of that present value and you have your solution. The picture, below, demonstrates the process:

We've already seen that we can calculate the present value of these cash flows using the NPV function, so we'll just plug the NPV function in for the PV argument in the FV function. The formula becomes: =FV(B1,A9,0,-NPV(B1,B5:B9)) and the answer is the same as before, $1,762.66.

Pretty easy, huh?  Ok, at least its easier and neater than adding up the future values of each of the individual cash flows. It is also less confusing to anybody who looks at your spreadsheet.

Example 4 — Net Present Value (NPV)

Calculating the net present value (NPV) and/or internal rate of return (IRR) is virtually identical to finding the present value of an uneven cash flow stream as we did in Example 3. However, be aware that Excel's NPV function doesn't really calculate net present value. Instead, it simply calculates the plain old present value of uneven cash flows. It does not, and this is vitally important, take the cost of the investment into account. (See my blog post on this topic.) We'll see how to deal with this in the example below.

Suppose that you were offered the investment in Example 3 at a cost of $800. What is the NPV?  IRR? MIRR?

Make a copy of your previous worksheet and paste it into a new worksheet, so that it looks like the one below.

Recall that the NPV, according to the actual definition, is calculated as the present value of the expected future cash flows less the cost of the investment. As we've seen, we can use the NPV function to calculate the present value of the uneven cash flows in this example. Then, we need to subtract the $800 cost of the investment. Therefore, the formula to calculate the net present value is: =NPV(B1,B5:B9)+B4 and the answer is $200.18. Note that since the cost of the investment is given as a negative number in B4 (it is a cash outflow), I had to ADD it to the result of the NPV function. In other words, the PV of the cash flows is $1,000.18 as we calculated in example 3, and subtracting $800 leaves us with $200.18 (the net present value).

Example 4.1 — Internal Rate of Return

Solving for the IRR is easier, because the IRR function does take the initial outlay into account automatically. This IRR function is defined as:

IRR(values,guess)

Note that the "values" is a contiguous range of cash flows, including the initial outlay. The "guess" argument is optional and generally isn't needed. For this problem, the function in B12 is: =IRR(B4:B9).

As seen above, the answer is 19.54%. This means that if you purchase the investment for $800 today, your compound average annual rate of return will be 19.54% per year.

Example 4.2 — Modified Internal Rate of Return

As noted in the definition of IRR, the IRR calculation implicitly assumes that you will reinvest the cash flows at the same rate as the IRR. Often, this assumption is unrealistic and can lead to expectations about the rate of return that are too high. That is why it is better to calculate the Modified Internal Rate of Return (MIRR), which allows you to specify an alternative reinvestment rate.

The MIRR function is defined almost identically to the IRR function, except that it has a reinvestment rate argument (and there is never a need for a "guess"):

MIRR(values,finance_rate,reinvest_rate)

In this function "values" is a contiguous range of cash flows (including the initial outlay), finance_rate is your required rate of return (i.e., the discount rate), and reinvest_rate is the reinvestment rate. Excel will use the finance_rate to calculate the present value of all of the cash outflows, and the reinvest_rate to calculate the future value of all of the cash inflows. The MIRR is the interest rate that makes the present value of the outflows grow to the future value of the inflows over the life of the investment.

Theoretically, the reinvestment rate should usually be the same as the cost of capital, so we usually set both the finance_rate and reinvest_rate to the same interest rate. To calculate the MIRR in B13, use the formula: =MIRR(B4:B9,B1,B1).

As you can see, the MIRR is 17.12%. This means that if you pay $800 for the investment and reinvest the cash flows at a rate of 12% per year, you compound average annual rate of return will be 17.12% per year.

As a final note, realize that this investment should be accepted because its NPV is greater than 0, its IRR is greater than the 12% cost of capital, and its MIRR is also greater than its cost of capital.

I hope that you have found this tutorial to be helpful.