60:40 portfolio in down market. Need help with math.

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Topic Author
ram
Posts: 1464
Joined: Tue Jan 01, 2008 10:47 pm
Location: Midwest

60:40 portfolio in down market. Need help with math.

Post by ram » Wed Mar 25, 2020 4:56 pm

Assume: Total invested assets (T) :......... 10 million.
Stocks S & P index (S): ..........................6 M
Bond/ Stable value fund (B). ..................4 M

Stock market starts going down. Every time the AA drifts by 5% ( 55: 45 etc) the investor re-balances to the original AA.

However rebalancing stops when stable value fund drops to 2 million ("Reserve amount" "R". (20 % of initial amount (T))

Questions:
1.How low will the stock market need to go in percentage terms to reach the the 2 M mark for the stable value fund.
2. 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"
Ram

livesoft
Posts: 70139
Joined: Thu Mar 01, 2007 8:00 pm

Re: 60:40 portfolio in down market. Need help with math.

Post by livesoft » Wed Mar 25, 2020 5:00 pm

In all honesty, I think you should ask your son for help on this.
Wiki This signature message sponsored by sscritic: Learn to fish.

User avatar
Topic Author
ram
Posts: 1464
Joined: Tue Jan 01, 2008 10:47 pm
Location: Midwest

Re: 60:40 portfolio in down market. Need help with math.

Post by ram » Wed Mar 25, 2020 5:21 pm

livesoft wrote:
Wed Mar 25, 2020 5:00 pm
In all honesty, I think you should ask your son for help on this.
Nice to "see" you back. I am sure he can do the math, but is usually not interested. The question is not for my situation. But I saw a lot of people posting strategies along these lines and thought that it may be useful for some members of the board. And if enough people find it useful it can become part of the wiki.
Ram

rossington
Posts: 552
Joined: Fri Jun 07, 2019 2:00 am
Location: Florida

Re: 60:40 portfolio in down market. Need help with math.

Post by rossington » Wed Mar 25, 2020 5:32 pm

Excel does have an "Investment Tracker" template that tracks portfolio values and actual asset allocation (which is compared to desired AA) that can be downloaded from Microsoft.
"Success is going from failure to failure without loss of enthusiasm." Winston Churchill.

siriusblack
Posts: 160
Joined: Mon Dec 24, 2018 3:50 pm

Re: 60:40 portfolio in down market. Need help with math.

Post by siriusblack » Wed Mar 25, 2020 6:09 pm

ram wrote:
Wed Mar 25, 2020 4:56 pm
Assume: Total invested assets (T) :......... 10 million.
Stocks S & P index (S): ..........................6 M
Bond/ Stable value fund (B). ..................4 M

Stock market starts going down. Every time the AA drifts by 5% ( 55: 45 etc) the investor re-balances to the original AA.

However rebalancing stops when stable value fund drops to 2 million ("Reserve amount" "R". (20 % of initial amount (T))

Questions:
1.How low will the stock market need to go in percentage terms to reach the the 2 M mark for the stable value fund.
2. 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"
At 60/40 with stable value fund = 2M, your total portfolio value would be 5M. A market drop of 83% in a single day would shave 5M from your stock allocation, and trigger a rebalance to 2M:3M. A slower market drop (depending on velocity) might trigger rebalance bands multiple times on the way to 5M total portfolio, so there are likely a number of different scenarios.

User avatar
Topic Author
ram
Posts: 1464
Joined: Tue Jan 01, 2008 10:47 pm
Location: Midwest

Re: 60:40 portfolio in down market. Need help with math.

Post by ram » Wed Mar 25, 2020 6:21 pm

siriusblack wrote:
Wed Mar 25, 2020 6:09 pm
At 60/40 with stable value fund = 2M, your total portfolio value would be 5M. A market drop of 83% in a single day would shave 5M from your stock allocation, and trigger a rebalance to 2M:3M. A slower market drop (depending on velocity) might trigger rebalance bands multiple times on the way to 5M total portfolio, so there are likely a number of different scenarios.
This is what I thought but was not sure. I do not know how difficult it will be, but perhaps having some reasonable rules, such as no more than 10% drop in a day and no more than 30% drop in a month can be applied.
Ram

User avatar
#Cruncher
Posts: 2917
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: 60:40 portfolio in down market. Need help with math.

Post by #Cruncher » Fri Mar 27, 2020 8:41 pm

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:
  1. How low will the stock market need to go in percentage terms to reach the the 2 M mark for the stable value fund.
  2. 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
  1. Calculation of 1st rebalancing:
    4,889 = 6000 * (1 - 0.1852)
    8,889 = 4889 [55%] + 4000 [45%]
    5,333 = 8889 * 60%
  2. Calculation of 6th rebalancing:
    2,780 = 3329 * (1 - 0.1650)
    5,000 = 2780 [55%] + 2220 [45%]
    3,000 = 5000 * 60%
  3. If you have trouble pasting, try "Paste Special" and "Text".

User avatar
Topic Author
ram
Posts: 1464
Joined: Tue Jan 01, 2008 10:47 pm
Location: Midwest

Re: 60:40 portfolio in down market. Need help with math.

Post by ram » Sat Mar 28, 2020 4:23 pm

Cruncher,
Excellent work. Thanks a lot. That kind of math calculation is far beyond my ability. (I am in healthcare).
There have been many recent posts and links to articles about fear from the recent fall.
You calculations have shown that if a person has:
1) a not too aggressive allocation ( 60:40) and
2) a relatively low reserve amount "R" (20%)
then there is no need to be scared of market declines of 2020 or 2008 and similar future ones. I would consider the >70% decline without recovery in between the stepwise declines as "once in a century or lower" event.

I understand that the second requirement (20%) may not be possible for most of the general population but should be feasible for many (?most) on this board.
Ram

User avatar
Topic Author
ram
Posts: 1464
Joined: Tue Jan 01, 2008 10:47 pm
Location: Midwest

Re: 60:40 portfolio in down market. Need help with math.

Post by ram » Sat Mar 28, 2020 5:03 pm

If reserve amount "R" = 25% ( bonds 2.5 M. stocks 3.75 M, Total= 6.25M ) then:

Stocks can fall by 56% in about 4 steps.
Ram

Post Reply