ram wrote: ↑Wed Mar 25, 2020 4:56 pm

Stock market starts going down. Every time the AA drifts by 5% ( 55:45 etc) the investor re-balances to the original [60:40 stocks:bonds] AA. However rebalancing stops when stable value fund [i.e., bonds] drops to 2 million ("Reserve amount" "R". (20 % of initial amount (T))

Questions:

- How low will the stock market need to go in percentage terms to reach the the 2 M mark for the stable value fund.
- Can there be an excel table where the ratio of stocks : bond can be changed, and
**"R" **can be changed to 20%, 25%, 30% etc of **"T"**

Assuming bonds don't rise or fall, I get about a 70% fall in stocks as the answer to question 1. The following is the little spreadsheet for question 2. It is complicated because a whole number of rebalances whenever the portfolio moves from 60:40 to 55:45 will not exactly make the bond value equal the reserve. It's necessary to rebalance at 55:45 N times and then rebalance one more time to match the reserve bond value.

Code: Select all

```
Row Col A Col B Formula
1 Initial portfolio value (T) 10,000
2 Stock allocation (S) 60%
3 Bond allocation (B) 40% =1-B2
4 Bond reserve of initial portfolio (R) 20%
5 Rebalance band 5%
6 Percent stocks fall to reach 55% (18.52%) =((B3*(B2-B5))/(1-B2+B5))/B2-1
7 Number of rebalances 5.88 =LN(B4/B3)/LN(1+B6*B2)
8 Whole number (N) 5 =INT(B7)
9 After 5 rebalances portfolio falls to 5,549 =B1*(1+B6*B2)^B8
10 Portfolio when bonds at reserve 5,000 =B1*B4/B3
11 Additional stock decrease (16.50%) =-(B9-B10)/(B9*B2)
12 Stocks fall overall (70.01%) =(1+B6)^B8*(1+B11)-1
```

And here is what the portfolio would look like after each of six rebalances:

Code: Select all

```
Stocks
Rebalances Stock Bond Total Fall
---------- ----- ----- ------ ------
0 6,000 4,000 10,000 18.52%
1 5,333 3,556 8,889 18.52% [1]
2 4,741 3,160 7,901 18.52%
3 4,214 2,809 7,023 18.52%
4 3,746 2,497 6,243 18.52%
5 3,329 2,220 5,549 16.50%
6 3,000 2,000 5,000 [2]
```

If you wish to use the spreadsheet, Select All, Copy, and Paste [3] the following at cell A1 of a blank Excel sheet:

Code: Select all

```
Initial portfolio value 10000
Stock allocation (S) 0.6
Bond allocation (B) =1-B2
Bond reserve of initial portfolio (R) 0.2
Rebalance band 0.05
="Percent stocks fall to reach "&TEXT(B2-B5,"##0%") =((B3*(B2-B5))/(1-B2+B5))/B2-1
Number of rebalances =LN(B4/B3)/LN(1+B6*B2)
Whole number =INT(B7)
="After "&B8&" rebalances portfolio falls to" =B1*(1+B6*B2)^B8
Portfolio when bonds at reserve =B1*B4/B3
Additional stock decrease =-(B9-B10)/(B9*B2)
Stocks fall overall =(1+B6)^B8*(1+B11)-1
```

- Calculation of 1st rebalancing:

4,889 = 6000 * (1 - 0.1852)

8,889 = 4889 [55%] + 4000 [45%]

5,333 = 8889 * 60%
- Calculation of 6th rebalancing:

2,780 = 3329 * (1 - 0.1650)

5,000 = 2780 [55%] + 2220 [45%]

3,000 = 5000 * 60%
- If you have trouble pasting, try "Paste Special" and "Text".