Monthly total bond returns for period t = Y

_{t}/Y

_{t+1}+ Y

_{t}+ (1+Y

_{t})^(-119)*(1-Y

_{t}/Y

_{t+1})

where Y

_{t}= 10-Year Treasury Constant Maturity Rate (GS10) divided by 12

Thanks!

- Ben Mathew
**Posts:**1556**Joined:**Tue Mar 13, 2018 11:41 am**Location:**Seattle

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 = Y_{t}/Y_{t+1} + Y_{t} + (1+Y_{t})^(-119)*(1-Y_{t}/Y_{t+1})

where Y_{t} = 10-Year Treasury Constant Maturity Rate (GS10) divided by 12

Thanks!

Monthly total bond returns for period t = Y

where Y

Thanks!

Last edited by Ben Mathew on Thu Apr 29, 2021 9:31 am, edited 1 time in total.

I too would very much appreciate an explanation!

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:
_{t})^(-119)" should be "(1+Y_{t})^(-119)".) Here is how to get from my formula above to Shiller's. Note that "x^-n" equals "1/x^n". Where
* 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)**

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

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
```

(You made a typo, Ben. "(1-YBen Mathew wrote: ↑Tue Apr 27, 2021 9:36 pmCan someone help me derive the formula Shiller uses ... t = Y_{t}/Y_{t+1}+ Y_{t}+ (1-Y_{t})^(-119)*(1-Y_{t}/Y_{t+1})

- c = coupon (same as initial rate) = Y
_{t} - r = new rate = Y
_{t+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
```

- Ben Mathew
**Posts:**1556**Joined:**Tue Mar 13, 2018 11:41 am**Location:**Seattle

Thanks, #Cruncher! This is super helpful. Makes perfect sense now.#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:

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.

- Ben Mathew
**Posts:**1556**Joined:**Tue Mar 13, 2018 11:41 am**Location:**Seattle

From cell B3 of Damodaran's "T. Bond yield & return" sheet: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?

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'sTo 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.

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

- Using the nine-year yield for a bond maturing in nine years. CAGR = 7.45%
- Using the ten-year yield for a bond maturing in nine years. CAGR = 7.02%
- 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%
```

- 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.
- Sample formulas for cells E4 & E5:

**E4: Case A: 39.04 = PRODUCT(E8:E58)**

E5: Case A: 7.45% = 39.04 ^ (1 / 51) - 1 - 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%

- Ben Mathew
**Posts:**1556**Joined:**Tue Mar 13, 2018 11:41 am**Location:**Seattle

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?#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's9-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.

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