Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XIRR and stock/mutual fund purchases (Probably

    22 Aug 2005 -8000.00

    22 Aug 2005 7999.99

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: XIRR and stock/mutual fund purchases (Probably

    Howard

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

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #15
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 LastLast

Posting Permissions

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