Excel Financial Functions
Find Future and Present Values from Scheduled Cash Flows in Excel
Here's how to set up a Future Value formula that allows compounding by using an interest rate and referencing cash flows and their dates.
"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 are new with Excel 2007. They perform
their calculations on a schedule of cash flows that aren't necessarily
periodic.
Users of Excel 2003 can use those two functions by choosing Tools,
AddIns, and choosing Analysis Toolpak. You also can create a formula that works like
the XNPV function. You
can use a similar formula to calculate future values in either version
of Excel.
The XIRR function, on the other hand, isn't merely calculated. Instead, the
function uses an iterative program to return its results. Therefore,
Excel 2003 users can't create an XIRR function without using the
Analysis Toolpak or VBA.
But with regard to the XNPV, let's use the example shown in
the Excel 2007 help file, which is shown in the figure below.
The cells in blue 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. (Use this link for an Excel 2003 version
of the SUMPRODUCT article.) 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 datethe point of referenceis 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 singleperiod 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$6Dates))
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))^(B6B2)
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.
Tags: #excel, #future value, #present value, #cash flows, #XNPV,
#SUMPRODUCT, #XIRR
