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?
[TABLE="width: 1480"]
<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Goals:[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Months to Reach Goal[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Long-Term Goal [/TD]
[TD]$ 1,000,000.00[/TD]
[TD] [/TD]
[TD]60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Trading Parameters:[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Trades Per Month [/TD]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Now[/TD]
[TD] [/TD]
[TD]After 1 Mo.[/TD]
[TD] [/TD]
[TD]After 12 Mo.[/TD]
[TD] [/TD]
[TD]After 24 Mo.[/TD]
[TD] [/TD]
[TD]After 36 Mo.[/TD]
[TD] [/TD]
[TD]After 48 Mo.[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Pct. of Account Put into Each Trade [/TD]
[TD]2.00%[/TD]
[TD]=[/TD]
[TD]$2,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Performance Needed to Meet Goal:[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Average Dollar Gain Per Trade [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Average Percent Gain Per Trade [/TD]
[TD]0.38%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 3"]Account Value [/TD]
[TD]$ 100,000.00[/TD]
[TD] [/TD]
[TD]$ 100,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
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?
[TABLE="width: 1480"]
<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Goals:[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Months to Reach Goal[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Long-Term Goal [/TD]
[TD]$ 1,000,000.00[/TD]
[TD] [/TD]
[TD]60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Trading Parameters:[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Trades Per Month [/TD]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Now[/TD]
[TD] [/TD]
[TD]After 1 Mo.[/TD]
[TD] [/TD]
[TD]After 12 Mo.[/TD]
[TD] [/TD]
[TD]After 24 Mo.[/TD]
[TD] [/TD]
[TD]After 36 Mo.[/TD]
[TD] [/TD]
[TD]After 48 Mo.[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Pct. of Account Put into Each Trade [/TD]
[TD]2.00%[/TD]
[TD]=[/TD]
[TD]$2,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Performance Needed to Meet Goal:[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Average Dollar Gain Per Trade [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 2"]Average Percent Gain Per Trade [/TD]
[TD]0.38%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 3"]Account Value [/TD]
[TD]$ 100,000.00[/TD]
[TD] [/TD]
[TD]$ 100,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]