Future value of cash flows Excel

Those two worksheet functions were introduced in Excel 2007. They perform their calculations on a schedule of cash flows that aren’t necessarily periodic.

Unlike the XNPV function, the XIRR function isn’t merely calculated. Instead, the function uses an iterative program to discover its results. So we can’t do much with that function.

But we can work with the XNPV. To do so, let’s use the example used in the Excel help file, shown in the figure below.

The first two columns are from the help file. Here’s the XNPV function as it’s used in the cell shown:

B7:  =XNPV(0.09,Values,Dates)

Here Values refers to the range A2:A6, and Dates refers to B2:B6. And the annual interest rate is 9%.

Cell B8 contains a formula that calculates the same result using Excel 2003 features. I used a similar version to calculate the Future Value shown in cell D8.

To get these results we first need to find the daily interest rate. This is the rate that yields the annual rate when it’s compounded for 365 days. That is:

1 + Annual Rate = (1 + Daily Rate)^365

Therefore, to find the daily rate we take the 365th root of both sides of the equation to obtain this formula:

1+ Daily Rate = (1 + Annual Rate)^(1/365)

Now let’s see how we can use this information to calculate the present value for the one cash flow number in cell A6:

C6:  =$A6/(1.09^(1/365))^($B6-$B$2)

Here, we’re dividing the value in cell A6 by one plus an interest rate. The amount 1.09^(1/365) calculates one plus the daily rate. We then compound this amount for the number of days between the date in cell B6 and the date in cell B2, which is the base date. That is, the date in cell B2 is “today’s” date for the present value calculation.

When we copy cell C6 to the range C2:C5 we get PVs for each individual value in cells A2:A6. When we sum the results in the cell shown…

C7:  =SUM(C2:C6)

…we get the same result as the calculated value in cell B7.

We should get a sense of accomplishment at this point. In cell B7 we used a new worksheet function to calculate a result that may or may not be correct. In the range C2:C6 we used a formula that seems correct, but could well be wrong in a way that’s not obvious. But because the two calculation methods agree, it’s likely that both approaches are correct.

We can now calculate the XNPV in by formula. The easiest way to do so is to copy the formula from cell C6 to cell B8, and then modify it:

C6:   =$A6/(1.09^(1/365))^($B6-$B$2)
B8:   =SUMPRODUCT(Values/(1.09^(1/365))^(Dates-$B$2))

In the formula shown for cell C6 I’ve inserted many spaces to line it up with the similar parts of the formula in cell B8. You can see that I merely replaced references to two cells with references to two columns of data, and then surrounded the whole thing with a SUMPRODUCT function.

The article, Use Excel’s SUMPRODUCT to Summarize Worksheet Data, introduced this function. As the article explains, the SUMPRODUCT function usually treats its arguments as array values. Therefore, we know that the formula should perform multiple calculations on cells in the ranges shown, and then return the grand total.

The formula for the future value of any one item is quite similar to the equivalent present value:

D6:  =$A6*(1.09^(1/365))^($B$6-$B6)

Here, we multiply cells in the Values range, rather than dividing them. And the base date–the point of reference–is changed from the first date to the last date.

After we copy this formula to the range D2:D6, we can find the total of all the individually calculated future values:

D7:  =SUM(D2:D6)

As before, we can copy one of the single-period formulas and then modify it to directly calculate the total future value for the data in the schedule:

D6:  =$A6*(1.09^(1/365))^($B$6-$B6)
D8:  =SUMPRODUCT(Values*(1.09^(1/365))^($B$6-Dates))

This is the formula that you were looking for, Jim.

But before we quit, we also need to check this formula in another way. By definition, the present value can be converted to the future value by growing the present value by the appropriate interest rate:

D9:  =C7*(1.09^(1/365))^(B6-B2)

Here, we multiply the present value by one plus the daily interest rate taken to the power of the number of days between the first and last cash flow. This test is successful, because our result matches the other two results.

Example A: Your client would like to contribute $12,000 to a retirement account at the beginning of each year for the next 20 years, earning an annual return of 6%. The formula in cell B13 in the screenshot "Calculating Future Value of Annuity With the FV Function," =FV(0.06,20,-12000,0,1), calculates the client's retirement account would grow to $467,913 at the end of 20 years assuming a 6% return per year. Notice that the Type is coded a 1 as the payments are made at the beginning of each year.

Example B: A more likely scenario might be for your client to make monthly deposits to his retirement account. Assume your client makes deposits of $1,000 at the end of each month for 20 years and earns 6% per year on his investments over that time. By applying the formula, as shown in cell B26 of the screenshot "Calculating Future Value of Annuity With the FV Function," =FV(0.005,240,-1000,0,0), he would have $462,041 in his account at the end of the 20-year period. Notice that the Type is coded differently from Example A because this example represents an ordinary annuity instead of an annuity due scenario.

Example C: An even more likely scenario would see your clients already having retirement savings and asking you to project the future value of their current savings combined with additional monthly contributions. Using the information from Example B, let's further assume that your client already has accumulated $200,000 in retirement savings. In that case, you would enter -$200,000 as the PV amount when using the FV function. The formula in cell B39 of the screenshot "Calculating Future Value of Annuity With the FV Function," =FV(0.005,240,-1000,-200000,0), calculates the future value of your client's savings, including the existing savings, is $1,124,082, assuming a 6% return per year.

In these examples, the CPA will review the calculations with his or her clients and evaluate if the clients are on pace to meet their retirement goals. For example, the CPA may need to advise the clients to increase their savings rate, postpone their retirement age, or alter their investment strategy to meet their lifestyle plans in retirement. Excel easily can be modified to accommodate such a What-If analysis.

TIME VALUE FUNCTIONS (PV AND NPV)

The PV function in Excel allows users to determine how much future cash flows are worth in today's dollars, whether the application involves a lump sum or an annuity. This concept is used when trying to determine today's value of the cost of an asset that is to be paid for in the future or to calculate monthly payments for a loan, among other examples. The NPV function can be used when calculating the present value of unequal future cash flows.

EXAMPLES USING PV AND NPV

Calculating the present value of a future single sum

Example A: A client has a desired retirement savings goal of $2 million to be achieved seven years from now. She plans on making only one deposit into her account, and an annual return of 6% per year is expected. By applying the PV function, the formula in cell B12 of the screenshot "Using the PV Function," =PV(0.06,7,0,-2000000), calculates that the client needs to deposit $1,330,114 in her retirement account today to achieve her goal of $2 million in seven years.

Using the PV function

Microsoft Excel as a Financial Calculator Part III

Are you a student? Did you know that Amazon is offering 6 months of Amazon Prime - free two-day shipping, free movies, and other benefits - to students?
Click here to learn more

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:

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. If you have any questions or comments, please feel free to contact me.

Previous: Excel TVM Functions Page 2