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.

by Charley Kyd, MBAMicrosoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

"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,
Add-Ins, 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:

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.

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.