Calculating an accelerating rate of growth as a formula

Question

Suppose I start with a $1,000 trading account.

During each 30 day period my account earns $1,000 in net profit from a trading strategy using one contract per trade.

The max amount lost across any series of consecutive days during the 30 day period averages to 25% of the total net profit, so in this case $250.

The maximum I am willing to risk seeing my account dip is by 25% across all consecutive losing days.

My account would now be at $2000 after 30 days, increasing the tolerable consecutive loss threshold to $500.

If this rate of growth continues steadily at $1000/month growth, then by the end of 90 days I will have $4,000 and a loss buffer of $1,000.

At this point, I want to add an additional contract to my trades. Now each 30 days returns $2,000.

Eventually the account will reach $8,000 of which $2,000 is 25%. At this point I will increase the number of contracts per trade to 3 which means earning $3,000 per 30 days.

How would I express this growth in a spreadsheet (Excel, Google Sheets) equation if I wanted to project which dates in the future would result in a specific number of contracts, account balance, and trading profit per month assuming everything holds steady?

Answer 1

I used Mathematica to find a function for the account balance sequence as the OP seems to describe it.

I will have to come back to this a bit later (tomorrow maybe) to implement it in Excel.

It's not a straightforward function - and obviously not a simple compounding formula. Just something to produce the OP's figures, to see how it goes.

Assuming the account balance goes like this, e.g. $8000 in the 6th month.

s = 1000;

sequence = {
  (x = 1 s) + x, x + 2 x, x + 3 x,
  (x = 2 s) + x, x + 2 x, x + 3 x,
  (x = 3 s) + x, x + 2 x, x + 3 x,
  (x = 4 s) + x, x + 2 x, x + 3 x,
  (x = 5 s) + x, x + 2 x, x + 3 x,
  (x = 6 s) + x, x + 2 x, x + 3 x,
  (x = 7 s) + x, x + 2 x, x + 3 x}

{2000, 3000, 4000, 4000, 6000, 8000, 6000, 9000, 12000, 8000, 12000,
 16000, 10000, 15000, 20000, 12000, 18000, 24000, 14000, 21000, 28000}

FindSequenceFunction[sequence, n]

yields

f = DifferenceRoot[Function[{y, n},
   {-s ((-1 + n)*(2 + n)*(-11 + 9 n)) + (2 - 3 n + 3 n^2) y[n] +
      (-1 - 6 n + 3 n^2) y[1 + n] + (8 - 9 n + 3 n^2) y[2 + n] == 0,
    y[1] == 2 s, y[2] == 3 s}]]

e.g. f[6] outputs 8000

The function should be fairly straightforward to implement in Excel. Perhaps not what the OP was expecting though.

Array[f, 21] == sequence

True

ListPlot[Array[f, 21]]

Plotting out to 210 months

ListPlot[Array[f, 210]]