Deriving Shiller's formula for Monthly Total Bond Returns

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

Deriving Shiller's formula for Monthly Total Bond Returns

Post by Ben Mathew »

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+1)^(-119)*(1-Yt/Yt+1)

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

Thanks!
Last edited by Ben Mathew on Fri May 07, 2021 12:50 pm, edited 2 times in total.
peter_s
Posts: 31
Joined: Mon Jul 23, 2007 3:56 pm

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

Post by peter_s »

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

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

Post by #Cruncher »

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)
User avatar
Topic Author
Ben Mathew
Posts: 1570
Joined: Tue Mar 13, 2018 11:41 am
Location: Seattle

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

Post by Ben Mathew »

#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.

Link to download Damodaran's spreadsheet: http://www.stern.nyu.edu/~adamodar/pc/d ... tretSP.xls

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.
User avatar
Topic Author
Ben Mathew
Posts: 1570
Joined: Tue Mar 13, 2018 11:41 am
Location: Seattle

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

Post by Ben Mathew »

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

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

Post by #Cruncher »

Ben Mathew wrote: Thu Apr 29, 2021 9:45 amLink to download Damodaran's spreadsheet: http://www.stern.nyu.edu/~adamodar/pc/d ... tretSP.xls
... 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%
User avatar
Topic Author
Ben Mathew
Posts: 1570
Joined: Tue Mar 13, 2018 11:41 am
Location: Seattle

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

Post by Ben Mathew »

#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.)

Image
User avatar
Topic Author
Ben Mathew
Posts: 1570
Joined: Tue Mar 13, 2018 11:41 am
Location: Seattle

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

Post by Ben Mathew »

I thought it would be interesting to compare the above estimates against a hypothetical that does not include price changes (i.e. return for the year is assumed to be the starting yield for the year). The results are plotted in the graph below as the "coupon only" series.

Image

There was a significant difference. "Coupon only" averaged a CAGR of 6.16%, quite a bit lower than the CAGR of 7.02% to 7.45% when you include price changes. It pulled ahead briefly in the 70s as interest rates rose, but lagged since then as interest rates fell.

Something to keep in mind when simulating bond returns is that the historical return included a bonus from interest rate declines that we cannot count on repeating going forward. In other words, the historical return from holding 10 year bonds was greater than the expected return because the price changes have been favorable in recent decades. Something similar would be true for stocks as well--historical stock returns have been aided by the tailwind of valuation increase, and our forward looking expectations should correct for that.
Post Reply