I am building a spreadsheet with the result of stock purchases using average down strategy. Here is the definition of averaging down for those of you who don't know:
Averaging down is an investing strategy that involves a stock owner purchasing additional shares of a previously initiated investment after the price has dropped. The result of this second purchase is a decrease in the average price at which the investor purchased the stock.
I am building a simulator that will buy stock shares, then if the price goes down it will buy more shares at specific price drop percentages. Each strategy in the simulator have different parameters, and my issue is finding the average bought price when the stock has gone down.
Let me explain the parameters for the strategy and give you an example:
Base Order (BO): This is the initial amount of $ used to purchase the stock
Extra Order (EO): This is the extra investment in the stock that will be made at specific price down intervals
Maximum extra orders (MEO): the maximum extra orders I am willing to place
Extra order scale (EOS): This is a factor that each extra order will be multiplied by
Price deviation (PD): This is the price down % at which to buy more stock
Price deviation step scale (PDSS): This is a factor that each price step deviation will be multiplied by
Let me give you an example of the behavior of a particular strategy
BO: $100
EO: $200
MEO: 3
EOS: 2
PD: %1
PDSS: 1.5
With those settings, I would buy an initial order of 100 dollars worth of stock at a certain price (specific price doesn't matter actually). If the stock goes down, I will be buying extra orders in specific intervals. The first one will be when the price drops 1% (the PD), for a volume of 200 dollars (EO) more worth of stock. If the price keeps going down, I will buy more when the price has gone down 2.5% from the initial order (1%+(1%*1.5)) and I will buy 400 dollars worth of shares (200 * 2). Lastly, if the price keeps on going down, once it reaches a 4.75% price drop (1%+(1%*1.5)+(2.5%*1.5), I will buy 800 dollars (400 * 2) worth of shares.
The question is: What formula will give me the new average price of shares bought when all extra orders are completed?
What I have so far is the formula to calculate the total amount of dollars invested and the total price deviation from the initial price. Both are calculated using a geometric progression like so (sorry I dont know how to write formulas here):
Sum of geometric progression formula
With this formula I can calculate the total money invested (in this case 1500 dollars) and the total price deviation at the last step (4.5% in this case), but to calculate the new average price, I need the amount of shares bought at each step, and that is what is driving me crazy. I managed to calculate it in a spreadsheet with each step, but I can't figure out what's the formula that can calculate it given the parameters. Here is my spreadsheet:
I can see that considering an initial price of 100 dollars (or 100% of the price), I would have bought 15.52 shares, with a new average price of 96.64 dollars (or 96.64% of the initial price, I achieved a -3.36% drop in my average price). That 3.36% drop is what I would like to calculate in a formula out of the parameters, without having to write each step.