CAGR for short sales using XIRR

[oukid]

I am trying to calculate the annual growth rate for a short sale. I've
entered the first amount as a positive number (because it was cash coming in)
and the second amount as a negative number (because it was cash going out).
When I use XIRR I get a negative percentage as the annualized return.

As a simple example, if I enter 110 on the first date and -100 a year later
XIRR gives me negative 9.1% as the annualized return. Since I have 10 more
at the end of the year, it seems that my return must be positive.

Am I misinterpreting this result or what?

Thanks for your help.

 

[Fred Smith]

But you told XIRR that you invested $110 and got back $100. So you lost
money. That's what XIRR is telling you.

Remember that to calculate investment returns, only the cash flow is
important. The transactions in between have no effect on the return.
Normally when you do a short sale, you need to put up 50% margin. Is that
what you did in this case? If so, you invested $55 (cash out of your
pocket), and received back $100. That's the transaction detail you need to
feed to XIRR.

Regards,
Fred.

 

[oukid]

I thought that my first entry, being positive, indicated cash going into my
account (proceeds of the short sale--actually a short call covered by stock).


The second entry was negative indicating that I had bought back the call
(cash going out).

The account is an IRA so margin is not involved. Except for the stock
purchase (which I am ignoring for the time being--I want only the CAGR for
selling the call and buying it back), so as far as I know $55 does not enter
into the problem.

I did not invest $110 and get back $100 thus losing $10. I sold for $110
and bought back for $100 and kept $10. Hopefully, I have explained the
example a little more clearly.

 

[Fred Smith]

XIRR (nor any CAGR calculation) cannot do what you are asking it to do. I
know you "did not invest $110 and get back $100 thus losing $10", but that's
what you told XIRR, which is why it gave you the results it did.

XIRR, like all return on investment calculations, is designed to calculate
return on investment. You're trying to calculate the return of a
transaction. But the $110 or the $100 you are using in your example is not
what you invested.

The most common application of XIRR is to calculate the return of the
account (the IRA in your eample). You feed XIRR the cash going in, the cash
coming out and the final balance, and it will happily calculate the CAGR. As
I stated, it's only the cash flow that determines return on investment. The
transactions within the account are immaterial.

If, for whatever reason, you still want to calculate the return on your
transaction, do it this way:
1. Set up (or simulate) an account for this single transaction.
2. Ask your broker what you need to have in an account to do a $110 short
sale. He'll tell you you need some amount of cash to cover the margin on the
sale. Let's say the cash required is $55. So you make this deposit.
3. Sell short for $110.
4. Cover the short at $100.
5. You now have $65 in the account.
6. Tell XIRR you invested $55, and got back $65. It will calculate the CAGR
for you.

Regards,
Fred.