The cash flow is the actual history of this investment,
When I set up the IRR function, I have every date,
(4/28/2006 through 3/31/2008) [...].
Any cells without values are set to 0 so the
If you are saying that you have an entry for every date (including Sat
and Sun), then IRR will compute a daily rate of return. In that case,
you would annualize it by the formula: (1 + IRR(range,guess))^365 -
1.
You will probably need the "guess" parameter in this case, as I
believe you are doing, based on one of your follow-up postings.
If you do not include Sat and Sun, or if you do not otherwise have
equal cash flows, the IRR result will be misleading.
amount invested, surrender value, and account value
from start to finish
IRR and XIRR deal with cash flows, not level amounts. But it would be
equally misleading to treat the difference in investment value from
time to time as a cash flow. The cash flows are: amounts that you
invest (inflow); interest earned or dividend payments (inflow if
reinvested; outflow otherwise); and ending value.
My intent is to able to get the same answer using XIRR
instead of using this brute force method.
As I explained previously, IRR computes a __periodic__ rate, assuming
that each cash flow represents an equally-space period. To compare
its result with XIRR, you must annualize the IRR result based on the
size of the period. In general:
daily IRR: =(1 + IRR(range,guess))^365 - 1
weekly IRR: =(1 + IRR(range,guess))^52 - 1
multiple months IRR: =(1 + IRR(range,guess))^(12/m) - 1
where "m" is the number of months per period.
I'm trying to learn how to shape the XIRR request
to match what I know is right.
The "shape" of the XIRR model is the same as the IRR model, with the
additional detail of specific dates. You use XIRR only if the cash
flows are irregularly spaced (or you want to bother with annualizing
the IRR result); otherwise, use IRR.
If the XIRR result does not closely match the annualized IRR, the
problem is likely in your model -- the values that you are using as
"cash flows" -- which would adversely effect the IRR result as well as
the XIRR result.
Let me know if this is helpful. Otherwise, I will stop wasting my
time beating a dead horse.
----- original posting -----
The cash flow is the actual history of this investment, When I set up the IRR
function, I have every date, (4/28/2006 through 3/31/2008) amount invested,
surrender value, and account value from start to finish in a column from
start to end in from R2C1:R705, Any cells without values are set to 0 so the
IRR will function properly with the calculation being done for the entire
history of the investment i.e. inception to date.
My proof also matches.
My intent is to able to get the same answer using XIRR instead of using this
brute force method. I'm certain the end result is valid. I'm trying to learn
how to shape the XIRR request to match what I know is right.
:
PS....
I wrote:"
Dates XIRR
12/31/2007 $61,445.04
1/7/2008 $4,062.21
2/6/2008 $4,062.02
3/6/2008 $3,921.48
3/31/2008 -$72,906.91
[....]
With your data, I compute the annualized IRR to be -0.025682814, and
XIRR computes -0.033609948. They are equal when rounded to 2 decimal
places. "Close enough for goverment work" ;-)
For an even better comparison, change the first date to 12/7/2007 and
the last date to 4/6/2008, so that the periods are now almost
monthly. The annualized IRR is -0.02568, and XIRR returns -0.02583 --
almost the same, as expected.
Note: There will always be at least a small difference between the
annualized IRR and XIRR because XIRR the varying number of days
between "monthly" dates (i.e. say day number each month).
