I need ideas/suggestions on how I could calculate how to recalculate a portfolio with a minimum purchase amount requirement.
I have this portfolio:
I wanted to rebalance it then it would be fairly straightforward by simply calculating in in Column F '=$E$15*A2'.
My issue is that there is a minimum difference requirement of 0.001 so because of that rows 4,6,11,12,13 are not acceptable.
How could I recalculate the Ideal Values for each row to respect the Target % considering the minimum difference requirement to be at least 0.001 for all rows?
I cant figure out how to do this.
If its possible I would prefer it in formulas but VBA is fine too.
Thanks
I have this portfolio:
| Rebalancing1.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Target % | Holding | Current Price | Current % | Current Value | Ideal Value | Difference | ||
| 2 | 40.00% | 1 | 0.05 | 36.92% | 0.050000000 | 0.054169939 | 0.004169939 | ||
| 3 | 20.00% | 0.9 | 0.02245264 | 14.92% | 0.020207376 | 0.027084969 | 0.006877593 | ||
| 4 | 10.00% | 500 | 0.00002776 | 10.25% | 0.013880000 | 0.013542485 | -0.000337515 | ||
| 5 | 10.00% | 2 | 0.00753386 | 11.13% | 0.015067720 | 0.013542485 | -0.001525235 | ||
| 6 | 1.00% | 1.90108348 | 0.00048866 | 0.69% | 0.000928983 | 0.001354248 | 0.000425265 | ||
| 7 | 4.00% | 300 | 0.00002547 | 5.64% | 0.007641000 | 0.005416994 | -0.002224006 | ||
| 8 | 4.00% | 3000 | 0.00000215 | 4.76% | 0.006450000 | 0.005416994 | -0.001033006 | ||
| 9 | 3.00% | 0.8 | 0.00836471 | 4.94% | 0.006691768 | 0.004062745 | -0.002629023 | ||
| 10 | 2.00% | 50 | 0.000089 | 3.29% | 0.004450000 | 0.002708497 | -0.001741503 | ||
| 11 | 2.00% | 500 | 0.00000717 | 2.65% | 0.003585000 | 0.002708497 | -0.000876503 | ||
| 12 | 2.00% | 900 | 0.00000347 | 2.31% | 0.003123000 | 0.002708497 | -0.000414503 | ||
| 13 | 2.00% | 1700 | 0.000002 | 2.51% | 0.003400000 | 0.002708497 | -0.000691503 | ||
| 14 | |||||||||
| 15 | 100.00% | 100.00% | 0.135424847 | 1.135424847 | 0.000000000 | ||||
Sheet4 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:G13,A15,D15:G15 | D2 | =E2/$E$15 |
I wanted to rebalance it then it would be fairly straightforward by simply calculating in in Column F '=$E$15*A2'.
My issue is that there is a minimum difference requirement of 0.001 so because of that rows 4,6,11,12,13 are not acceptable.
How could I recalculate the Ideal Values for each row to respect the Target % considering the minimum difference requirement to be at least 0.001 for all rows?
I cant figure out how to do this.
If its possible I would prefer it in formulas but VBA is fine too.
Thanks
