Calculate Composite Rate for Series I Savings Bonds (I Bonds) using CPI¶
Install Packages¶
import numpy as np
import pandas as pd
from pandas_datareader.fred import FredReader
end_date = pd.to_datetime('today').date()
start_date = (end_date + pd.DateOffset(years=-5)).date()
print(f'start_date: {start_date} to end_date: {end_date}')
start_date: 2017-04-23 to end_date: 2022-04-23
df = FredReader('CPIAUCNS', start=start_date).read()
df
CPIAUCNS | |
---|---|
DATE | |
2017-05-01 | 244.733 |
2017-06-01 | 244.955 |
2017-07-01 | 244.786 |
2017-08-01 | 245.519 |
2017-09-01 | 246.819 |
2017-10-01 | 246.663 |
2017-11-01 | 246.669 |
2017-12-01 | 246.524 |
2018-01-01 | 247.867 |
2018-02-01 | 248.991 |
2018-03-01 | 249.554 |
2018-04-01 | 250.546 |
2018-05-01 | 251.588 |
2018-06-01 | 251.989 |
2018-07-01 | 252.006 |
2018-08-01 | 252.146 |
2018-09-01 | 252.439 |
2018-10-01 | 252.885 |
2018-11-01 | 252.038 |
2018-12-01 | 251.233 |
2019-01-01 | 251.712 |
2019-02-01 | 252.776 |
2019-03-01 | 254.202 |
2019-04-01 | 255.548 |
2019-05-01 | 256.092 |
2019-06-01 | 256.143 |
2019-07-01 | 256.571 |
2019-08-01 | 256.558 |
2019-09-01 | 256.759 |
2019-10-01 | 257.346 |
2019-11-01 | 257.208 |
2019-12-01 | 256.974 |
2020-01-01 | 257.971 |
2020-02-01 | 258.678 |
2020-03-01 | 258.115 |
2020-04-01 | 256.389 |
2020-05-01 | 256.394 |
2020-06-01 | 257.797 |
2020-07-01 | 259.101 |
2020-08-01 | 259.918 |
2020-09-01 | 260.280 |
2020-10-01 | 260.388 |
2020-11-01 | 260.229 |
2020-12-01 | 260.474 |
2021-01-01 | 261.582 |
2021-02-01 | 263.014 |
2021-03-01 | 264.877 |
2021-04-01 | 267.054 |
2021-05-01 | 269.195 |
2021-06-01 | 271.696 |
2021-07-01 | 273.003 |
2021-08-01 | 273.567 |
2021-09-01 | 274.310 |
2021-10-01 | 276.589 |
2021-11-01 | 277.948 |
2021-12-01 | 278.802 |
2022-01-01 | 281.148 |
2022-02-01 | 283.716 |
2022-03-01 | 287.504 |
Plot CPI Data¶
df.plot(title='CPI - CPIAUCNS', figsize=(15,10));
Calculate Percent Change¶
df['CPIAUCNS-1m'] = df['CPIAUCNS'].shift(periods=1)
df['CPIAUCNS-6m'] = df['CPIAUCNS'].shift(periods=6)
df['CPIAUCNS-12m'] = df['CPIAUCNS'].shift(periods=12)
df
CPIAUCNS | CPIAUCNS-1m | CPIAUCNS-6m | CPIAUCNS-12m | |
---|---|---|---|---|
DATE | ||||
2017-05-01 | 244.733 | NaN | NaN | NaN |
2017-06-01 | 244.955 | 244.733 | NaN | NaN |
2017-07-01 | 244.786 | 244.955 | NaN | NaN |
2017-08-01 | 245.519 | 244.786 | NaN | NaN |
2017-09-01 | 246.819 | 245.519 | NaN | NaN |
2017-10-01 | 246.663 | 246.819 | NaN | NaN |
2017-11-01 | 246.669 | 246.663 | 244.733 | NaN |
2017-12-01 | 246.524 | 246.669 | 244.955 | NaN |
2018-01-01 | 247.867 | 246.524 | 244.786 | NaN |
2018-02-01 | 248.991 | 247.867 | 245.519 | NaN |
2018-03-01 | 249.554 | 248.991 | 246.819 | NaN |
2018-04-01 | 250.546 | 249.554 | 246.663 | NaN |
2018-05-01 | 251.588 | 250.546 | 246.669 | 244.733 |
2018-06-01 | 251.989 | 251.588 | 246.524 | 244.955 |
2018-07-01 | 252.006 | 251.989 | 247.867 | 244.786 |
2018-08-01 | 252.146 | 252.006 | 248.991 | 245.519 |
2018-09-01 | 252.439 | 252.146 | 249.554 | 246.819 |
2018-10-01 | 252.885 | 252.439 | 250.546 | 246.663 |
2018-11-01 | 252.038 | 252.885 | 251.588 | 246.669 |
2018-12-01 | 251.233 | 252.038 | 251.989 | 246.524 |
2019-01-01 | 251.712 | 251.233 | 252.006 | 247.867 |
2019-02-01 | 252.776 | 251.712 | 252.146 | 248.991 |
2019-03-01 | 254.202 | 252.776 | 252.439 | 249.554 |
2019-04-01 | 255.548 | 254.202 | 252.885 | 250.546 |
2019-05-01 | 256.092 | 255.548 | 252.038 | 251.588 |
2019-06-01 | 256.143 | 256.092 | 251.233 | 251.989 |
2019-07-01 | 256.571 | 256.143 | 251.712 | 252.006 |
2019-08-01 | 256.558 | 256.571 | 252.776 | 252.146 |
2019-09-01 | 256.759 | 256.558 | 254.202 | 252.439 |
2019-10-01 | 257.346 | 256.759 | 255.548 | 252.885 |
2019-11-01 | 257.208 | 257.346 | 256.092 | 252.038 |
2019-12-01 | 256.974 | 257.208 | 256.143 | 251.233 |
2020-01-01 | 257.971 | 256.974 | 256.571 | 251.712 |
2020-02-01 | 258.678 | 257.971 | 256.558 | 252.776 |
2020-03-01 | 258.115 | 258.678 | 256.759 | 254.202 |
2020-04-01 | 256.389 | 258.115 | 257.346 | 255.548 |
2020-05-01 | 256.394 | 256.389 | 257.208 | 256.092 |
2020-06-01 | 257.797 | 256.394 | 256.974 | 256.143 |
2020-07-01 | 259.101 | 257.797 | 257.971 | 256.571 |
2020-08-01 | 259.918 | 259.101 | 258.678 | 256.558 |
2020-09-01 | 260.280 | 259.918 | 258.115 | 256.759 |
2020-10-01 | 260.388 | 260.280 | 256.389 | 257.346 |
2020-11-01 | 260.229 | 260.388 | 256.394 | 257.208 |
2020-12-01 | 260.474 | 260.229 | 257.797 | 256.974 |
2021-01-01 | 261.582 | 260.474 | 259.101 | 257.971 |
2021-02-01 | 263.014 | 261.582 | 259.918 | 258.678 |
2021-03-01 | 264.877 | 263.014 | 260.280 | 258.115 |
2021-04-01 | 267.054 | 264.877 | 260.388 | 256.389 |
2021-05-01 | 269.195 | 267.054 | 260.229 | 256.394 |
2021-06-01 | 271.696 | 269.195 | 260.474 | 257.797 |
2021-07-01 | 273.003 | 271.696 | 261.582 | 259.101 |
2021-08-01 | 273.567 | 273.003 | 263.014 | 259.918 |
2021-09-01 | 274.310 | 273.567 | 264.877 | 260.280 |
2021-10-01 | 276.589 | 274.310 | 267.054 | 260.388 |
2021-11-01 | 277.948 | 276.589 | 269.195 | 260.229 |
2021-12-01 | 278.802 | 277.948 | 271.696 | 260.474 |
2022-01-01 | 281.148 | 278.802 | 273.003 | 261.582 |
2022-02-01 | 283.716 | 281.148 | 273.567 | 263.014 |
2022-03-01 | 287.504 | 283.716 | 274.310 | 264.877 |
Composite Rate is 6 months percentage change multiplied by 2 (annualized).
df['percent_change_1m'] = (df['CPIAUCNS'] - df['CPIAUCNS-1m'])*100 / df['CPIAUCNS-1m']
df['percent_change_6m'] = (df['CPIAUCNS'] - df['CPIAUCNS-6m'])*100 / df['CPIAUCNS-6m']
df['percent_change_12m'] = (df['CPIAUCNS'] - df['CPIAUCNS-12m'])*100 / df['CPIAUCNS-12m']
# Composite Rate
df['percent_change_6m_annualized'] = df['percent_change_6m'] *2
df
CPIAUCNS | CPIAUCNS-1m | CPIAUCNS-6m | CPIAUCNS-12m | percent_change_1m | percent_change_6m | percent_change_12m | percent_change_6m_annualized | |
---|---|---|---|---|---|---|---|---|
DATE | ||||||||
2017-05-01 | 244.733 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
2017-06-01 | 244.955 | 244.733 | NaN | NaN | 0.090711 | NaN | NaN | NaN |
2017-07-01 | 244.786 | 244.955 | NaN | NaN | -0.068992 | NaN | NaN | NaN |
2017-08-01 | 245.519 | 244.786 | NaN | NaN | 0.299445 | NaN | NaN | NaN |
2017-09-01 | 246.819 | 245.519 | NaN | NaN | 0.529491 | NaN | NaN | NaN |
2017-10-01 | 246.663 | 246.819 | NaN | NaN | -0.063204 | NaN | NaN | NaN |
2017-11-01 | 246.669 | 246.663 | 244.733 | NaN | 0.002432 | 0.791066 | NaN | 1.582132 |
2017-12-01 | 246.524 | 246.669 | 244.955 | NaN | -0.058783 | 0.640526 | NaN | 1.281052 |
2018-01-01 | 247.867 | 246.524 | 244.786 | NaN | 0.544775 | 1.258650 | NaN | 2.517301 |
2018-02-01 | 248.991 | 247.867 | 245.519 | NaN | 0.453469 | 1.414147 | NaN | 2.828294 |
2018-03-01 | 249.554 | 248.991 | 246.819 | NaN | 0.226113 | 1.108099 | NaN | 2.216199 |
2018-04-01 | 250.546 | 249.554 | 246.663 | NaN | 0.397509 | 1.574213 | NaN | 3.148425 |
2018-05-01 | 251.588 | 250.546 | 246.669 | 244.733 | 0.415892 | 1.994170 | 2.801012 | 3.988341 |
2018-06-01 | 251.989 | 251.588 | 246.524 | 244.955 | 0.159388 | 2.216823 | 2.871548 | 4.433645 |
2018-07-01 | 252.006 | 251.989 | 247.867 | 244.786 | 0.006746 | 1.669847 | 2.949515 | 3.339694 |
2018-08-01 | 252.146 | 252.006 | 248.991 | 245.519 | 0.055554 | 1.267114 | 2.699180 | 2.534228 |
2018-09-01 | 252.439 | 252.146 | 249.554 | 246.819 | 0.116203 | 1.156062 | 2.276972 | 2.312125 |
2018-10-01 | 252.885 | 252.439 | 250.546 | 246.663 | 0.176676 | 0.933561 | 2.522470 | 1.867122 |
2018-11-01 | 252.038 | 252.885 | 251.588 | 246.669 | -0.334935 | 0.178864 | 2.176601 | 0.357728 |
2018-12-01 | 251.233 | 252.038 | 251.989 | 246.524 | -0.319396 | -0.300013 | 1.910159 | -0.600026 |
2019-01-01 | 251.712 | 251.233 | 252.006 | 247.867 | 0.190660 | -0.116664 | 1.551235 | -0.233328 |
2019-02-01 | 252.776 | 251.712 | 252.146 | 248.991 | 0.422705 | 0.249855 | 1.520135 | 0.499710 |
2019-03-01 | 254.202 | 252.776 | 252.439 | 249.554 | 0.564136 | 0.698387 | 1.862523 | 1.396773 |
2019-04-01 | 255.548 | 254.202 | 252.885 | 250.546 | 0.529500 | 1.053048 | 1.996440 | 2.106096 |
2019-05-01 | 256.092 | 255.548 | 252.038 | 251.588 | 0.212876 | 1.608488 | 1.790228 | 3.216975 |
2019-06-01 | 256.143 | 256.092 | 251.233 | 251.989 | 0.019915 | 1.954361 | 1.648485 | 3.908722 |
2019-07-01 | 256.571 | 256.143 | 251.712 | 252.006 | 0.167094 | 1.930381 | 1.811465 | 3.860762 |
2019-08-01 | 256.558 | 256.571 | 252.776 | 252.146 | -0.005067 | 1.496186 | 1.749780 | 2.992373 |
2019-09-01 | 256.759 | 256.558 | 254.202 | 252.439 | 0.078345 | 1.005893 | 1.711305 | 2.011786 |
2019-10-01 | 257.346 | 256.759 | 255.548 | 252.885 | 0.228619 | 0.703586 | 1.764043 | 1.407172 |
2019-11-01 | 257.208 | 257.346 | 256.092 | 252.038 | -0.053624 | 0.435781 | 2.051278 | 0.871562 |
2019-12-01 | 256.974 | 257.208 | 256.143 | 251.233 | -0.090977 | 0.324428 | 2.285130 | 0.648856 |
2020-01-01 | 257.971 | 256.974 | 256.571 | 251.712 | 0.387977 | 0.545658 | 2.486572 | 1.091316 |
2020-02-01 | 258.678 | 257.971 | 256.558 | 252.776 | 0.274062 | 0.826324 | 2.334874 | 1.652648 |
2020-03-01 | 258.115 | 258.678 | 256.759 | 254.202 | -0.217645 | 0.528122 | 1.539327 | 1.056243 |
2020-04-01 | 256.389 | 258.115 | 257.346 | 255.548 | -0.668694 | -0.371873 | 0.329097 | -0.743746 |
2020-05-01 | 256.394 | 256.389 | 257.208 | 256.092 | 0.001950 | -0.316475 | 0.117926 | -0.632951 |
2020-06-01 | 257.797 | 256.394 | 256.974 | 256.143 | 0.547205 | 0.320266 | 0.645733 | 0.640532 |
2020-07-01 | 259.101 | 257.797 | 257.971 | 256.571 | 0.505824 | 0.438034 | 0.986082 | 0.876067 |
2020-08-01 | 259.918 | 259.101 | 258.678 | 256.558 | 0.315321 | 0.479360 | 1.309645 | 0.958721 |
2020-09-01 | 260.280 | 259.918 | 258.115 | 256.759 | 0.139275 | 0.838773 | 1.371325 | 1.677547 |
2020-10-01 | 260.388 | 260.280 | 256.389 | 257.346 | 0.041494 | 1.559739 | 1.182066 | 3.119479 |
2020-11-01 | 260.229 | 260.388 | 256.394 | 257.208 | -0.061063 | 1.495745 | 1.174536 | 2.991490 |
2020-12-01 | 260.474 | 260.229 | 257.797 | 256.974 | 0.094148 | 1.038414 | 1.362005 | 2.076828 |
2021-01-01 | 261.582 | 260.474 | 259.101 | 257.971 | 0.425378 | 0.957542 | 1.399770 | 1.915083 |
2021-02-01 | 263.014 | 261.582 | 259.918 | 258.678 | 0.547438 | 1.191145 | 1.676215 | 2.382290 |
2021-03-01 | 264.877 | 263.014 | 260.280 | 258.115 | 0.708327 | 1.766175 | 2.619763 | 3.532350 |
2021-04-01 | 267.054 | 264.877 | 260.388 | 256.389 | 0.821891 | 2.560026 | 4.159695 | 5.120052 |
2021-05-01 | 269.195 | 267.054 | 260.229 | 256.394 | 0.801711 | 3.445427 | 4.992707 | 6.890854 |
2021-06-01 | 271.696 | 269.195 | 260.474 | 257.797 | 0.929066 | 4.308299 | 5.391451 | 8.616599 |
2021-07-01 | 273.003 | 271.696 | 261.582 | 259.101 | 0.481052 | 4.366126 | 5.365475 | 8.732252 |
2021-08-01 | 273.567 | 273.003 | 263.014 | 259.918 | 0.206591 | 4.012334 | 5.251272 | 8.024668 |
2021-09-01 | 274.310 | 273.567 | 264.877 | 260.280 | 0.271597 | 3.561276 | 5.390349 | 7.122551 |
2021-10-01 | 276.589 | 274.310 | 267.054 | 260.388 | 0.830812 | 3.570439 | 6.221869 | 7.140878 |
2021-11-01 | 277.948 | 276.589 | 269.195 | 260.229 | 0.491343 | 3.251546 | 6.809003 | 6.503093 |
2021-12-01 | 278.802 | 277.948 | 271.696 | 260.474 | 0.307252 | 2.615423 | 7.036403 | 5.230846 |
2022-01-01 | 281.148 | 278.802 | 273.003 | 261.582 | 0.841457 | 2.983484 | 7.479872 | 5.966967 |
2022-02-01 | 283.716 | 281.148 | 273.567 | 263.014 | 0.913398 | 3.709877 | 7.871064 | 7.419755 |
2022-03-01 | 287.504 | 283.716 | 274.310 | 264.877 | 1.335138 | 4.809887 | 8.542456 | 9.619773 |
Composite Rate¶
Following we can see the composite rate based on the latest CPI data.
composite_rate = df.iloc[-1]['percent_change_6m_annualized'].round(2)
latest_CPI_date = df.iloc[-1].name.date()
print(f'Latest CPI Date: {latest_CPI_date}, Composite Rate: {composite_rate}%')
Latest CPI Date: 2022-03-01, Composite Rate: 9.62%
Plot Percent Changes over Time¶
pct_columns = ['percent_change_1m', 'percent_change_6m', 'percent_change_12m', 'percent_change_6m_annualized']
df[pct_columns].tail(12)
percent_change_1m | percent_change_6m | percent_change_12m | percent_change_6m_annualized | |
---|---|---|---|---|
DATE | ||||
2021-04-01 | 0.821891 | 2.560026 | 4.159695 | 5.120052 |
2021-05-01 | 0.801711 | 3.445427 | 4.992707 | 6.890854 |
2021-06-01 | 0.929066 | 4.308299 | 5.391451 | 8.616599 |
2021-07-01 | 0.481052 | 4.366126 | 5.365475 | 8.732252 |
2021-08-01 | 0.206591 | 4.012334 | 5.251272 | 8.024668 |
2021-09-01 | 0.271597 | 3.561276 | 5.390349 | 7.122551 |
2021-10-01 | 0.830812 | 3.570439 | 6.221869 | 7.140878 |
2021-11-01 | 0.491343 | 3.251546 | 6.809003 | 6.503093 |
2021-12-01 | 0.307252 | 2.615423 | 7.036403 | 5.230846 |
2022-01-01 | 0.841457 | 2.983484 | 7.479872 | 5.966967 |
2022-02-01 | 0.913398 | 3.709877 | 7.871064 | 7.419755 |
2022-03-01 | 1.335138 | 4.809887 | 8.542456 | 9.619773 |
df[pct_columns].plot(title='CPI', figsize=(15, 10));