I'm hoping this is the correct forum as I wasn't sure if algebra or business math was the best place to post.

I have a spreadsheet that I'm using to backtest an investment model that was normally just long, but recently I added a mode where it could go short. In the spreadsheet there is a column that calculates buy/hold returns, and then I have another column that compares the model's yearly performance to buy/hold. To be accurate at the end of the year you need to calculate your gain (and adjust your basis) even if you have not sold your long position (lets call it an unrealized gain/loss). So as an example let's say we have the following stock prices on these dates.

1/1/10 100

12/31/10 80 (unrealized)

12/31/11 104 (sell)

On 12/31/10 we have a -20% loss (unrealized) and new basis of 80, and from 12/31/10 - 12/31/11 we have a 30% gain. If we had a portfolio of $10,000 we would have 10,000*.8=8000 on 12/31/10, and we would have 8,000*1.3%=10,400 on 12/31/11.

Now if you were buy/hold you would start with 100 and end with 104 for an 4% gain, and the portfolio would be 10,000*1.04=10,400 which is exactly the same as the previous example. You can put in as many unrealized buy/sells in this example as you want, and both portfolios will be exactly the same in the end.

Now lets do the exact same example but assume we are going short. This means once you buy you need the price to go down to profit (exactly the opposite of being long). So for this case from 1/1/10-12/31/10 we have a 20% gain (20/100) and new 80 basis, and from 12/31/10-12/31/11 we have a 30% loss (24/80). If we multiply our portfolio buy these gains (like in the long example) we get $10,000*1.2=12,000 and then 12,000*.7=8400.

But the actual portfolio value is a 4% loss (4/100) and would be $10,000*.96=9600. In the short case one portfolio that took the intermediate unrealized gain is 8400 and the other is 9600 which definitely aren't the same like they are in the long case.

Now I realize the whole percentage loss vs gain thing when you have a loss you need a higher percentage gain to get back to even. Like if you loss 20% you need a 25% gain to get back to even, but I don't believe that is the issue here. The issue is that when you go short you can get leverage after the unrealized gain/loss. In this case when we are short we initially have a 20% gain, so when I buy back in at the lower stock price of 80 I have $12,000 to buy stock vs $10,000. This can't happen when you are long. So it's this leverage (in this case 1.2X) that makes the 2nd loss much worse than it really is.

The question is how to fix this and make the math work out so both portfolios match in the short case. The loss needs to be -20% (vs -30%) to get the portfolios to match (12,000*.80=9600), but I don't see the formula to make it work. I've tried using the leverage variable 1.2 (which seems like it has to play in somewhere) to multiply the basis, the loss, etc but again nothing seems to work.

jcg