“I would like a Future Value command similar to the XIRR and XNPV functions, which allow compounding by using an interest rate and referencing cash flows and their dates. Any suggestions?” — Jim C.
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.
