XIRR Function

[W]

Is XIRR known to give a bad result when you have only two dates and values?

I have an investment that costs $29 on 1/29/2011 and generates $30.25 on
3/20/2011, so profit is $1.25. XIRR gives 36.1%, whereas a simple
interest calculation gives 31.5%. Simple interest was calculated as:

( $1.25 / $29 ) / (( End-Date - Start-Date ) / 365 )

 

[joeu2004]

Is XIRR known to give a bad result
Yes.

when you have only two dates and values?

Not specifically.

I have an investment that costs $29 on 1/29/2011 and
generates $30.25 on 3/20/2011, so profit is $1.25.
XIRR gives 36.1%, whereas a simple interest calculation
gives 31.5%.  Simple interest was calculated as:
( $1.25 / $29 ) /  (( End-Date - Start-Date ) / 365 )

And what does that have to do with the price of tea in China?

XIRR returns a compounded annual rate. The daily rate is
(30.25/29)^(1/("3/20/2011"-"1/29/2011"))-1.

The annual rate is the daily rate (dr) compounded over 365 days, to
wit: (1+dr)^365. Or simply:
(30.25/29)^(365/("3/20/2011"-"1/29/2011"))-1.

That formula and XIRR return essentially the same IRR within about
3E-9. So the XIRR function is correct in this case.

And since you got the correct result with XIRR, it appears that you
used XIRR correctly, using -29 for the first cash flow and 30.25 for
the second cash flow.

Your formula computes simple interest, as you say. Simple and
compounded rates are not expected to be the same.