2

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?

0
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]]

enter image description here

Plotting out to 210 months

ListPlot[Array[f, 210]]

enter image description here

1
  • This is great. Didn't realize Mathematica could do this. It has made me realize that the threshold for increasing the contract count based on the monthly profit reaching 25% of the account size is inefficient. Contract count could conceivably grow at a much faster rate while still being within the 25% max consecutive loss limit. For instance, when reaching $8k account size, I could actually increase the number of contracts by 6 instead of just 1 and still be within 25% at 8 contracts. This suggests an exponential contract growth as well.
    – eComEvo
    Jul 13 at 4:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.