Return Formula

[RMussel007]

I need help with a formula to calculate returns of stocks over a given time
period. I need something that will be equivalent to the Average Annual Return.
When I use the XIRR function, if I lose money on the stock, it won't give me
the negative Internal Rate of Return. I'm just trying to get a number than
will compare to various funds, stock, and indices performance on an average
annual return basis.

Can anyone help? I'm sure this is easy but it's giving me fits.

 

[Ron Rosenfeld]

I need help with a formula to calculate returns of stocks over a given time
period. I need something that will be equivalent to the Average Annual Return.
When I use the XIRR function, if I lose money on the stock, it won't give me
the negative Internal Rate of Return. I'm just trying to get a number than
will compare to various funds, stock, and indices performance on an average
annual return basis.

Can anyone help? I'm sure this is easy but it's giving me fits.

XIRR works OK for me in that situation. Possibly you are not entering the
values correctly.

If you would post some sample data along with the actual formula you are using;
and the actual and expected results, a more helpful reply might be possible.


--ron

 

[RMussel007]

Ron,
Thanks for the reply. I am using the XIRR function in Excel but I get the #NUM
value when I lose money on an investment.

Lets say I purchase a stock on January 1 for $5,000. It's entered as -5000.
Let's say I sell the stock for $4,000 on July 1. That is entered as 4000. It
seems as though the IRR should be very close to negative 40%. However in my
spreadsheet I only get the #NUM value. All of the IRR values on stocks where
I make money seem to be accurate.

Randy

 

[Ron Rosenfeld]

Ron,
Thanks for the reply. I am using the XIRR function in Excel but I get the #NUM
value when I lose money on an investment.

Lets say I purchase a stock on January 1 for $5,000. It's entered as -5000.
Let's say I sell the stock for $4,000 on July 1. That is entered as 4000. It
seems as though the IRR should be very close to negative 40%. However in my
spreadsheet I only get the #NUM value. All of the IRR values on stocks where
I make money seem to be accurate.

Randy

Well, you did not post the actual formula you used, so again it's hard to tell
where you went wrong.

When I use your data using the XIRR function, I get -0.360784202 which is
pretty close to your negative 40%.

I'll make a guess that you are entering the arguments incorrectly in the XIRR
function -- probably putting in the dates where the values belong, and vice
versa.


--ron

 

[RMussel007]

Ron,
Again thanks for replying. Now here is a real oddity. This has been giving me
a problem for a few weeks. Today I went back into the spreadsheet and gave it
another try. Guess what?....yeah, it worked. I didn't change a things. That
is really weird.

 

[Ron Rosenfeld]

Ron,
Again thanks for replying. Now here is a real oddity. This has been giving me
a problem for a few weeks. Today I went back into the spreadsheet and gave it
another try. Guess what?....yeah, it worked. I didn't change a things. That
is really weird.

Well, there are several reasons for XIRR to return the #NUM! error:

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

If any number in dates precedes the starting date, XIRR returns the #NUM! error
value.

If values and dates contain a different number of values, XIRR returns the
#NUM! error value.

So likely one of those parameters changed.

But I'm glad it's working now.


--ron