US Treasuries#

…

#> Libraries
import numpy as np

import pandas as pd
# from IPython.display import display  # import not required, maybe

import plotly.express as px
import plotly.graph_objects as go
import plotly.io as pio
from plotly.subplots import make_subplots

import yfinance as yf

#> Mount GDrive if required - for Colab editing
from pathlib import Path

# Detect Colab and mount Drive if needed
try:
    from google.colab import drive
    IN_COLAB = True
    pio.renderers.default = "colab"
except ImportError:
    IN_COLAB = False
    pio.renderers.default = "iframe"

if IN_COLAB:
    drive.mount('/content/drive')
    %cd /content/drive/MyDrive/basics-books/repos/financial-edu/bbooks-financial-edu/ch/history

FILE_ROOT = Path.cwd()

print("Repo root:", FILE_ROOT)

Repo root: /home/davide/Documents/basics-books/books/bbooks-financial-edu/ch/history

#> Import Shiller data (.xls)
shiller_filen = str(FILE_ROOT) + "/../../code/data/shiller/ie_data.xlsx"
df_shiller = pd.read_excel(shiller_filen, sheet_name="Data", skiprows=7,) #  engine="xlrd")

#> Clean data
#> Shiller
#> Remove non-data last row
df_shiller = df_shiller.iloc[:-1].copy()

#> Convert Data to proper datetime type
df_shiller["Date"] = df_shiller["Date"].map(lambda x: f"{x:.2f}")
df_shiller["Date"] = pd.to_datetime( df_shiller["Date"], format="%Y.%m")

#> Shiller, chapt 26 file, with yearly data
chap26_filen = str(FILE_ROOT) + "/../../code/data/shiller/chapt26.xlsx"
colnames = pd.read_excel(chap26_filen, sheet_name='Data', skiprows=2, nrows=1).columns
df_chap26 = pd.read_excel(chap26_filen, sheet_name="Data", skiprows=7, names=colnames)

df_chap26 = df_chap26.iloc[:-7].copy()

# Rename the first column to 'Year'
df_chap26.rename(columns={df_chap26.columns[0]: 'Year'}, inplace=True)
df_chap26['Year'] = df_chap26['Year'].astype(int)

df_chap26["T-Bill Return"] = ( 1. + df_chap26["R"]/100. ).shift(1).fillna(1).cumprod()
df_chap26["T-Bill Real Return"] = df_chap26["RealR"].shift(1).fillna(1).cumprod()

display(df_chap26.head())
display(df_chap26.tail())
Year P D E R RLONG CPI RealR C Unnamed: 9 ... P*C RealD Return ln(1+ret) RealE P/E E10 P/E10 T-Bill Return T-Bill Real Return
0 1871 4.44 0.26 0.40 6.35 5.32 12.464061 1.047504 Tab) 1871.0 ... NaN 4.731385 0.135809 0.127345 7.279054 NaN NaN NaN 1.000000 1.000000
1 1872 4.86 0.30 0.43 7.81 5.36 12.654392 1.054320 in 2005 1872.0 ... NaN 5.338874 0.088615 0.084907 7.652386 12.150000 NaN NaN 1.063500 1.047504
2 1873 5.11 0.33 0.46 8.35 5.58 12.939807 1.133511 dollars 1873.0 ... NaN 6.143830 0.021590 0.021360 8.564127 11.883721 NaN NaN 1.146559 1.104405
3 1874 4.66 0.33 0.46 6.86 5.47 12.368896 1.148076 NaN 1874.0 ... NaN 6.600773 0.122790 0.115817 9.201078 10.130435 NaN NaN 1.242297 1.251855
4 1875 4.54 0.30 0.36 4.96 5.07 11.512651 1.114055 NaN 1875.0 ... NaN 6.369199 0.112843 0.106918 7.643039 9.869565 NaN NaN 1.327519 1.437225

5 rows × 23 columns

Year P D E R RLONG CPI RealR C Unnamed: 9 ... P*C RealD Return ln(1+ret) RealE P/E E10 P/E10 T-Bill Return T-Bill Real Return
141 2012 1300.58 31.25 86.51 NaN 1.97 226.665 0.984302 NaN 2012.0 ... NaN 31.25 0.144043 0.134569 NaN 14.957792 68.558322 20.265948 631.02331 34.699283
142 2013 1480.4 34.99 100.20 NaN 1.91 230.280 NaN NaN 2013.0 ... NaN NaN NaN NaN NaN NaN 69.552621 21.593294 631.02331 34.154564
143 2014 1822.36 39.44 102.31 NaN 2.86 233.916 NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 631.02331 34.154564
144 2015 2028.18 43.39 NaN NaN 1.88 233.707 NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 631.02331 34.154564
145 2016 1918.6 NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN 631.02331 34.154564

5 rows × 23 columns

Monthly total bond return. Given interest of 10-year bonds, with \(N = 10\) years

\[\begin{split}\begin{aligned} M_n & = \underbrace{\frac{I_n}{12 \cdot 100}}_{\text{month coupon}} + \underbrace{\frac{I_n}{I_{n+1}} + \left( 1 + \frac{I_{n+1}}{12 \cdot 100} \right)^{-\frac{1}{12N-1}} \cdot \left( 1 - \frac{I_n}{I_{n+1}} \right)}_{\text{price change}} = \\ & = \underbrace{\frac{i_n}{12}}_{\text{month coupon}} + \underbrace{\frac{i_n}{i_{n+1}} + \left( 1 + \frac{i_{n+1}}{12} \right)^{-\frac{1}{12N-1}} \cdot \left( 1 - \frac{i_n}{i_{n+1}} \right)}_{\text{price change}} \ , \end{aligned}\end{split}\]

being \(I = 100 \cdot i\), the rates as a percentage.

Real total bond return.

\[T^R_{n+1} = T^R_n \cdot M_n \cdot \frac{\text{CPI}_n}{\text{CPI}_{n+1}} \ ,\]

being \(\frac{CPI_n}{CPI_{n+1}}\) the “realization” factor.

#> Inspection
# Rate GS10    : Interest Rate of 10Y Bond
# Returns      : Monthly Total Bond Returns
# Returns.1    :    Real Total Bond Returns
# Real Return  : 10Y Annualized Stock Real Return
# Real Return.1: 10Y Annualized Bonds Real Return
# Real Return.2: Real 10Y Excess Annualized Returns
print(df_shiller.columns)
# display(df_shiller.head())
# display(df_shiller.tail())

Index(['Date', 'P', 'D', 'E', 'CPI', 'Fraction', 'Rate GS10', 'Price',
       'Dividend', 'Price.1', 'Earnings', 'Earnings.1', 'CAPE', 'Unnamed: 13',
       'TR CAPE', 'Unnamed: 15', 'Yield', 'Returns', 'Returns.1',
       'Real Return', 'Real Return.1', 'Returns.2'],
      dtype='object')

Return#

#> Evaluate nominal return, not contained in Shiller data
# df_shiller["10Y Bond Nominal Total Return"] = 1.
df_shiller["10Y Bond Nominal Total Return"] = df_shiller['Returns'].shift(1).fillna(1).cumprod()

display(df_shiller.head())
display(df_shiller.tail())
Date P D E CPI Fraction Rate GS10 Price Dividend Price.1 ... Unnamed: 13 TR CAPE Unnamed: 15 Yield Returns Returns.1 Real Return Real Return.1 Returns.2 10Y Bond Nominal Total Return
0 1871-01-01 4.44 0.26 0.4 12.464061 1871.041667 5.32 114.692610 6.716234 114.692610 ... NaN NaN NaN NaN 1.004177 1.000000 0.130609 0.092504 0.038106 1.000000
1 1871-02-01 4.5 0.26 0.4 12.844641 1871.125000 5.323333 112.798304 6.517235 113.341406 ... NaN NaN NaN NaN 1.004180 0.974424 0.130858 0.094635 0.036224 1.004177
2 1871-03-01 4.61 0.26 0.4 13.034972 1871.208333 5.326667 113.868307 6.422074 114.954311 ... NaN NaN NaN NaN 1.004183 0.964209 0.130951 0.096186 0.034765 1.008374
3 1871-04-01 4.74 0.26 0.4 12.559226 1871.291667 5.33 121.514327 6.665343 123.233997 ... NaN NaN NaN NaN 1.004185 1.004919 0.122056 0.090972 0.031084 1.012592
4 1871-05-01 4.86 0.26 0.4 12.273812 1871.375000 5.333333 127.487866 6.820339 129.868479 ... NaN NaN NaN NaN 1.004188 1.032591 0.122638 0.089488 0.033150 1.016830

5 rows × 23 columns

Date P D E CPI Fraction Rate GS10 Price Dividend Price.1 ... Unnamed: 13 TR CAPE Unnamed: 15 Yield Returns Returns.1 Real Return Real Return.1 Returns.2 10Y Bond Nominal Total Return
1850 2025-03-01 5683.983333 76.145301 216.69 319.799 2025.208333 4.28 5722.525411 76.661629 3845325.219 ... NaN 37.348615 NaN 0.016749 1.003567 39.625790 NaN NaN NaN 1016.706201
1851 2025-04-01 5369.495714 76.546867 NaN 320.795 2025.291667 4.28 5389.121125 76.826645 3625591.938 ... NaN 35.015341 NaN 0.018767 0.992343 39.643654 NaN NaN NaN 1020.332454
1852 2025-05-01 5810.919524 76.948434 NaN 321.465 2025.375000 4.42 5820.002899 77.068716 3919793.241 ... NaN 37.769849 NaN 0.014834 1.006896 39.258095 NaN NaN NaN 1012.519392
1853 2025-06-01 6029.9515 77.350000 NaN 321.8 2025.458333 4.38 6033.090148 77.390261 4067651.835 ... NaN 39.098992 NaN 0.014026 1.010099 39.487674 NaN NaN NaN 1019.501865
1854 2025-07-01 6279.35 NaN NaN 321.9675 2025.541667 4.3 6279.350000 NaN 4233686.042 ... NaN 40.590130 NaN 0.013847 NaN 39.865727 NaN NaN NaN 1029.798273

5 rows × 23 columns

#> Nominal and real value
fig = make_subplots(rows=2, cols=1, shared_xaxes=True, # vertical_spacing=0.1,
    subplot_titles=("10Y T-Bond - Nominal Total Return", "10Y T-Bond - Real Total Return",)
)

#> 10Y T-Bonds
fig.add_trace(go.Scatter(x=df_shiller["Date"], y=df_shiller["10Y Bond Nominal Total Return"], name="10Y", line=dict(color="steelblue")),
    row=1, col=1
)
fig.add_trace(go.Scatter(x=df_shiller["Date"], y=df_shiller["Returns.1"], name="10Y", line=dict(color="steelblue"), showlegend=False),
    row=2, col=1
)
#> 1Y T-Bills
fig.add_trace(go.Scatter(x=df_chap26["Year"], y=df_chap26["T-Bill Return"], name="1Y", line=dict(color="orange")),
    row=1, col=1
)
fig.add_trace(go.Scatter(x=df_chap26["Year"], y=df_chap26["T-Bill Real Return"], name="1Y", line=dict(color="orange"), showlegend=False),
    row=2, col=1
)



fig.update_yaxes(type="log", row=1, col=1)
fig.update_yaxes(type="log", row=2, col=1)

fig.update_layout(showlegend=True,)

fig.show()