I am trying to write a sheet to allow me to play with certain variables to develop a plan that will get me from Account Value A to Account Value B.
Variables that can be changed:
- Initial Account Balance
- Number of Months until Goal Balance
- Number of Trades/mo during period
- Percentage of Account put into each trade
- Ending Account Balance
Assumptions:
- 0.01% (0.0001) interest/year gained on uninvested funds (brokers don't want uninvested funds)
- No withdrawals
After thinking on this, I guess it breaks down into 3 problems (formulas).
1) What Average return per trade will take Current Account Balance X to future Account Value Y in Z periods with a near zero interest rate?
2) What is the "return function" that would deliver this Average Return/Trade?
(What are the initial return, return growth rate and ending return/trade?)
3) What do the returns found in problem #2 represent as a percentage gain on the amount being invested in each trade (initially, investment growth rate as account grows and ending as well as average gain)?
I was able to guess my way to one partial answer using the FV function.
If a brokerage pays 0.01%/year (0.0001) on uninvested funds and for argument sake I use 600 trades as periods (60mo x 10/mo) with a starting balance of $100,000 the FV function shows it would require an average gain of $1445.55/trade to run the account to $1 million over 600 trades.
1,000,015.49 = FV(0.0001,600,-1445.55,-100000)
That tells me "on average" the dollar return needed is $1,445.55. However, I need to know the initial return needed and the rate at which the dollars gained/trade compounds.
Just as an example, perhaps the initial return is $250/trade and that compounds to a point where the final return/trade is $12500 giving an average return/trade of $1445.75 over the 600 trades. (Those are numbers just pulled out of the air for an example.)
So how do I write formulas to allow the variables to be changed (scenario planning)?
- Starting balance
- Desired Ending Balance
- Percent of Account put into each trade
- Number of months to reach goal Ending Balance
- Number of trades per month
And then calculate the:
- initial gain/trade required
- growth rate of that required gain/trade over the series of trades
- the average gain/trade this growth generates over the series of trades?
<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Variables that can be changed:
- Initial Account Balance
- Number of Months until Goal Balance
- Number of Trades/mo during period
- Percentage of Account put into each trade
- Ending Account Balance
Assumptions:
- 0.01% (0.0001) interest/year gained on uninvested funds (brokers don't want uninvested funds)
- No withdrawals
After thinking on this, I guess it breaks down into 3 problems (formulas).
1) What Average return per trade will take Current Account Balance X to future Account Value Y in Z periods with a near zero interest rate?
2) What is the "return function" that would deliver this Average Return/Trade?
(What are the initial return, return growth rate and ending return/trade?)
3) What do the returns found in problem #2 represent as a percentage gain on the amount being invested in each trade (initially, investment growth rate as account grows and ending as well as average gain)?
I was able to guess my way to one partial answer using the FV function.
If a brokerage pays 0.01%/year (0.0001) on uninvested funds and for argument sake I use 600 trades as periods (60mo x 10/mo) with a starting balance of $100,000 the FV function shows it would require an average gain of $1445.55/trade to run the account to $1 million over 600 trades.
1,000,015.49 = FV(0.0001,600,-1445.55,-100000)
That tells me "on average" the dollar return needed is $1,445.55. However, I need to know the initial return needed and the rate at which the dollars gained/trade compounds.
Just as an example, perhaps the initial return is $250/trade and that compounds to a point where the final return/trade is $12500 giving an average return/trade of $1445.75 over the 600 trades. (Those are numbers just pulled out of the air for an example.)
So how do I write formulas to allow the variables to be changed (scenario planning)?
- Starting balance
- Desired Ending Balance
- Percent of Account put into each trade
- Number of months to reach goal Ending Balance
- Number of trades per month
And then calculate the:
- initial gain/trade required
- growth rate of that required gain/trade over the series of trades
- the average gain/trade this growth generates over the series of trades?
| Goals: | Months to Reach Goal | ||||||||||||||
| Long-Term Goal | $ 1,000,000.00 | 60 | |||||||||||||
| Trading Parameters: | |||||||||||||||
| Trades Per Month | 10 | ||||||||||||||
| Now | After 1 Mo. | After 12 Mo. | After 24 Mo. | After 36 Mo. | After 48 Mo. | ||||||||||
| Pct. of Account Put into Each Trade | 2.00% | = | $2,000.00 | ||||||||||||
| Performance Needed to Meet Goal: | |||||||||||||||
| Average Dollar Gain Per Trade | |||||||||||||||
| Average Percent Gain Per Trade | 0.38% | ||||||||||||||
| Account Value | $ 100,000.00 | $ 100,000.00 | |||||||||||||
<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
