I need help with some stock market math

[DH888]

Here's a link to the table I'm working on.
https://drive.google.com/open?id=11xxNJBIXnwgfm8E5Q27U5wF8xbMMPeB_

note:
I'm expecting either a 61.8% or a 78.6% Fibonacci retracement so I'm using those percentages.

I'm trying to calculate the return of a strategy where you short the coming bear market with margin for the first 40% of the decline, and then go marginless for the remainder of the decline.

Then in the new Bull Market Going long with margin for the first 150% or 300% then going marginless for the remaining 111.8% or 167.3%

The problem I'm having is when I increase the 300% to 400% my final number should get bigger because it means I'll be using margin for an extra 100% increase. so clearly the value should be higher. I'm pretty sure I know what the problem is but I don't know how to do the formula.

When I'm increasing the 300 to 400 it decreases the amount I'm multiplying the second part of the move by.
1st part - ($10,000 plus your interest from the bear) times 3 with margin, times 300%
2nd part - (Initial investment+interest earned in part 1 above)*(467.3-300) (which = 167.3)
so ($10,000+int)*(167.3)

But when I increase the 300 to 400 I'm saying
1st part - ($10,000 plus your interest from the bear) times 3 with margin, times 400%
2nd part - (Initial investment+interest earned in part 1 above)*(467.3-400) (which = 67.3)
so ($10,000+int)*(67.3)

Which isn't right in either case. what I need to do, is use a compounding formula to take a daily average from the average I'm using/expecting. that way the change isn't so abrupt and using an inaccurate multiplier.

I provided a link above. Thanks to anyone that even tries to understand and solve this.

Thanks for your time. It's generous of you to help someone you'll never meet. It is appreciated.

 

[DH888]

This link gets you the actual worksheet which is far more useful.
https://drive.google.com/open?id=1q5CYlH4Uhy0wYyOfiteJn1H2ciqw2h7H

 

[DH888]

I added a second sheet to the workbook that walks you through the logic of what I'm trying to do step by step. Kind of like showing your work in school.
It describes and shows specifically what I'm looking for.
In words and results at each step.

It's simple really.
I'm just trying to figure out how much money I could make per $10,000 if I short the coming bear market and then go long the next Bull Market using margin in both directions for the initial part of the move.
40% on the decline in both cases and 150% on the upside in the case of a 61.8% decline OR
300% on the upside in the case of a 78.6% decline.

I don't know the RATES of decline per year and they're not important. The end decline is all I'm interested in. In this case 78.6%. So you can average it over the period.

Part 1 - short the coming bear market, I'm using the 78.6% fibonacci retracement. (that means I'm expecting the market to drop by 78.6%) Using 3 times margin for the 1st 40% of the decline (margin means you can trade 3 times your capital. In this case $10,000 (so times $30,000)),
that leaves 38.6% still to go to get to a 78.6% decline.

At 40% I'm going to stop using margin and ride it the rest of the way down with just the initial 10k plus the interest accrued to that point. NO MARGIN. I'll also be increasing my position size based on the amount of margin that has been released by the falling stock price.

I can't do it based on time since it's an unknown, so I'm going to use 10% increments.
So at 10% I'll increase my position to include
1. The initial 10k
2. the $3,000 earned on the first 10% ($30,000x10%=$3,000)

The next 10% will be funded like so
1. The $13,000 principle I now have times 3 because of margin.
2. So it will be $39,000 x10%
3. $42.900 etc

Here I built a table.

10% increments $10,000.00
Break the decline into 8 parts.

.............$30,000.00| ...Margin|......Principle...|Re-Margined| @10% Increase
__________________________________________________________
10.00% $33,000.00 $20,000.00 $13,000.00 $39,000.00 $3,000.00
20.00% $42,900.00 $26,000.00 $16,900.00 $50,700.00 $3,900.00
30.00% $55,770.00 $33,800.00 $21,970.00 $65,910.00 $5,070.00
40.00% $72,501.00 $43,940.00 $28,561.00 $85,683.00 $6,591.00
50.00% $31,417.10 -----------------------------------------$2,856.10
60.00% $34,558.81 -----------------------------------------$3,141.71
70.00% $38,014.69 -----------------------------------------$3,455.88
78.60% $41,283.95 -----------------------------------------$3,269.26

3 times margin stops at 40%. Growth happens on only on the principle after that.
but compounding continues because I'll keep increasing my position size. We'll say at the 10% levels.

Turns out my final number because of compounding is much higher. $41,283.95 instead of $30,000 ish
No surprise obviously

I just don't know how to do this in a formula

 

[DH888]

Or how about this.
I need a formula that will compound every 10%. Starting balance is $10,000
Up to a maximum of 78.6%

I also asked this question on Excelforum