XIRR in Excel 2003

[Howard Kaikow]

There are two common situations:

1. Place an order to buy $10000 of a mutual fund. Due to fractional shares,
and rounding, it is not uncommon to see value on that same day be $9999.99.

2. Purchase a stock at, say, 11:00 A.M. and then value may be lower later
that same day, e.g., at market close or in after hours market.

XIRR, in Excel 2003, handles these cases if the value is >= the purchase
price, but not if the value is < the purchase price, in which case a NUM
error is displayed.

Has this been fixed in Excel 2005?

 

[Jerry W. Lewis]

There is no Excel 2005. The next Windows version of Excel is not even
in beta yet. If it were, beta testers would be prevented by
non-dicslosure agreements from answering your question.

From Help for XIRR "XIRR expects at least one positive cash flow and
one negative cash flow; otherwise, XIRR returns the #NUM! error value."

Jerry

 

[Howard Kaikow]

From Help for XIRR "XIRR expects at least one positive cash flow and
one negative cash flow; otherwise, XIRR returns the #NUM! error value."

Yes, that's got nothing to do with the issue I raised.

 

[JE McGimpsey]

Perhaps it's because I don't use XIRR very frequently, but I'm a bit
confused about how you're using XIRR since, according to Help, dates are
"truncated to integers" and "all other dates must be later than (the
first date)". So I'm not sure how you are using XIRR for what appears to
be intra-day analysis...

Why does it matter what the same-day value is, unless you're generating
a cash flow?

I have no idea what XL2005 is.

 

[Ron Rosenfeld]

There are two common situations:

1. Place an order to buy $10000 of a mutual fund. Due to fractional shares,
and rounding, it is not uncommon to see value on that same day be $9999.99.

2. Purchase a stock at, say, 11:00 A.M. and then value may be lower later
that same day, e.g., at market close or in after hours market.

XIRR, in Excel 2003, handles these cases if the value is >= the purchase
price, but not if the value is < the purchase price, in which case a NUM
error is displayed.

Has this been fixed in Excel 2005?

I have Excel 2002. Does 2003 accept date and time inputs for the date values?

In 2002, all dates must be later than the first date. That would not be the
case in the example you give.

Or perhaps the magnitude of the annualized rate, based on a change taking place
over just a few hours, is too large to be expressed.


--ron

 

[Jay Petrulis]

There are two common situations:

1. Place an order to buy $10000 of a mutual fund. Due to fractional shares,
and rounding, it is not uncommon to see value on that same day be $9999.99.

2. Purchase a stock at, say, 11:00 A.M. and then value may be lower later
that same day, e.g., at market close or in after hours market.

XIRR, in Excel 2003, handles these cases if the value is >= the purchase
price, but not if the value is < the purchase price, in which case a NUM
error is displayed.

Has this been fixed in Excel 2005?

Hi,

Can you provide sample data to replicate this problem?

In my tests, I had the following in A13
24Aug05 -10000 -10000 -10000
24Aug05 -1500 -1500 -1500
24Aug05 -SUM(B1:B2)-1 -SUM(C1:C2)+1 -SUM(D12)

I got #NUM! errors for the first two and 0 (correct) for the third
scenario.

If I changed the first date to 23Aug05, XIRR returns values
(annualized, make sure you unannualize for the periodic return).

If I changed the last date to 25Aug05, XIRR reports values as well.

If I changed the middle date to the earliest date, XIRR worked for me.
If I changed it to the last date, XIRR bombed on all three situations
(including the zero case).

XIRR weights the flows as of the end of the day, where you are trying
to weight them at the beginning, mid, and end of day. You may want to
use the BAI method for that, then.

For your specific instance, what valuation are you given for the
initial mutual fund purchase? If it is the NAV as of the previous
market close, you can safely change the value date of the purchase to
the previous day and XIRR should work.

Basically, what I think is happening is that XIRR is not recognizing
any days in the period. The End Date - Beg Date = 0 and it is bombing.

HTH.

Regards,
Jay