## Deriving Shiller's formula for Monthly Total Bond Returns

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Topic Author
Ben Mathew
Posts: 1556
Joined: Tue Mar 13, 2018 11:41 am
Location: Seattle

### Deriving Shiller's formula for Monthly Total Bond Returns

Can someone help me derive the formula Shiller uses for calculating monthly total bond returns in his spreadsheet "US Stock Market 1871 - Present and CAPE Ratio" on his website:

Monthly total bond returns for period t = Yt/Yt+1 + Yt + (1+Yt)^(-119)*(1-Yt/Yt+1)

where Yt = 10-Year Treasury Constant Maturity Rate (GS10) divided by 12

Thanks!
Last edited by Ben Mathew on Thu Apr 29, 2021 9:31 am, edited 1 time in total.
peter_s
Posts: 31
Joined: Mon Jul 23, 2007 3:56 pm

### Re: Deriving Shiller's formula for Monthly Total Bond Returns

I too would very much appreciate an explanation!
#Cruncher
Posts: 3182
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

### Re: Deriving Shiller's formula for Monthly Total Bond Returns

I believe Ben is referring to column R, "Monthly Total Bond Returns" on the Data sheet of the Excel file, U.S. Stock Markets 1871-Present and CAPE Ratio (clicking this link will download the spreadsheet!), linked near the top of the web page he references. Note that column R shows the return plus 1. E.g., if the return is 1%, column R will show 1.01. (Also by default the column shows only 2 decimal places.)

Shiller is calculating based on column G, "Long Interest Rate GS10". His calculation assumes a bond selling at par with a coupon equal to the rate. The return of such a bond after one period will equal the sum of one interest payment plus the change in value of the bond. This value after one period can be calculated using these two formulas where r is the new rate and n is the number of periods remaining to maturity:
• PV\$1 = 1 / (1 + r) ^ n = Present Value of \$1
• PVA\$1 = (1 - PV\$1) / r = Present Value of Annuity of \$1
I'll illustrate this with January 2001 from Shiller's Data sheet (row 1569):

Code: Select all

``````1.00896 = Shiller's value in column R
0.0043  = rate Jan 2001  = 5.16 / 1200
0.00425 = rate Feb 2001  = 5.10 / 1200
0.60370 = PV\$1           = 1 / 1.00425 ^ 119
0.40096 = coupon * PVA\$1 = 0.0043 * (1 - 0.60370) / 0.00425
1.00466 = total present value after one month [*]
0.00430 = one period's coupon
1.00896 = total value including coupon
0.896% = return``````
Ben Mathew wrote: Tue Apr 27, 2021 9:36 pmCan someone help me derive the formula Shiller uses ... t = Yt/Yt+1 + Yt + (1-Yt)^(-119)*(1-Yt/Yt+1)
(You made a typo, Ben. "(1-Yt)^(-119)" should be "(1+Yt)^(-119)".) Here is how to get from my formula above to Shiller's. Note that "x^-n" equals "1/x^n". Where
• c = coupon (same as initial rate) = Yt
• r = new rate = Yt+1
• n = number of remaining periods

Code: Select all

``````--PV\$1--           ------- PVA\$1--------
(1+r)^-n + c     * (1    - PV\$1      / r     + c  Formula explained above
(1+r)^-n + c     * (1    - (1+r)^-n) / r     + c  Substitute formula for PV\$1

(1+r)^-n + (c/r) * (1    - (1+r)^-n)         + c  Rearrange
(1+r)^-n + (c/r) - (c/r) * (1+r)^-n          + c  Extend (c/r)
(1+r)^-n +       - (c/r) * (1+r)^-n  + (c/r) + c  Rearrange
(1+r)^-n * (1    - (c/r))            + (c/r) + c  Factor (1+r)^-n

(c/r) + c + (1+r)^-n * (1 - (c/r))                Rearrange into Shiller's formula``````
* Equals the sum of the present value of principal after 119 months plus present value of the remaining 119 coupons (1.00466 = 0.60370 + 0.40096). Can also be computed in one step with Excel PV function:
1.00466 = -PV(0.00425, 119, 0.0043, 1, 0)
Topic Author
Ben Mathew
Posts: 1556
Joined: Tue Mar 13, 2018 11:41 am
Location: Seattle

### Re: Deriving Shiller's formula for Monthly Total Bond Returns

#Cruncher wrote: Wed Apr 28, 2021 5:10 pm Shiller is calculating based on column G, "Long Interest Rate GS10". His calculation assumes a bond selling at par with a coupon equal to the rate. The return of such a bond after one period will equal the sum of one interest payment plus the change in value of the bond. This value after one period can be calculated using these two formulas where r is the new rate and n is the number of periods remaining to maturity:
Thanks, #Cruncher! This is super helpful. Makes perfect sense now.

With some rearranging, this is the same formula used by Damodaran to calculate bond returns in his data spreadsheet "Discount Rate Estimation Historical Returns on Stocks, Bonds and Bills - United States" from his website.

In the sheet "T.Bond yield and return", he uses a formula for "Return on bond" (column C) that rearranges to Shiller's formula. Except Damodaran's formula uses ^10 instead of the ^9 that I would expect based on your derivation of Shiller's formula above. Any thoughts on why Damodaran is using ^10 instead of ^9?
Last edited by Ben Mathew on Thu Apr 29, 2021 10:40 am, edited 1 time in total.
Topic Author
Ben Mathew
Posts: 1556
Joined: Tue Mar 13, 2018 11:41 am
Location: Seattle

### Re: Deriving Shiller's formula for Monthly Total Bond Returns

#Cruncher wrote: Wed Apr 28, 2021 5:10 pm (You made a typo, Ben. "(1-Yt)^(-119)" should be "(1+Yt)^(-119)"
Fixed. Thanks!
#Cruncher
Posts: 3182
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

### Re: Deriving Shiller's formula for Monthly Total Bond Returns

... Any thoughts on why Damodaran is using ^10 instead of ^9?
From cell B3 of Damodaran's "T. Bond yield & return" sheet:
To compute the return on a constant maturity bond, I add two components - the promised coupon at the start of the year and the price change due to interest rate changes.
He's valuing each bond at the end of the year assuming it will be priced based on next year's 10-year yield. But in fact it will be priced based on next year's 9-year yield. Since he doesn't have that yield, he's stuck using the 10-year yield. And since his yield is wrong, his pricing will also be wrong whether he assumes the remaining life is 9 years or 10 years. By assuming ten, he magnifies the effect of rate changes: using 10 years makes the loss greater if rates go up, and the gain greater if rates go down.

I computed the effect of using 9 or 10 years for the 51 years 1969 to 2020. It made little difference. Using ten years the Compound Annual Growth Rate (CAGR) is 7.10%. If he'd used nine years, it would have been 7.02%. For the same period I estimated the rate for a bond with nine years to maturity by interpolating between the 7-year and 10-year FRED rates. Using this the CAGR would have been 7.45% over the 51 years, noticeably better than using the ten-year rate. [1] The following table shows the results for three cases of pricing the bonds after one year:
1. Using the nine-year yield for a bond maturing in nine years. CAGR = 7.45%
2. Using the ten-year yield for a bond maturing in nine years. CAGR = 7.02%
3. Using the ten-year yield for a bond maturing in ten years (Damodaran's method). CAGR = 7.10%

Code: Select all

``````Row       Col A   Col B   Col C    Col D      Col E    Col F    Col G
4          Growth factor 51 years  -->      39.04    31.87    33.05 [2]
5      Compound annual growth rate -->      7.45%    7.02%    7.10% [2]
-FRED Rates--  Est 9yr    - Value after One Year -
6                DGS7   DGS10  Mature     Case A   Case B   Case C``````

Code: Select all

``````  7  12/31/1969           7.88%
8  12/31/1970   6.25%   6.50%   6.417%    117.66%  117.07%  117.80% [3]
9  12/31/1971   5.85%   5.89%   5.877%    110.76%  110.67%  111.01%
10  12/31/1972   6.29%   6.41%   6.370%    102.68%  102.42%  102.14%
11  12/31/1973   6.76%   6.90%   6.853%    103.50%  103.20%  102.95%
12  12/31/1974   7.34%   7.40%   7.380%    103.82%  103.70%  103.45%
13  12/31/1975   7.68%   7.76%   7.733%    105.29%  105.13%  104.96%
14  12/31/1976   6.42%   6.81%   6.680%    114.89%  114.00%  114.49%
15  12/31/1977   7.66%   7.78%   7.740%    100.94%  100.69%  100.24%
16  12/31/1978   9.23%   9.15%   9.177%     99.47%   99.62%   99.05%
17  12/31/1979  10.36%  10.33%  10.340%    102.39%  102.44%  102.00%
18  12/31/1980  12.49%  12.43%  12.450%     99.22%   99.32%   98.67%

19  12/31/1981  13.97%  13.98%  13.977%    104.77%  104.76%  104.34%
20  12/31/1982  10.32%  10.36%  10.347%    134.62%  134.53%  135.88%
21  12/31/1983  11.77%  11.82%  11.803%    102.61%  102.53%  102.05%
22  12/31/1984  11.52%  11.55%  11.540%    113.34%  113.28%  113.37%
23  12/31/1985   8.87%   9.00%   8.957%    127.13%  126.84%  127.92%
24  12/31/1986   7.09%   7.23%   7.183%    120.74%  120.42%  121.30%
25  12/31/1987   8.67%   8.83%   8.777%     97.87%   97.57%   96.88%
26  12/31/1988   9.18%   9.14%   9.153%    106.90%  106.98%  106.85%
27  12/31/1989   7.97%   7.93%   7.943%    116.63%  116.72%  117.28%
28  12/31/1990   8.00%   8.08%   8.053%    107.16%  107.00%  106.93%

29  12/31/1991   6.38%   6.71%   6.600%    117.89%  117.12%  117.83%
30  12/31/1992   6.43%   6.70%   6.610%    107.37%  106.78%  106.78%
31  12/31/1993   5.53%   5.83%   5.730%    113.38%  112.66%  113.16%
32  12/31/1994   7.84%   7.84%   7.840%     93.19%   93.19%   92.24%
33  12/31/1995   5.49%   5.58%   5.550%    123.73%  123.50%  124.81%
34  12/31/1996   6.34%   6.43%   6.400%    100.10%   99.91%   99.45%
35  12/31/1997   5.77%   5.75%   5.757%    111.06%  111.11%  111.49%
36  12/31/1998   4.73%   4.65%   4.677%    113.49%  113.69%  114.39%
37  12/31/1999   6.55%   6.45%   6.483%     92.44%   92.64%   91.68%
38  12/31/2000   5.16%   5.12%   5.133%    115.75%  115.85%  116.66%

39  12/31/2001   4.84%   5.07%   4.993%    106.02%  105.47%  105.50%
40  12/31/2002   3.36%   3.83%   3.673%    115.61%  114.36%  115.21%
41  12/31/2003   3.77%   4.27%   4.103%    101.81%  100.60%  100.31%
42  12/31/2004   3.94%   4.24%   4.140%    105.23%  104.49%  104.51%
43  12/31/2005   4.36%   4.39%   4.380%    103.22%  103.14%  103.05%
44  12/31/2006   4.70%   4.71%   4.707%    102.11%  102.09%  101.88%
45  12/31/2007   3.70%   4.04%   3.927%    110.55%  109.68%  110.13%
46  12/31/2008   1.87%   2.25%   2.123%    119.59%  118.48%  119.91%
47  12/31/2009   3.39%   3.85%   3.697%     91.34%   90.27%   89.18%
48  12/31/2010   2.71%   3.30%   3.103%    109.64%  108.07%  108.47%

49  12/31/2011   1.35%   1.89%   1.710%    116.46%  114.87%  116.04%
50  12/31/2012   1.18%   1.78%   1.580%    104.47%  102.80%  102.89%
51  12/31/2013   2.45%   3.04%   2.843%     93.44%   91.99%   91.05%
52  12/31/2014   1.97%   2.17%   2.103%    110.65%  110.08%  110.79%
53  12/31/2015   2.09%   2.27%   2.210%    101.85%  101.36%  101.28%
54  12/31/2016   2.25%   2.45%   2.383%    101.36%  100.83%  100.69%
55  12/31/2017   2.33%   2.40%   2.377%    103.04%  102.85%  102.89%
56  12/31/2018   2.59%   2.69%   2.657%    100.37%  100.11%   99.89%
57  12/31/2019   1.83%   1.92%   1.890%    109.25%  109.00%  109.64%
58  12/31/2020   0.65%   0.93%   0.837%    111.27%  110.43%  111.33%``````
1. This is because for most of the years 1970 through 2020, the yield curve rises, meaning the yield of a nine-year maturity was less than that of a ten-year maturity. In such a case one makes extra money by repeatedly selling a 10-year after a year and buying a new 10-year.
2. Sample formulas for cells E4 & E5:
E4: Case A: 39.04 = PRODUCT(E8:E58)
E5: Case A: 7.45% = 39.04 ^ (1 / 51) - 1
3. Sample formulas for cells E8:G8:
E8: Case A: 117.66% = -PV(6.417%, 9, 7.88%, 1, 0) + 7.88%
F8: Case B: 117.07% = -PV(6.500%, 9, 7.88%, 1, 0) + 7.88%
G8: Case C: 117.80% = -PV(6.500%, 10, 7.88%, 1, 0) + 7.88%
Topic Author
Ben Mathew
Posts: 1556
Joined: Tue Mar 13, 2018 11:41 am
Location: Seattle

### Re: Deriving Shiller's formula for Monthly Total Bond Returns

#Cruncher wrote: Mon May 03, 2021 2:59 pm He's valuing each bond at the end of the year assuming it will be priced based on next year's 10-year yield. But in fact it will be priced based on next year's 9-year yield. Since he doesn't have that yield, he's stuck using the 10-year yield. And since his yield is wrong, his pricing will also be wrong whether he assumes the remaining life is 9 years or 10 years.
Am I understanding correctly that this is a problem with Shiller's calculation as well? i.e. He should be using the 119 month yield to calculate the selling price, but is stuck using the 120 month yield because that's all he has?

If so, I can see that Shiller's monthly return calculation may be more accurate than Damodaran's annual return calculation (using ^9 to keep the formula the same) because the difference in yield between 119 months vs 120 months is less than the difference in yield between 9 year and 10 year bonds. But then, maybe the fact that the smaller monthly error is being made 12 times per year wipes out that advantage?

To check, I added Shiller's estimates to your comparison. Shiller's CAGR was 7.45%, exactly the same as your calculation for the nine-year yield for a bond maturing in nine years. But though the average was correct, it had deviated in the middle, so it's not clear that it's better than Damodaran's estimate. So maybe a small monthly error * 12 is not better than a bigger annual error?

(And, as you said, Damodaran^9 and Damodaran^10 are practically the same. But it's helpful to know that I wasn't missing some reasoning behind his choice to use ^10.)