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: $ 745.65

Posts: 3625
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: $ 1626.30

Posts: 7990
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~

Sat Feb 06, 2010 3:25 am

coaster Senior Advisor

Cash: $ 1626.30

Posts: 7990
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~

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

zadvfb First Time Poster

Cash: $ 0.20

Posts: 1
Joined: 26 Apr 2018

This blog can help many sellers for calculation is their all ongoing and outgoing amount for clear and maintain all the accounts details. Thatâ€™s why I use the software which is bestessays reviews for check my all the accounts properly.[/url]