# Thread: XIRR and stock/mutual fund purchases (Probably all

1. ## XIRR and stock/mutual fund purchases (Probably all

I've been using XIRR to get the annualized return on my investments.

Is there a way around the following problem using XIRR, or some other function.

Say I invest \$10000 in a mutual fund.
Due to the use of fractional shares and rounding, it is not unusual to find that the share value after the purchase that day could be \$9999.99.

So feeding XIRR 10000 as the investment amount and 9999.99 as the value on that same day results in a NUM error.

Is this inherent in the mathematics of the XIRR function?
Or is it a bug in Excel?

2. ## Re: XIRR and stock/mutual fund purchases (Probably all

Could you give an example of the numbers you feed into the XIRR which gives errors?

The Help lists a variety of things which yield errors. I think it is partly the "nature" of iterative calculations. Have you tried changing the "guess" value?

Steve

3. ## Re: XIRR and stock/mutual fund purchases (Probably

22 Aug 2005 -8000.00

22 Aug 2005 7999.99

4. ## Re: XIRR and stock/mutual fund purchases (Probably

Note that XIRR works correctly for

22 Aug 2005 -8000.00

22 Aug 2005 8000.00

5. ## Re: XIRR and stock/mutual fund purchases (Probably

Howard

Our Friends at Excel Tips-XIRR function give a good explanation...hope this helps

6. ## Re: XIRR and stock/mutual fund purchases (Probably

Howard - the problem is that you are trying to calculate a return for two values that occur on the same day. The XIRR calculation determines r such that the sum of the transaction amounts discounted by (1 + r)^(date difference/365) = 0, where the "date difference" is the number of days from the initial transaction to the transaction in question. If the date difference is zero there is no value of r that can solve the equation unless the transaction amounts are equal. That is (1 + X)^0 = 1 for all X<>-1. I suspect from the behaviour of the XIRR function that it first tests the total of all undiscounted cash flows (ie, r=0). If that sum is zero it returns a zero XIRR without ever calculating any of the discounted values - it doesn't need to - and that is why the special case of no change in value on the day yields the correct, if uninteresting, result that it does. You cannot avoid this by "time-stamping" the transaction as a contribution at 9:00 am and a valuation at 9:00 pm - according to the XIRR helpfile entry dates are truncated to integers.

Note that if you have multiple contributions this is not a problem - even if the valuation is on the day of the last contribution, since the discounting works "properly" for all of the other transactions, and the last contribution and the valuation just have the same discount factor applied. This means that it is only an issue for the instance where you are evaluating a single conribution on the day it is made

I would suggest a couple of work-arounds:
<UL><LI> Date all contributions for the day before they are made - the difference in IRR will not be noticeable over the long run, but will avoid the error you are seeing now
<LI> Date the valuations for the day after they are made - you could rationalize this as the value you (might) get if you decided to sell based on the value on Monday night, since the order would go through on Tuesday
<LI> Test the calculation - if the valuation date equals the initial contribution date, return a blank [/list]

7. ## Re: XIRR and stock/mutual fund purchases (Probably

i realize al of that, but i feel that computers should do the right thing.
no reason, the special case cannot be detected.
mutual fund orders are processed the same, not the next, day.
the value is usually known by 17:30.
the case is very common when purchasing mutual funds.

8. ## Re: XIRR and stock/mutual fund purchases (Probably

Howard: I would have expected that you would know that if general purpose software like Excel were programmed to detect and "do the right thing" for every possible special case, then the software would be too large to fit on a 100 gig hard disk, and too slow for the fastest super computer. You should be able to detect the special case that you are interested in with a simple IF statement and do whatever YOU think is the right thing.

9. ## Re: XIRR and stock/mutual fund purchases (Probably

<hr>"i realize al of that, but i feel that computers should do the right thing."<hr>

By convention, Howard, financial transactions occur on a day, not at a point in time. Your bank, for example, will not pay you additional interest if you make a deposit (even a very large one) at 10:00 am rather than 2:00 pm (or whatever they define to be the last second before the end of that day) nor will they charge additional interest because of a drawdown of a loan at a different time of day. In the situation you are describing the entry price is different than the closing, or valuation, price on the day of entry. Because of that convention, Excel (and your stockbroker and your mutual fund company) considers the two transactions to have occured in the same instant. If an investment creates a gain or loss in no elapsed time, then the annualized return is infinite - either positive or negative. In this case, Excel is doing the right thing - it is telling you that you have an infinite return.

It is possible (but not likely) that the convention for financial calculations will start to address a time-step shorter than one day - but I do not believe it to be likely.

10. ## Re: XIRR and stock/mutual fund purchases (Probably

This is a simple algorithmic implementation issue.
Usually very trivial to code if the programmer understood the algorithm.

11. ## Re: XIRR and stock/mutual fund purchases (Probably

mutual funds are different.
u do not know the price until the end of the day, or an hour later (if you buy hourly priced funds such as Fidelity Selects),

The underlying algorithm for XIRR has no idea about the type of asset, it just cares about the cash flow.

12. ## Re: XIRR and stock/mutual fund purchases (Probably

It sounds like since you want calculations within days, that the XIRR is not an appropriate function. You might have to create your own function to do what you need...

Steve

13. ## Re: XIRR and stock/mutual fund purchases (Probably

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.

XIRR handles these cases if the value remains unchanged or the value rises, but not if the value rises.
So it appears that the problem is with the lower value, not that it is on the same day.

14. ## Re: XIRR and stock/mutual fund purchases (Probably

In XL97 I get a #NUM error whether it is larger or smaller in magnititude.

Only when the first and second are equal in magnitude do I get a value. This fits with Dean's <!profile=dcardno>dcardno<!/profile> hypothesis in <post#=510618>post 510618</post#> that it ignores the dates when the sum = 0.

Steve

15. ## Re: XIRR and stock/mutual fund purchases (Probably

XL 2003 is alleged to have "fixed" some of the algorithm/implementation issues from earlier Excel version.

Apparently, XIRR is one the functions they changed.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•