| How to calculate ongoing short sale return |
|
|
|
|
|
MarkJC
New Member
Cash: $ 0.65
Posts: 3
Joined: 04 Feb 2010
|
| How to calculate ongoing short sale return |
 |
|
Hi guys, i'm having trouble calculating the ongoing return in Excel as if I was holding a short position in a stock and i'm really stumped at what i'm doing wrong.
Since it will make no sense explaining in words, I attached a picture of my excel sheet to demonstrate what is not working. To summarize, if I have a short position, I can take the final share price and the initial share price and calculate the return I would see if I had held this position short. This works.
But I should also be able to use the daily compounded returns to come up with that same number, and here is where i'm having the problem. When I run the calculations, doing a product of the daily returns to get overall return, i'm getting a totally different number. Would be really grateful if someone can find where i'm goofing up!
|
Thu Feb 04, 2010 11:17 pm |
|
|
oldguy
Senior Member
Cash: $ 308.30
Posts: 1476
Joined: 21 May 2006
Location: arizona
|
Yes, going from 16 to 15.1 is a 0.9/16, or 5.625% gain on a 'short' position.
Your other calculations are not refernced back to 16, they are referenced to the previous day. You want the gain/loss referenced to your initial position, ie 16.
|
|
Fri Feb 05, 2010 12:25 am |
|
|
MarkJC
New Member
Cash: $ 0.65
Posts: 3
Joined: 04 Feb 2010
|
But I thought I should be able to take each day's returns and manipulate them to arrive at the same answer as if I was just looking at the first and last day's share price. This works for long positions. For instance, you can test it with the excel data above, and both calculations will yield the same return, whether it's using only the first and last share price or the daily returns:
15.1/16 * 100= -5.625% loss.
Or take the daily returns of 0.9375, 1.033333, and 0.974194 and you get the same answer:
1 - (0.9375*1.033333*0.974194) = -5.625% loss
I attempted to use that same method to calculate the return from shorting, but negating the returns and multiplying them like before no longer works. How can I calculate the overall return from shorting using only the daily returns instead of the first and last day's share price?
|
|
Fri Feb 05, 2010 11:49 pm |
|
|
coaster
Senior Advisor
Cash: $ 1356.80
Posts: 6681
Joined: 11 Oct 2005
Location: Wisconsin
|
You do it the same way as with a long position.
LONG: A stock going from $15 to $16 is $1 gain on a $15 investment. Your current net value is gain + investment or $15 + $1 = $16 for a gain of 16/15-1 = 6.7%
SHORT: A stock going from $15 to $14 is $1 gain on a $15 investment. Your current net value is same: gain + investment or $15 + $1, or 16/15-1 = 6.7%
Always calculate current gain as if you liquidate now (mark-to-market) divided by invested capital.
Feels good when you're right, eh? 
~Tim~
Eye Candy : Why Whimsy
|
|
Sat Feb 06, 2010 3:25 am |
|
|
coaster
Senior Advisor
Cash: $ 1356.80
Posts: 6681
Joined: 11 Oct 2005
Location: Wisconsin
|
|
|
|
Boy, it's no telling how the brain works. I'd been up not more than 15 minutes and this thread comes bombing into my mind shouting at me that I'm wrong. In the first place, the capital invested in my example ISN'T $16 because the shares are borrowed, not bought.
So what is the capital invested in a short sale? It could be looked at two ways: what it takes to cover (buy the shares back) at the end of the trade -- something you don't know right now; or secondly, the margin required to put the trade on in the first place.
If your return is calculated on what it takes to cover, then your gain is going to be what is the net gain to your account as the result of the trade. For doing it that way, if you want to know the gain on just this one position, then I'd do it as per the example in my last post, because it's a hypothetical gain anyway, since you didn't actually part with any funds to put the trade on, but you could also do it on an ongoing basis on the daily mark-to-market cost to cover. Say the stock drops from $15 to $14 on day one then the return at that point is (($14+$1)/$14)-1 or 7.1%.
But if you want to know what the gain is on invested capital, ie the gain on what it "cost" you in terms of "tying up" your funds in this one position, then you calculate it based on margin required. You can use initial margin, which is 50% per SEC regulations, or as set by your broker, whichever is higher, or you can use maintenance margin, which could be lower, and is often referred to as "buying power" since it's a measure of what capital you could bring to bear on putting on another trade given the current market value of your short position.
So, gain using initial margin, in my example, would be ((gain + investment)/(investment)) - 1; and using 50% of $15 as your "investment", your gain is ((1+7.50)/7.50)-1 = 13.3%. Whooee!! No wonder people like using margin. 
All this is a great exercise in showing how slippery a metric is "return". I have a system-backtesting program I wrote in Excel VBA which calculates return on investment, return on account, return on risk, return on margin, and maybe one or two others I can't remember right off the top.
~Tim~
Eye Candy : Why Whimsy
|
|
Sat Feb 06, 2010 3:42 pm |
|
|
MarkJC
New Member
Cash: $ 0.65
Posts: 3
Joined: 04 Feb 2010
|
thanks coaster, I didn't think to take margin requirements into account. But for the most conservative calculations, I will probably still assume that the full margin requirement is at 100%.
|
|
Sat Feb 06, 2010 6:39 pm |
|
|
|
