I’ve been having a devil of a time calculating IRR for my short option investments. There’s got to be a simple solution, but nothing I’ve tried seems to work. I’m using Excel, and even the simplest case of two cash flows and two dates is not working.
Here’s a recent example (normalized to 1 option contract). Sold put options for 90 cents. Current value is 16 cents. Two cash flows, first one is $89.32, second one is -$16.00. What is the internal rate of return?
Maybe XIRR() does not like empty cells.
The Captain
I’ve tried it ALL ways, and in all orders. With or without empty cells, and with or without the zero cells. Nothing works.
These are literally the simplest case of the IRR calculations because only two cash flows, one in, one out. I’ve battled it for decades now to no avail. Instead of using IRR, as I do for ALL other trades, I use a simple percentage return for short option trades (which, of course, is hardly useful as it doesn’t account for time at all).
Hi @MarkR,
You are using transaction amounts that make the XIRR value exceedingly high. The algo has a hard time guessing interval amounts to fit.
Here is a goog sheet showing how to use XIRR.
It starts with an initial balance. Then cash flows in (deposits) and cash flows out (withdrawals) are listed. At the bottom, the current balance is listed as a withdrawal (negative number)
NOTE: There are a couple hundred blank lines. They have no affect.
XIRR is Balance to Balance with Transactions between them.
It is not for a single transaction.
Does that help you?
Gene
All holdings and some statistics on my Fool profile page
https://discussion.fool.com/u/gdett2/activity (Click Expand)
I played with the numbers and your response is the right one.
The Captain
I used to think that was the problem. But, it also can’t calculate normal XIRR values. Here’s an example.
So far, the only thing I can guess that is causing the issue is that the “purchase” happens after the “sale” in time. But why would that matter for an IRR calculation? All it should care about is the relative cash flows and the time each one occurs. That’s why. I started adding zero cash flows at the end and start, but that doesn’t help (probably because it strips out zero cash flows before calculating).
Here’s an even better example of a plain old moderate IRR (maybe 30% or so) but Excel still can’t calculate it.
Hi @MarkR,
I get -72.16% on the second one.
I do note that your formula does not have a guess value. I always use .01 for that.
Does that help you?
Gene
All holdings and some statistics on my Fool profile page
https://discussion.fool.com/u/gdett2/activity (Click Expand)
How? There are only 2 cash flows. First I sold the options (“sell to open”) for $10,996 on 7/22/21, and then I purchased them (“buy to close”) for $6,153 on 10/25/22. How can a net gain of $4k+ result in a negative IRR? It’s simply not possible.
I’ve tried guesses. I’ve tried a lot of things. Nothing I’ve tried works. Here’s with a guess -
Google docs spreadsheet comes up with about 37% IRR which is correct. Why can’t Excel do it?
Hi @MarkR,
I used your example of:
9/15/23 6069.55
2/20/24 -3505
You started with 6,069.55 on Sep 15 and the balance dropped to 3,505 on Feb 20 th with No Cash Flows.
Please re-read this:
You only have Transactions!
You have no Beginning Balance and no Ending Balance.
You are trying to adjust a fine clock mechanism using a sledge hammer.
XIRR is not the correct tool for this calculation.
Gene
All holdings and some statistics on my Fool profile page
https://discussion.fool.com/u/gdett2/activity (Click Expand)
For first cash flow positive, second cash flow negative, and first cash flow greater in magnitude (ignoring sign), the calculated irr is negative. For the above example, there is no irr > 0 that satisfies the equation for NPV of cash flows = 0 (which is how irr is calculated).
That said, I would ask if the two cash flows given above correctly represent the economics of a short transaction. For example, a short option is a liability, so when you open a short option position, you incur a liability, this liability could be represented on an income statement as a negative cash flow and then when the option position is closed this liability is reversed.
That’s not what this says!
This says that I sold it on 9/15/23 for 6069.55 and bought it on 2/20/24 for 3505. That’s a profit of 2564!
They literally represent the actual cash flows in my account due to the transaction. A short option has two parts, first a deposit of the premium into the account (an actual cash flow), and second a liability of sorts (as specified in the contract, but with no cash flow at all). Then, one of two things can happen:
This could be done, but it doesn’t really represent reality because those cash flows never happen. The money accounting for the liability (let’s say number of options times strike price) remains in the account throughout, and earns interest at the usual rate. Not only that, but I can use that money for other things pretty much at will. I could buy treasury bills with that money to get a higher rate than using a sweep fund. I could even invest it into an S&P500 ETF, at least to a large extent.
How would it make sense to add a negative cash flow of $100,000 (say $200 strike price times 500 shares) at the start, and then a positive cash flow at the end of $100,000?
[[quote=“MarkR, post:6, topic:102030”]
I used to think that was the problem. But, it also can’t calculate normal XIRR values. Here’s an example.
[/quote]
Using LibreOffice on Mac (no guess)
The Captain
Unfortunately the whole thing is absurd. How can investing $3505 and ending up with $6095 result in a negative IRR?
So I did an experiment in Google docs. I tried all 4 possibilities of 2 cash flows, ordered by dates, reverse ordered by dates, negative and positive cases. They all result in a negative IRR. Weird.
Since I am experimenting anyway, I went back to some older trades, but those are not “short” trades, they are regular long trades. They are also trades of short duration, and very similar except that the purchase happened before the sale. And sure enough Excel has no problem at all calculating IRR, even when there are only 2 cash flows, and even when IRR is large. Here’s an example -
The other thing that needs to be understood is that a negative cash flow is literally that, a cash flow OUT of the account, and a positive cash flow is a cash flow INTO the account.
Check the dates!
The Captain
The dates are absolutely correct. They are the exact dates that the cash flows occurred in real life.
Would the sign be correct for a long position?
The Captain
Yes. See the example I posted just above.
Recognizing my first statement is important, it helps explain, using the formula for irr using NPV = 0, why the cash flows you are using give a nonsensical (negative) answer.
You write,
but I just explained why this gives a negative irr so in fact that case is not well defined, at least not for irr, unless perhaps you think negative irr is appropriate economics for your example.
Regarding the bolded “there is no irr > 0”, here’s a universal truth: if you search for something that doesn’t exist, you 100% won’t find it. Having a nonsensical irr is an indication that something is off. The formula for irr is a known quantity, what remains less obvious is how we use it. My suggestion is then to modify your search for irr by modifying how you are using the irr formula (not a bad suggestion, and to be clear, I am not speaking to any specific Excel built in formulas).
Regarding a modified search, concerning the liability,
This is a very real liability that is a very real part of the economics of the trade. Because that liability is real, your broker will require you to post very real collateral. You can be sure that the broker is accounting for this liability. And if your short option is exercised, your liability will be settled by a flow of assets from your account to the counterparty account.
How are you representing this liability in your irr calculation?
