How to Return the FUTURE VALUE for a schedule of cash flows
that is NOT PERIODIC with a fixed internal rate.
In other words the other side of XIRR function where a series of
dates and cash flows and internal rate is known.
or XNPV (with FUTURE value rather than PRESENT value)
You want the opposite of XNPV, not XIRR. There are some gotchas, but
ostensibly you compute the FV of the XNPV. It is always best to work
with an example. Consider the following, in columns A and B starting
in row 1:
1/1/2007 -1000
4/1/2007 100
11/1/2007 200
5/1/2008 300
8/1/2008 400
1/1/2009 1000
If the IRR ("fixed internal rate") is 12%, the most "consistent"
result (see below) would be:
=fv(rate(365,0,-1,1+12%), A6-A1, 0, -XNPV(12%, B1:B6, A1:A6))
Note that the daily rate for FV is RATE(...), not simply 12%/365.
RATE(...) produces a compounded daily rate. That is somewhat
debatable; half of the financial experts might choose 12%/365. It
depends on how we assume the IRR is computed. IMHO, since XNPV
effectively uses a compounded daily rate, I think we should use a
compounded daily rate for FV.
Also note that since the starting and ending dates are a multiple of
years apart (namely 2 years), we might think that we should be able to
compute the answer simply by:
=fv(12%, 2, 0, -XNPV(12%, B1:B6, A1:A6))
But you will notice that the result is slightly different. This is
because, in my example, we include the leap month of Feb 2008, and
XNPV uses the difference between dates in actual days.
So in general, I think it is better to use daily compounding for FV
because that is effectively what XNPV does.
(If we changed the years to 2009 and 2010, the simplified formulation
would give the same result -- but again, only if we use the compounded
daily rate in the first FV formulation.)
HTH.
