Portfolio Balancing - Stock Fund & Stable Fund¶
M. Fawcett - 03/09/2021
Start with a $1 million 403(b) retirement investment portfolio consisting of stock fund shares and Lincoln Stable fund. Use the historical prices and dividends found on Yahoo Finance to analyze the portfolio's performance from the stock fund inception to December 31, 2020, up to a limit of 25 years.
"Stable Funds" are a special type of asset usually only available through 401 and 403 accounts. They are backed by Guaranteed Income Contracts (GIC) with insurance companies.
Assumptions:
- The portfolio is contained in a tax deferred 403(b) account.
- The portfolio starts with $1 million with no further contributions.
- There are no transaction costs or administration costs.
- The portfolio contains two types of assets, a Stock Fund and a Fixed Dividend Stable Fund.
- IRS Minimum distributions (RMD) are required each year starting at the end of the first year.
- The RMD is handled by withdrawals from the stable fund. If there are insufficient stable funds, stock fund shares are sold.
- A quarter's "ending shares" is the quarter's "starting shares" plus purchases using paid dividends minus shares sold to meet RMD requirements.
- A quarter's "starting shares" is the previous quarter's "ending shares"
- Paid dividends for a quarter are based on the quarter's "starting shares".
- The number of fund shares bought with dividends during a quarter is based on the actual closing not the adjusted closing price per share for that quarter. See Note below.
- The value of the portfolio that the RMD is based on is the quarter's "starting shares" plus "shares purchased" times the adjusted closing price.
- All dividends are reinvested in new shares of the stock fund.
- The Stable Fund pays a constant 3 percent per year.
- Quarterly rebalancing between the stock fund and stable fund is aimed at a set target proportion.
- Rebalancing is done by either selling stock fund shares and moving the proceeds to the Stable Fund, or new stock fund shares are bought using Stable Fund money.
- Rebalancing is the last step in a quarter's adjustements and occurs after removing the RMD amount from the portfolio.
- All other buying, selling and valuing of stock fund shares is done using the final closing price of the quarter.
- An inflation adjusted after tax annual income of at least $50,000 is required.
- If the RMD is less than inflation adjusted after tax $50,000, stock funds are sold to make up the difference.
- Annual inflation is 2%.
- RMD amounts in excess of the after tax $50,000 are used to buy SPY shares in a separate non-tax deferred investment account (like an Etrade account).
I used the yfinance package to download prices and dividends from Yahoo Finance.
I used the "adjusted" closing price from Yahoo when calculating porfolio values. These are the prices that have been adjusted for stock splits and ex-dividend bumps. See reference number 6.
NOTE - Historical dividend amounts are not adjusted by Yahoo, and are the actual amount that was declared at the time. When I calculate the number of shares to purchased with reinvested dividends I do so using the historical closing price rather than the adjusted closing price.
For example, say the historical actual closing price was $80.00 and the adjusted closing price is \$40.00 (to account for a later 2:1 stock split.)
If the dividend amount was $1.00 per share and I had 1,000 shares, there would be \$1,000 in paid dividends. Dividing that amount by 80, the actual price at the time, would mean buying 12.5 shares and adding them to the portfolio.
The value of the portfolio would then be increased by 12.5 times the adjusted price of $40.00, or \$500.00.
References¶
- Mimimum distribution calculations: https://www.irs.gov/pub/irs-tege/uniform_rmd_wksht.pdf
- yfinance package: (https://github.com/ranaroussi/yfinance)
- Asset allocation: https://www.sec.gov/reportspubs/investor-publications/investorpubsassetallocationhtm.html
- Required Mimimum Distribution table: https://www.johngoldhamer.com/Workshops/IRS-Required-Minimum-Distributions-(RMD)-Table-III-(Uniform-Lifetime)-Spreadsheet.xlsx
- Financial data: https://finance.yahoo.com
- Adjusting historical prices: https://www.investopedia.com/terms/a/adjusted_closing_price.asp
In [1]:
# Install libraries. Did this once. Not available in Anaconda.
# !pip install yfinance
In [2]:
# Load libraries
import pandas as pd
import numpy as np
import yfinance as yf
# from yahoofinancials import YahooFinancials
import datetime as dt
In [3]:
# Make exponentiated values appear as 2 decimal places.
pd.options.display.float_format = '{:.2f}'.format
In [4]:
#### Settings to allow the lists of results to not be truncated.
pd.set_option('display.max_rows', None)
pd.set_option('display.max_colwidth', 140)
pd.set_option('display.max_columns', None)
In [5]:
# Set some CONSTANT values.
STOCK_FUND_NAME = 'High Yield Bond Fund' # Enter stock fund name here before executing the Notebook.
STOCK_FUND_SYMBOL = 'VWEAX' # Enter stock fund symbol here before executing the Notebook.
STARTING_INVESTMENT = 1000000 # $1 million. The amount the portfolio starts with.
END_DATE = '2020-12-31' # Cut-off date for the portfolio analysis.
STABLE_FUND_ANNUAL_RATE = 0.03 # 3%
STABLE_FUND_QUARTERLY_RATE = STABLE_FUND_ANNUAL_RATE / 4
STABLE_FUND_PROPORTION = 0.333 # 1/3rd
AFTER_TAX_INCOME = 40000
TAX_RATE = 0.25 # 25%
BEFORE_TAX_INCOME = AFTER_TAX_INCOME * (1 + TAX_RATE)
INFLATION_RATE = 0.02 # 2%
# Other constant values set in code below:
# STARTING_SHARES_STOCK
# STARTING_PRICE_STOCK
# START_DATE # Will be the first January 1 after the fund's inception date.
print("STABLE_FUND_QUARTERLY_RATE:", STABLE_FUND_QUARTERLY_RATE)
print("Required before tax annual income, adjusted for inflation: ${:.2f}".format(BEFORE_TAX_INCOME))
STABLE_FUND_QUARTERLY_RATE: 0.0075 Required before tax annual income, adjusted for inflation: $50000.00
In [6]:
# Load Required Mimimum Distributions (RMD) table.
# Original RMD table was downloaded from Web as a Mac "numbers" file and was then converted to an Excel file.
rmd_df = pd.read_excel("MinimumDistributions.xlsx")
rmd_df = rmd_df.iloc[4:35][['Unnamed: 1', 'Unnamed: 2']]
rmd_df.columns = ["Age", "Divisor"]
rmd_df.index = np.arange(1, len(rmd_df) + 1) # Make the index "1" based.
In [7]:
# Examine RMD table
print(rmd_df.head())
print(rmd_df.tail())
Age Divisor
1 70.50 27.40
2 71 26.50
3 72 25.60
4 73 24.70
5 74 23.80
Age Divisor
27 96 8.10
28 97 7.60
29 98 7.10
30 99 6.70
31 100 6.30
In [8]:
# Get stock fund daily prices fro inception date to specified End date.
# Using "auto_adjust" = True to use the split adjusted price to standardize all prices
stock_price_df = yf.download(STOCK_FUND_SYMBOL,
# start = '1990-01-01',
end = END_DATE,
progress = False)
# Examine results
print(STOCK_FUND_NAME, '-', STOCK_FUND_SYMBOL)
print(stock_price_df.head())
print(stock_price_df.tail())
High Yield Bond Fund - VWEAX
Open High Low Close Adj Close Volume
Date
2001-11-12 6.33 6.33 6.33 6.33 1.93 0.00
2001-11-13 6.34 6.34 6.34 6.34 1.94 0.00
2001-11-14 6.35 6.35 6.35 6.35 1.94 0.00
2001-11-15 6.34 6.34 6.34 6.34 1.94 0.00
2001-11-16 6.34 6.34 6.34 6.34 1.94 0.00
Open High Low Close Adj Close Volume
Date
2020-12-24 5.96 5.96 5.96 5.96 5.92 0.00
2020-12-28 5.97 5.97 5.97 5.97 5.93 0.00
2020-12-29 5.98 5.98 5.98 5.98 5.94 0.00
2020-12-30 5.98 5.98 5.98 5.98 5.94 0.00
2020-12-31 nan nan nan nan nan nan
Note that in some instances, Yahoo does not have daily open, high, low or volume data. Only closing prices will be available. That's okay.
In [9]:
# Find the fund's first January 1st after inception date.
START_DATE = str(stock_price_df.index[0].year + 1) + '-01-01'
if START_DATE < '1994-01-01':
START_DATE = '1994-01-01'
print('START_DATE:', START_DATE)
# Limit fund data using that start date.
stock_price_df = stock_price_df[stock_price_df.index >= START_DATE]
stock_price_df.head()
START_DATE: 2002-01-01
Out[9]:
| Open | High | Low | Close | Adj Close | Volume | |
|---|---|---|---|---|---|---|
| Date | ||||||
| 2002-01-02 | 6.29 | 6.29 | 6.29 | 6.29 | 1.92 | 0.00 |
| 2002-01-03 | 6.31 | 6.31 | 6.31 | 6.31 | 1.93 | 0.00 |
| 2002-01-04 | 6.32 | 6.32 | 6.32 | 6.32 | 1.93 | 0.00 |
| 2002-01-07 | 6.34 | 6.34 | 6.34 | 6.34 | 1.94 | 0.00 |
| 2002-01-08 | 6.34 | 6.34 | 6.34 | 6.34 | 1.94 | 0.00 |
In [10]:
# Just keep the fund's closing price
stock_close_df = stock_price_df.loc[:, ['Close', 'Adj Close']]
# Set column names
stock_close_df = stock_close_df.rename(columns={'Close': 'ActualClose', 'Adj Close': 'AdjustedClose'})
# Examine results
print(stock_close_df.head())
print(stock_close_df.tail())
ActualClose AdjustedClose
Date
2002-01-02 6.29 1.92
2002-01-03 6.31 1.93
2002-01-04 6.32 1.93
2002-01-07 6.34 1.94
2002-01-08 6.34 1.94
ActualClose AdjustedClose
Date
2020-12-24 5.96 5.92
2020-12-28 5.97 5.93
2020-12-29 5.98 5.94
2020-12-30 5.98 5.94
2020-12-31 nan nan
In [11]:
# Use "resample" to get the earliest and the latest closing prices for each quarter.
# Do this for Actual and Adjusted separately and then merge them into one dataset.
# Quarters are represented by their ending dates.
# The syntax is simplifed, so no "on" parameter needed, by the dataset having a timeseries index, "Date".
stock_quarter_price_actual_df = stock_close_df.resample('Q')['ActualClose'].agg(['first','last'])
stock_quarter_price_adjusted_df = stock_close_df.resample('Q')['AdjustedClose'].agg(['first','last'])
# Get the starting price for fund
STARTING_PRICE_STOCK = round(stock_quarter_price_adjusted_df.iloc[0, 0], 2)
# Do the merge
stock_quarter_price_df = pd.merge(stock_quarter_price_actual_df, stock_quarter_price_adjusted_df, how = 'left', on = 'Date')
# Set column names
stock_quarter_price_df = stock_quarter_price_df.rename(columns =
{'first_x': 'ActualOpen',
'last_x': 'ActualClose',
'first_y': 'AdjustedOpen',
'last_y': 'AdjustedClose'})
# Examine results
print(stock_quarter_price_df.head())
print(stock_quarter_price_df.tail())
ActualOpen ActualClose AdjustedOpen AdjustedClose
Date
2002-03-31 6.29 6.24 1.92 1.91
2002-06-30 6.23 6.00 1.90 1.83
2002-09-30 5.99 5.68 1.83 1.74
2002-12-31 5.67 5.88 1.73 1.80
2003-03-31 5.87 6.01 1.79 1.85
ActualOpen ActualClose AdjustedOpen AdjustedClose
Date
2019-12-31 5.89 5.96 5.51 5.65
2020-03-31 5.97 5.26 5.66 5.05
2020-06-30 5.21 5.61 5.01 5.46
2020-09-30 5.63 5.78 5.48 5.69
2020-12-31 5.79 5.98 5.70 5.94
In [12]:
print("The price to use to set the initial number of shares is the AdjustedOpen:", STARTING_PRICE_STOCK)
The price to use to set the initial number of shares is the AdjustedOpen: 1.92
In [13]:
# Get stock fund dividends and put into the form of a pandas dataframe
stock_ticker = yf.Ticker(STOCK_FUND_SYMBOL)
stock_div_df = pd.DataFrame(stock_ticker.dividends)
# Give the value column a name
stock_div_df.columns = ['Dividend']
# stock_div_df['Dividend'] = float(stock_div_df['Dividend'])
pd.to_numeric(stock_div_df['Dividend'], errors='coerce')
# Examine results
print(STOCK_FUND_NAME, '-', STOCK_FUND_SYMBOL)
print(stock_div_df.head())
High Yield Bond Fund - VWEAX
Dividend
Date
2003-03-31 0.04
2003-04-30 0.04
2003-05-30 0.04
2003-06-30 0.04
2003-07-31 0.04
In [14]:
# Do this so 3 decimal places will be displayed. Will convert back to float during calculations
stock_div_df['Dividend'] = stock_div_df['Dividend'].map('{:,.3f}'.format)
In [15]:
# Get the dividends within the study date range using the date "index".
stock_div_df = stock_div_df[(stock_div_df.index >= START_DATE) & (stock_div_df.index <= END_DATE)]
In [16]:
# Resample SPY dividend data to get the ending date for each quarter so it can be matched up with the
# price change dataframe. I don't need the actual dividend payout date.
stock_quarter_dividend_df = pd.DataFrame(stock_div_df.resample('Q')['Dividend'].max())
# Add a "Quarter" column. This is a calendar quarter. 1st quarter starts Jan. 1, ends March 31.
# spy_quarter_dividend_df['Quarter'] = pd.PeriodIndex(spy_quarter_dividend_df.index, freq = 'Q-Dec')
# Replace NaN with zero
stock_quarter_dividend_df['Dividend'].fillna(0, inplace=True)
In [17]:
# Examine the results
stock_quarter_dividend_df.head(10)
Out[17]:
| Dividend | |
|---|---|
| Date | |
| 2003-03-31 | 0.042 |
| 2003-06-30 | 0.041 |
| 2003-09-30 | 0.041 |
| 2003-12-31 | 0.040 |
| 2004-03-31 | 0.040 |
| 2004-06-30 | 0.040 |
| 2004-09-30 | 0.040 |
| 2004-12-31 | 0.040 |
| 2005-03-31 | 0.040 |
| 2005-06-30 | 0.038 |
In [18]:
### Set the initial portfolio allocation amounts.
# Find the number of shares of SPY that can be purchased on January 1, 1994
# at the starting price.
STARTING_STABLE_INVESTMENT = STARTING_INVESTMENT * STABLE_FUND_PROPORTION
STARTING_STOCK_INVESTMENT = STARTING_INVESTMENT - STARTING_STABLE_INVESTMENT
# Get the starting price for SPY
STARTING_PRICE_STOCK = float(stock_quarter_price_df.iloc[0, 2]) # Opening adjusted price first Q.
STARTING_SHARES_STOCK = round(STARTING_STOCK_INVESTMENT / STARTING_PRICE_STOCK, 2)
### TODO: Find out if the number of shares should be changed due to splits in SPY.
# Calculate the market value of the shares at the end of each quarter based on the closing price.
# stock_close_price_df = pd.DataFrame(stock_quarter_price_df['last'])
# stock_close_price_df.columns = ["ClosePrice"] # Change column name
# Do this so 3 decimal places will be displayed. Will convert back to float during calculations
# stock_quarter_price_df["Open"] = stock_quarter_price_df["Open"].map('{:,.3f}'.format)
print(stock_quarter_price_df.shape)
print("Stock starting price:", STARTING_PRICE_STOCK)
print("Stable fund proportion:", STABLE_FUND_PROPORTION)
print("Starting stable fund amunt", STARTING_STABLE_INVESTMENT)
print("Starting stock investment", STARTING_STOCK_INVESTMENT)
print('{0} ${1} is {2}'.format('Number of stock shares purchased with:', STARTING_STOCK_INVESTMENT, STARTING_SHARES_STOCK))
print("")
print(stock_quarter_price_df.head())
print(stock_quarter_price_df.tail())
(76, 4)
Stock starting price: 1.9222804307937622
Stable fund proportion: 0.333
Starting stable fund amunt 333000.0
Starting stock investment 667000.0
Number of stock shares purchased with: $667000.0 is 346983.71
ActualOpen ActualClose AdjustedOpen AdjustedClose
Date
2002-03-31 6.29 6.24 1.92 1.91
2002-06-30 6.23 6.00 1.90 1.83
2002-09-30 5.99 5.68 1.83 1.74
2002-12-31 5.67 5.88 1.73 1.80
2003-03-31 5.87 6.01 1.79 1.85
ActualOpen ActualClose AdjustedOpen AdjustedClose
Date
2019-12-31 5.89 5.96 5.51 5.65
2020-03-31 5.97 5.26 5.66 5.05
2020-06-30 5.21 5.61 5.01 5.46
2020-09-30 5.63 5.78 5.48 5.69
2020-12-31 5.79 5.98 5.70 5.94
In [19]:
### MERGE the closing price and dividend dataframes to create the framework for the final dataset.
# There is one row per quarter.
portfolio_value_df = pd.merge(stock_quarter_price_df, stock_quarter_dividend_df, how = 'left', on = 'Date')
# Replace NaN with zero
portfolio_value_df['Dividend'].fillna(0, inplace=True)
# The following turns the prices into "objects" with 3 decimal places.
# These will need to be cast as numbers before using them in calculations.
portfolio_value_df['ActualOpen'] = portfolio_value_df['ActualOpen'].map('{:,.3f}'.format)
portfolio_value_df['ActualClose'] = portfolio_value_df['ActualClose'].map('{:,.3f}'.format)
portfolio_value_df['AdjustedOpen'] = portfolio_value_df['AdjustedOpen'].map('{:,.3f}'.format)
portfolio_value_df['AdjustedClose'] = portfolio_value_df['AdjustedClose'].map('{:,.3f}'.format)
print(portfolio_value_df.head())
print(portfolio_value_df.tail())
print(portfolio_value_df.info())
portfolio_value_df
ActualOpen ActualClose AdjustedOpen AdjustedClose Dividend
Date
2002-03-31 6.290 6.240 1.922 1.907 0
2002-06-30 6.230 6.000 1.904 1.834 0
2002-09-30 5.990 5.680 1.831 1.736 0
2002-12-31 5.670 5.880 1.733 1.797 0
2003-03-31 5.870 6.010 1.794 1.850 0.042
ActualOpen ActualClose AdjustedOpen AdjustedClose Dividend
Date
2019-12-31 5.890 5.960 5.513 5.652 0.026
2020-03-31 5.970 5.260 5.661 5.054 0.026
2020-06-30 5.210 5.610 5.006 5.461 0.025
2020-09-30 5.630 5.780 5.480 5.694 0.024
2020-12-31 5.790 5.980 5.704 5.936 0.022
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 76 entries, 2002-03-31 to 2020-12-31
Data columns (total 5 columns):
ActualOpen 76 non-null object
ActualClose 76 non-null object
AdjustedOpen 76 non-null object
AdjustedClose 76 non-null object
Dividend 76 non-null object
dtypes: object(5)
memory usage: 3.6+ KB
None
Out[19]:
| ActualOpen | ActualClose | AdjustedOpen | AdjustedClose | Dividend | |
|---|---|---|---|---|---|
| Date | |||||
| 2002-03-31 | 6.290 | 6.240 | 1.922 | 1.907 | 0 |
| 2002-06-30 | 6.230 | 6.000 | 1.904 | 1.834 | 0 |
| 2002-09-30 | 5.990 | 5.680 | 1.831 | 1.736 | 0 |
| 2002-12-31 | 5.670 | 5.880 | 1.733 | 1.797 | 0 |
| 2003-03-31 | 5.870 | 6.010 | 1.794 | 1.850 | 0.042 |
| 2003-06-30 | 6.020 | 6.250 | 1.853 | 1.961 | 0.041 |
| 2003-09-30 | 6.240 | 6.210 | 1.958 | 1.987 | 0.041 |
| 2003-12-31 | 6.220 | 6.380 | 1.990 | 2.080 | 0.040 |
| 2004-03-31 | 6.380 | 6.390 | 2.080 | 2.122 | 0.040 |
| 2004-06-30 | 6.390 | 6.210 | 2.122 | 2.101 | 0.040 |
| 2004-09-30 | 6.210 | 6.360 | 2.101 | 2.192 | 0.040 |
| 2004-12-31 | 6.360 | 6.440 | 2.192 | 2.261 | 0.040 |
| 2005-03-31 | 6.440 | 6.220 | 2.261 | 2.223 | 0.040 |
| 2005-06-30 | 6.220 | 6.270 | 2.223 | 2.282 | 0.038 |
| 2005-09-30 | 6.270 | 6.200 | 2.282 | 2.297 | 0.038 |
| 2005-12-31 | 6.200 | 6.170 | 2.297 | 2.328 | 0.038 |
| 2006-03-31 | 6.170 | 6.160 | 2.328 | 2.366 | 0.038 |
| 2006-06-30 | 6.160 | 6.020 | 2.366 | 2.355 | 0.038 |
| 2006-09-30 | 6.020 | 6.120 | 2.355 | 2.439 | 0.038 |
| 2006-12-31 | 6.120 | 6.220 | 2.439 | 2.525 | 0.038 |
| 2007-03-31 | 6.230 | 6.250 | 2.529 | 2.583 | 0.039 |
| 2007-06-30 | 6.250 | 6.110 | 2.583 | 2.573 | 0.039 |
| 2007-09-30 | 6.110 | 6.070 | 2.573 | 2.606 | 0.039 |
| 2007-12-31 | 6.070 | 5.900 | 2.606 | 2.582 | 0.038 |
| 2008-03-31 | 5.900 | 5.650 | 2.582 | 2.521 | 0.038 |
| 2008-06-30 | 5.650 | 5.560 | 2.521 | 2.530 | 0.038 |
| 2008-09-30 | 5.550 | 5.090 | 2.526 | 2.365 | 0.037 |
| 2008-12-31 | 5.050 | 4.270 | 2.346 | 2.036 | 0.037 |
| 2009-03-31 | 4.280 | 4.370 | 2.041 | 2.132 | 0.035 |
| 2009-06-30 | 4.370 | 4.900 | 2.132 | 2.441 | 0.034 |
| 2009-09-30 | 4.910 | 5.310 | 2.446 | 2.699 | 0.036 |
| 2009-12-31 | 5.300 | 5.470 | 2.694 | 2.839 | 0.039 |
| 2010-03-31 | 5.480 | 5.560 | 2.844 | 2.942 | 0.038 |
| 2010-06-30 | 5.560 | 5.450 | 2.942 | 2.924 | 0.038 |
| 2010-09-30 | 5.440 | 5.700 | 2.918 | 3.117 | 0.037 |
| 2010-12-31 | 5.710 | 5.700 | 3.123 | 3.175 | 0.036 |
| 2011-03-31 | 5.710 | 5.800 | 3.181 | 3.290 | 0.037 |
| 2011-06-30 | 5.810 | 5.770 | 3.296 | 3.332 | 0.035 |
| 2011-09-30 | 5.780 | 5.460 | 3.338 | 3.211 | 0.035 |
| 2011-12-31 | 5.420 | 5.690 | 3.188 | 3.408 | 0.035 |
| 2012-03-31 | 5.710 | 5.840 | 3.420 | 3.557 | 0.034 |
| 2012-06-30 | 5.850 | 5.870 | 3.563 | 3.634 | 0.032 |
| 2012-09-30 | 5.880 | 6.020 | 3.640 | 3.787 | 0.032 |
| 2012-12-31 | 6.030 | 6.110 | 3.793 | 3.904 | 0.032 |
| 2013-03-31 | 6.120 | 6.130 | 3.910 | 3.975 | 0.031 |
| 2013-06-30 | 6.130 | 5.910 | 3.975 | 3.888 | 0.030 |
| 2013-09-30 | 5.920 | 5.930 | 3.895 | 3.960 | 0.030 |
| 2013-12-31 | 5.930 | 6.030 | 3.960 | 4.086 | 0.030 |
| 2014-03-31 | 6.030 | 6.110 | 4.086 | 4.200 | 0.030 |
| 2014-06-30 | 6.110 | 6.160 | 4.200 | 4.293 | 0.029 |
| 2014-09-30 | 6.160 | 5.990 | 4.293 | 4.235 | 0.029 |
| 2014-12-31 | 6.000 | 5.970 | 4.242 | 4.280 | 0.029 |
| 2015-03-31 | 5.970 | 6.000 | 4.280 | 4.361 | 0.029 |
| 2015-06-30 | 6.000 | 5.910 | 4.361 | 4.355 | 0.028 |
| 2015-09-30 | 5.920 | 5.670 | 4.363 | 4.239 | 0.028 |
| 2015-12-31 | 5.660 | 5.540 | 4.231 | 4.204 | 0.029 |
| 2016-03-31 | 5.520 | 5.590 | 4.188 | 4.301 | 0.026 |
| 2016-06-30 | 5.590 | 5.700 | 4.301 | 4.448 | 0.028 |
| 2016-09-30 | 5.720 | 5.870 | 4.463 | 4.644 | 0.027 |
| 2016-12-31 | 5.870 | 5.830 | 4.644 | 4.679 | 0.029 |
| 2017-03-31 | 5.840 | 5.880 | 4.687 | 4.785 | 0.029 |
| 2017-06-30 | 5.880 | 5.950 | 4.785 | 4.909 | 0.027 |
| 2017-09-30 | 5.950 | 5.990 | 4.909 | 5.009 | 0.027 |
| 2017-12-31 | 5.980 | 5.920 | 5.000 | 5.016 | 0.027 |
| 2018-03-31 | 5.920 | 5.760 | 5.016 | 4.945 | 0.028 |
| 2018-06-30 | 5.750 | 5.700 | 4.936 | 4.964 | 0.028 |
| 2018-09-30 | 5.680 | 5.770 | 4.946 | 5.098 | 0.028 |
| 2018-12-31 | 5.780 | 5.430 | 5.107 | 4.876 | 0.031 |
| 2019-03-31 | 5.440 | 5.760 | 4.885 | 5.247 | 0.028 |
| 2019-06-30 | 5.780 | 5.860 | 5.265 | 5.412 | 0.027 |
| 2019-09-30 | 5.870 | 5.890 | 5.421 | 5.513 | 0.027 |
| 2019-12-31 | 5.890 | 5.960 | 5.513 | 5.652 | 0.026 |
| 2020-03-31 | 5.970 | 5.260 | 5.661 | 5.054 | 0.026 |
| 2020-06-30 | 5.210 | 5.610 | 5.006 | 5.461 | 0.025 |
| 2020-09-30 | 5.630 | 5.780 | 5.480 | 5.694 | 0.024 |
| 2020-12-31 | 5.790 | 5.980 | 5.704 | 5.936 | 0.022 |
In [20]:
# Build a dataframe with one row for each quarter. Use the dividends paid in each quarter to buy
# additional SPY shares at the start of the following quarter. Use the closing price of the previous quarter
# to determine the number of shares purchased. Calculate current quarter's dividends from the number
# of shares including the ones purchased from previous quarter dividend, times the current quarter dividend
# per share.
# "SharesToPurchase" are purchased the following quarter.
# "SharesToSell" are sold the following quarter. (SharesToSell are only calculated in the 4th Quarter)
# Add placeholder columns to the framework created above for calculated values next derived during a loop.
# ActualOpen from framework dataframe
# ActualClose from framework dataframe
# AdjustedOpen from framework dataframe
# AdjustedClose from framework dataframe
# Dividend from framework dataframe
portfolio_value_df["StockSharesStart"] = 0 # Number of shares before any purchases and sales during the quarter.
portfolio_value_df["StableValueStart"] = 0 # Value of the Stable Value Fund before adding paid interest
# Interest and Dividends
portfolio_value_df["StockSharesDividends"] = 0 # StockSharesStart times Dividend (current quarter)
portfolio_value_df["StableValueInterest"] = 0 # Interest received from stable fund.
portfolio_value_df["StockBuyWithDividends"] = 0 # Number of new shares to buy using paid dividends.
portfolio_value_df["StockValueAfterDividends"] = 0 # Value of stock fund after adding reinvested dividends
portfolio_value_df["StableValueAfterInterest"] = 0 # Value of stable fund after adding paid interest
# Desired After Tax Income & Required Minimum Distribution.
portfolio_value_df['Quarter'] = pd.PeriodIndex(portfolio_value_df.index, freq = 'Q-Dec') # Calendar quarter.
portfolio_value_df["RMDBasis"] = 0 # Value of portfolio that RMD based on. StableValueAfterInterest + StockValueAfterDividends
portfolio_value_df["RMDDivisor"] = 0 # divisor to determine the minimum dstribution per IRS.
portfolio_value_df["RMD"] = 0 # RMD amount to withdraw from Stable Fund
portfolio_value_df["RequiredAfterTaxIncome"] = 0 # The inflation adjusted desired after tax income.
portfolio_value_df["RequiredBeforeTaxIncome"] = 0 # Pretax amount needed to get desired after tax income.
portfolio_value_df["RMDIncomeGap"] = 0 # Gap between required income and RMD.
portfolio_value_df["RMDIncomeExcess"] = 0 # If RMD > desired income, reinvest excess in trading account.
portfolio_value_df["RMDStockSharesToSell"] = 0 # Number shares to sell to meet the RMD/Income requirement.
portfolio_value_df["StableValueAfterRMD"] = 0 # The amount in the Stable Fund after income distribution.
portfolio_value_df["StockValueAfterRMD"] = 0 # Value of the stock fund after income distribution
# Reinvest excess income.
portfolio_value_df["TradingAcctStockSharesBuy"] = 0 # Number of stock fund shares to reinvest using excess income.
portfolio_value_df["TradingAcctStockShares"] = 0 # Cummulative number of stock shares in trading account
portfolio_value_df["TradingAcctStockDividends"] = 0 # Dividends paid on previous quarter total shares
portfolio_value_df["TradingAcctReinvestmentShares"] = 0 # Shares being bought with dividends in trading account.
portfolio_value_df["TradingAcctStockSharesFinal"] = 0 # Trading fund shares after reinvesting dividends.
portfolio_value_df["TradingAcctStockValue"] = 0 # TradingAcctStockSharesFinal * AdjustedClose
# Rebalance the portfolio.
portfolio_value_df["TotalValueToRebalance"] = 0 # Rebalance around this total amount
portfolio_value_df["StableValuePctBeforeRebalance"] = 0 # Percent made up by Stable Fund brfore rebalancing.
# Positive RebalanceAmount means funds to be added to Stable Fund. Negative means funds to be removed.
portfolio_value_df["RebalanceAmount"] = 0 # Amount to change Stable Fund to maintain desired proportions
# Positive SharesToRebalance means sell stock, put proceeds in Stable. Negative means sell SPY using Stable Funds.
portfolio_value_df["SharesToRebalance"] = 0 # The number of SPY shares to buy or sell to balance the portfolio.
portfolio_value_df["SharesAfterRebalance"] = 0 # StockSharesStart+StockBuyWithDividends-RMDStockSharesToSell-SharesToRebalance-IncomeSharesToSell
portfolio_value_df["SharesValueAfterRebalance"] = 0 # SharesAfterRebalance times ClosePrice
portfolio_value_df["StableValueAfterRebalance"] = 0 # StableValueAfterInterest + RebalanceAmount
portfolio_value_df["StableValuePctAfterRebalance"] = 0 # Percent of portfolio made up by Stable Fund after rebalancing.
portfolio_value_df["PortfolioValueEnd"] = 0 # SharesValueAfterRebalance + StableValueAfterRebalance
yr_counter = 1 # counter used to identify minimum distribution divisor to use.
SharesToSell = 0
# Being verbose below for clarity...
for i in range(len(portfolio_value_df)): # Loop through quarters
Quarter = portfolio_value_df.iloc[i, 12] # Different quarters need different actions below.
if i == 0: # Update the first dataset row here. I am assuming it is a "1st Quarter".
### Use these next 5 pre-set values in further calculations
ActualOpen = float(portfolio_value_df.iloc[i, 0]) # Actual historical price start of quarter.
ActualClose = float(portfolio_value_df.iloc[i, 1]) # Actual historical price at end of quarter.
AdjustedOpen = float(portfolio_value_df.iloc[i, 2]) # Adjusted price at start of quarter.
AdjustedClose = float(portfolio_value_df.iloc[i, 3]) # Adjusted price at end of quarter.
Dividend = float(portfolio_value_df.iloc[i, 4]) # Per share dividend paid during current quarter.
### Initialize starting values
StockSharesStart = STARTING_SHARES_STOCK # The number of stock shares to start in current quarter.
StableValueStart = STARTING_STABLE_INVESTMENT # Amount in the Stable Fund to start out.
### Interest and Dividends
StockSharesDividends = Dividend * StockSharesStart # Total of dividends paid in quarter
StableValueInterest = STABLE_FUND_QUARTERLY_RATE * StableValueStart # Interest paid that quarter
StockBuyWithDividends = StockSharesDividends / ActualClose # Number of shares that could be bought with paid dividends
StockValueAfterDividends = (StockSharesStart + StockBuyWithDividends) * AdjustedClose # based on actual historical price of stock.
StableValueAfterInterest = StableValueStart + StableValueInterest # Previous stable value plus interest
### Desired After Tax Income & Required Minimum Distribution. (ONLY in 4th Q)
RMDBasis = StableValueAfterInterest + StockValueAfterDividends
RMDDivisor = 0 # divisor to determine the minimum dstribution per IRS.
RMD = 0 # RMD amount to withdraw from Stable Fund.
RequiredAfterTaxIncome = 0 # The inflation adjusted desired after tax income.
RequiredBeforeTaxIncome = 0 # Pretax amount needed to get desired after tax income.
RMDIncomeGap = 0 # Gap between required income and RMD.
RMDIncomeExcess = 0 # If RMD > desired income, reinvest excess in trading account.
RMDStockSharesToSell = 0 # Number shares to sell to meet the RMD/Income requirement.
StableValueAfterRMD = StableValueAfterInterest # The amount in the Stable Fund after income distribution.
StockValueAfterRMD = StockValueAfterDividends # Value of the stock fund after income distribution.
### Reinvesting excess RMD in external trading account.
TradingAcctStockSharesBuy = 0 # Number of stock fund shares to reinvest using excess income.
TradingAcctStockShares = 0 # Cummulative number of stock shares in trading account
TradingAcctStockDividends = 0 # Dividends paid on previous quarter total shares
TradingAcctReinvestmentShares = 0 # Shares being bought with dividends in trading account.
TradingAcctStockSharesFinal = 0 # Trading fund shares after reinvesting dividends.
TradingAcctStockValue = 0 # TradingAcctStockSharesFinal * AdjustedClose
### Rebalance the portfolio. (Done at the end of every quarter)
TotalValueToRebalance = StableValueAfterRMD + StockValueAfterRMD # Rebalance this total amount
StableValuePctBeforeRebalance = round(StableValueAfterRMD / TotalValueToRebalance, 2)
# Positive RebalanceAmount means funds to be added to Stable Fund. Negative means funds to be removed.
if StableValuePctBeforeRebalance == STABLE_FUND_PROPORTION: # No change in balance needed
RebalanceAmount = 0
SharesToRebalance = 0
elif StableValuePctBeforeRebalance < STABLE_FUND_PROPORTION: # Need to increase Stable Fund.
# Amount to increase Stable Fund:
RebalanceAmount = (STABLE_FUND_PROPORTION * TotalValueToRebalance) - StableValueAfterRMD
# Number of stock fund shares to sell
SharesToRebalance = -1 * (RebalanceAmount / AdjustedClose) # stock fund shares to sell
elif StableValuePctBeforeRebalance > STABLE_FUND_PROPORTION: # Need to reduce Stable Fund.
# Amount to reduce Stable Fund:
RebalanceAmount = (STABLE_FUND_PROPORTION * TotalValueToRebalance) - StableValueAfterRMD
# Number of SPY shares to buy:
SharesToRebalance = -1 * (RebalanceAmount / AdjustedClose)
SharesAfterRebalance = 0 # StockSharesStart+StockBuyWithDividends-RMDStockSharesToSell-SharesToRebalance-IncomeSharesToSell
### Calculate ending quarterly value of the portfolio.
SharesAfterRebalance = StockSharesStart + StockBuyWithDividends - RMDStockSharesToSell + SharesToRebalance
SharesValueAfterRebalance = AdjustedClose * SharesAfterRebalance
StableValueAfterRebalance = StableValueAfterRMD + RebalanceAmount
StableValuePctAfterRebalance = StableValueAfterRebalance / (SharesValueAfterRebalance + StableValueAfterRebalance)
PortfolioValueEnd = SharesValueAfterRebalance + StableValueAfterRebalance
### Update the dataset row with the above calculated values.
portfolio_value_df.iloc[i, 5] = StockSharesStart # Number of shares before any purchases and sales during the quarter.
portfolio_value_df.iloc[i, 6] = StableValueStart # Value of the Stable Value Fund before adding paid interest
# Interest and Dividends
portfolio_value_df.iloc[i, 7] = StockSharesDividends # StockSharesStart times Dividend (current quarter)
portfolio_value_df.iloc[i, 8] = StableValueInterest # Interest received from stable fund.
portfolio_value_df.iloc[i, 9] = StockBuyWithDividends # Number of new shares to buy using paid dividends.
portfolio_value_df.iloc[i, 10] = StockValueAfterDividends # Value of stock fund after adding reinvested dividends
portfolio_value_df.iloc[i, 11] = StableValueAfterInterest # Value of stable fund after adding paid interest
# Desired After Tax Income & Required Minimum Distribution.
# Column "12" = Calendar quarter.
portfolio_value_df.iloc[i, 13] = RMDBasis # Value of portfolio that RMD based on. StableValueAfterInterest + StockValueAfterDividends
portfolio_value_df.iloc[i, 14] = RMDDivisor # divisor to determine the minimum dstribution per IRS.
portfolio_value_df.iloc[i, 15] = RMD # RMD amount to withdraw from Stable Fund
portfolio_value_df.iloc[i, 16] = RequiredAfterTaxIncome # The inflation adjusted desired after tax income.
portfolio_value_df.iloc[i, 17] = RequiredBeforeTaxIncome # Pretax amount needed to get desired after tax income.
portfolio_value_df.iloc[i, 18] = RMDIncomeGap # Gap between required income and RMD.
portfolio_value_df.iloc[i, 19] = RMDIncomeExcess # If RMD > desired income, reinvest excess in trading account.
portfolio_value_df.iloc[i, 20] = RMDStockSharesToSell # Number shares to sell to meet the RMD/Income requirement.
portfolio_value_df.iloc[i, 21] = StableValueAfterRMD # The amount in the Stable Fund after income distribution.
portfolio_value_df.iloc[i, 22] = StockValueAfterRMD # Value of the stock fund after income distribution
# Reinvest excess income.
portfolio_value_df.iloc[i, 23] = TradingAcctStockSharesBuy # Number of stock fund shares to reinvest using excess income.
portfolio_value_df.iloc[i, 24] = TradingAcctStockShares # Cummulative number of stock shares in trading account
portfolio_value_df.iloc[i, 25] = TradingAcctStockDividends # Dividends paid on previous quarter total shares
portfolio_value_df.iloc[i, 26] = TradingAcctReinvestmentShares # Shares being bought with dividends in trading account.
portfolio_value_df.iloc[i, 27] = TradingAcctStockSharesFinal # Trading fund shares after reinvesting dividends.
portfolio_value_df.iloc[i, 28] = TradingAcctStockValue # TradingAcctStockSharesFinal * AdjustedClose
# Rebalance the portfolio.
portfolio_value_df.iloc[i, 29] = TotalValueToRebalance # Rebalance around this total amount
portfolio_value_df.iloc[i, 30] = StableValuePctBeforeRebalance # Percent made up by Stable Fund brfore rebalancing.
# Positive RebalanceAmount means funds to be added to Stable Fund. Negative means funds to be removed.
portfolio_value_df.iloc[i, 31] = RebalanceAmount # Amount to change Stable Fund to maintain desired proportions
# Positive SharesToRebalance means sell stock, put proceeds in Stable. Negative means sell SPY using Stable Funds.
portfolio_value_df.iloc[i, 32] = SharesToRebalance # The number of SPY shares to buy or sell to balance the portfolio.
portfolio_value_df.iloc[i, 33] = SharesAfterRebalance # StockSharesStart+StockBuyWithDividends-RMDStockSharesToSell-SharesToRebalance-IncomeSharesToSell
portfolio_value_df.iloc[i, 34] = SharesValueAfterRebalance # SharesAfterRebalance times ClosePrice
portfolio_value_df.iloc[i, 35] = StableValueAfterRebalance # StableValueAfterInterest + RebalanceAmount
portfolio_value_df.iloc[i, 36] = StableValuePctAfterRebalance # Percent of portfolio made up by Stable Fund after rebalancing.
portfolio_value_df.iloc[i, 37] = PortfolioValueEnd # SharesValueAfterRebalance + StableValueAfterRebalance
if i > 0: # After the first row is updated
### Update 1st, 2nd or 3rd Qtr.
if Quarter.quarter == 1 or Quarter.quarter == 2 or Quarter.quarter == 3:
### Use these next 5 pre-set values in further calculations
ActualOpen = float(portfolio_value_df.iloc[i, 0]) # Actual historical price start of quarter.
ActualClose = float(portfolio_value_df.iloc[i, 1]) # Actual historical price at end of quarter.
AdjustedOpen = float(portfolio_value_df.iloc[i, 2]) # Adjusted price at start of quarter.
AdjustedClose = float(portfolio_value_df.iloc[i, 3]) # Adjusted price at end of quarter.
Dividend = float(portfolio_value_df.iloc[i, 4]) # Per share dividend paid during current quarter.
### Initialize starting values
PreviousQuarterEndingShares = portfolio_value_df.iloc[i - 1, 33]
StockSharesStart = PreviousQuarterEndingShares # Prev. qtr. ending number of shares
PreviousQuarterEndingStable = portfolio_value_df.iloc[i - 1, 35]
StableValueStart = PreviousQuarterEndingStable # Amount in Stable Fund end of previous Q.
### Interest and Dividends
StockSharesDividends = Dividend * StockSharesStart # Total of dividends paid in quarter
StockBuyWithDividends = StockSharesDividends / ActualClose # Number of shares that could be bought with paid dividends
# based on actual historical price of stock.
StockValueAfterDividends = (StockSharesStart + StockBuyWithDividends) * AdjustedClose
StableValueInterest = STABLE_FUND_QUARTERLY_RATE * StableValueStart
StableValueAfterInterest = StableValueStart + StableValueInterest
### Desired After Tax Income & Required Minimum Distribution. (ONLY in 4th Q)
RMDBasis = StableValueAfterInterest + StockValueAfterDividends
RMDDivisor = 0 # divisor to determine the minimum dstribution per IRS.
RMD = 0 # RMD amount to withdraw from Stable Fund.
RequiredAfterTaxIncome = 0 # The inflation adjusted desired after tax income.
RequiredBeforeTaxIncome = 0 # Pretax amount needed to get desired after tax income.
RMDIncomeGap = 0 # Gap between required income and RMD.
RMDIncomeExcess = 0 # If RMD > desired income, reinvest excess in trading account.
RMDStockSharesToSell = 0 # Number shares to sell to meet the RMD/Income requirement.
StableValueAfterRMD = StableValueAfterInterest # The amount in the Stable Fund after income distribution.
StockValueAfterRMD = StockValueAfterDividends # Value of the stock fund after income distribution.
### Reinvesting excess RMD in external trading account.
TradingAcctStockSharesBuy = 0 # Number of stock fund shares to reinvest using excess income.
TradingAcctStockShares = 0 # Cummulative number of stock shares in trading account
TradingAcctStockDividends = 0 # Dividends paid on previous quarter total shares
TradingAcctReinvestmentShares = 0 # Shares being bought with dividends in trading account.
TradingAcctStockSharesFinal = 0 # Trading fund shares after reinvesting dividends.
TradingAcctStockValue = 0 # TradingAcctStockSharesFinal * AdjustedClose
### Rebalance the portfolio. (Done at the end of every quarter)
TotalValueToRebalance = StableValueAfterRMD + StockValueAfterRMD # Rebalance this total amount
StableValuePctBeforeRebalance = round(StableValueAfterRMD / TotalValueToRebalance, 2)
# Positive RebalanceAmount means funds to be added to Stable Fund. Negative means funds to be removed.
if StableValuePctBeforeRebalance == STABLE_FUND_PROPORTION: # No change in balance needed
RebalanceAmount = 0
SharesToRebalance = 0
elif StableValuePctBeforeRebalance < STABLE_FUND_PROPORTION: # Need to increase Stable Fund.
# Amount to increase Stable Fund:
RebalanceAmount = (STABLE_FUND_PROPORTION * TotalValueToRebalance) - StableValueAfterRMD
# Number of stock fund shares to sell
SharesToRebalance = -1 * (RebalanceAmount / AdjustedClose) # stock fund shares to sell
elif StableValuePctBeforeRebalance > STABLE_FUND_PROPORTION: # Need to reduce Stable Fund.
# Amount to reduce Stable Fund:
RebalanceAmount = (STABLE_FUND_PROPORTION * TotalValueToRebalance) - StableValueAfterRMD
# Number of SPY shares to buy:
SharesToRebalance = -1 * (RebalanceAmount / AdjustedClose)
SharesAfterRebalance = 0 # StockSharesStart+StockBuyWithDividends-RMDStockSharesToSell-SharesToRebalance-IncomeSharesToSell
### Calculate ending quarterly value of the portfolio.
SharesAfterRebalance = StockSharesStart + StockBuyWithDividends - RMDStockSharesToSell + SharesToRebalance
SharesValueAfterRebalance = AdjustedClose * SharesAfterRebalance
StableValueAfterRebalance = StableValueAfterRMD + RebalanceAmount
StableValuePctAfterRebalance = StableValueAfterRebalance / (SharesValueAfterRebalance + StableValueAfterRebalance)
PortfolioValueEnd = SharesValueAfterRebalance + StableValueAfterRebalance
### Update the dataset row with the above calculated values.
portfolio_value_df.iloc[i, 5] = StockSharesStart # Number of shares before any purchases and sales during the quarter.
portfolio_value_df.iloc[i, 6] = StableValueStart # Value of the Stable Value Fund before adding paid interest
# Interest and Dividends
portfolio_value_df.iloc[i, 7] = StockSharesDividends # StockSharesStart times Dividend (current quarter)
portfolio_value_df.iloc[i, 8] = StableValueInterest # Interest received from stable fund.
portfolio_value_df.iloc[i, 9] = StockBuyWithDividends # Number of new shares to buy using paid dividends.
portfolio_value_df.iloc[i, 10] = StockValueAfterDividends # Value of stock fund after adding reinvested dividends
portfolio_value_df.iloc[i, 11] = StableValueAfterInterest # Value of stable fund after adding paid interest
# Desired After Tax Income & Required Minimum Distribution.
# Column "12" = Calendar quarter.
portfolio_value_df.iloc[i, 13] = RMDBasis # Value of portfolio that RMD based on. StableValueAfterInterest + StockValueAfterDividends
portfolio_value_df.iloc[i, 14] = RMDDivisor # divisor to determine the minimum dstribution per IRS.
portfolio_value_df.iloc[i, 15] = RMD # RMD amount to withdraw from Stable Fund
portfolio_value_df.iloc[i, 16] = RequiredAfterTaxIncome # The inflation adjusted desired after tax income.
portfolio_value_df.iloc[i, 17] = RequiredBeforeTaxIncome # Pretax amount needed to get desired after tax income.
portfolio_value_df.iloc[i, 18] = RMDIncomeGap # Gap between required income and RMD.
portfolio_value_df.iloc[i, 19] = RMDIncomeExcess # If RMD > desired income, reinvest excess in trading account.
portfolio_value_df.iloc[i, 20] = RMDStockSharesToSell # Number shares to sell to meet the RMD/Income requirement.
portfolio_value_df.iloc[i, 21] = StableValueAfterRMD # The amount in the Stable Fund after income distribution.
portfolio_value_df.iloc[i, 22] = StockValueAfterRMD # Value of the stock fund after income distribution
# Reinvest excess income.
portfolio_value_df.iloc[i, 23] = TradingAcctStockSharesBuy # Number of stock fund shares to reinvest using excess income.
portfolio_value_df.iloc[i, 24] = TradingAcctStockShares # Cummulative number of stock shares in trading account
portfolio_value_df.iloc[i, 25] = TradingAcctStockDividends # Dividends paid on previous quarter total shares
portfolio_value_df.iloc[i, 26] = TradingAcctReinvestmentShares # Shares being bought with dividends in trading account.
portfolio_value_df.iloc[i, 27] = TradingAcctStockSharesFinal # Trading fund shares after reinvesting dividends.
portfolio_value_df.iloc[i, 28] = TradingAcctStockValue # TradingAcctStockSharesFinal * AdjustedClose
# Rebalance the portfolio.
portfolio_value_df.iloc[i, 29] = TotalValueToRebalance # Rebalance around this total amount
portfolio_value_df.iloc[i, 30] = StableValuePctBeforeRebalance # Percent made up by Stable Fund brfore rebalancing.
# Positive RebalanceAmount means funds to be added to Stable Fund. Negative means funds to be removed.
portfolio_value_df.iloc[i, 31] = RebalanceAmount # Amount to change Stable Fund to maintain desired proportions
# Positive SharesToRebalance means sell stock, put proceeds in Stable. Negative means sell SPY using Stable Funds.
portfolio_value_df.iloc[i, 32] = SharesToRebalance # The number of SPY shares to buy or sell to balance the portfolio.
portfolio_value_df.iloc[i, 33] = SharesAfterRebalance # StockSharesStart+StockBuyWithDividends-RMDStockSharesToSell-SharesToRebalance-IncomeSharesToSell
portfolio_value_df.iloc[i, 34] = SharesValueAfterRebalance # SharesAfterRebalance times ClosePrice
portfolio_value_df.iloc[i, 35] = StableValueAfterRebalance # StableValueAfterInterest + RebalanceAmount
portfolio_value_df.iloc[i, 36] = StableValuePctAfterRebalance # Percent of portfolio made up by Stable Fund after rebalancing.
portfolio_value_df.iloc[i, 37] = PortfolioValueEnd # SharesValueAfterRebalance + StableValueAfterRebalance
if Quarter.quarter == 4: # Need to make minimum distribution at the end of the year.
### Use these next 5 pre-set values in further calculations
ActualOpen = float(portfolio_value_df.iloc[i, 0]) # Actual historical price start of quarter.
ActualClose = float(portfolio_value_df.iloc[i, 1]) # Actual historical price at end of quarter.
AdjustedOpen = float(portfolio_value_df.iloc[i, 2]) # Adjusted price at start of quarter.
AdjustedClose = float(portfolio_value_df.iloc[i, 3]) # Adjusted price at end of quarter.
Dividend = float(portfolio_value_df.iloc[i, 4]) # Per share dividend paid during current quarter.
### Initialize starting values
PreviousQuarterEndingShares = portfolio_value_df.iloc[i - 1, 33]
StockSharesStart = PreviousQuarterEndingShares # Prev. qtr. ending number of shares
PreviousQuarterEndingStable = portfolio_value_df.iloc[i - 1, 35]
StableValueStart = PreviousQuarterEndingStable # Amount in Stable Fund end of previous Q.
### Interest and Dividends
StockSharesDividends = Dividend * StockSharesStart # Total of dividends paid in quarter
StockBuyWithDividends = StockSharesDividends / ActualClose # Number of shares that could be bought with paid dividends
# based on actual historical price of stock.
StockValueAfterDividends = (StockSharesStart + StockBuyWithDividends) * AdjustedClose
StableValueInterest = STABLE_FUND_QUARTERLY_RATE * StableValueStart
StableValueAfterInterest = StableValueStart + StableValueInterest
### Desired After Tax Income & Required Minimum Distribution. (ONLY in 4th Q)
RMDBasis = StableValueAfterInterest + StockValueAfterDividends
RMDDivisor = rmd_df.iloc[yr_counter]["Divisor"] # divisor for IRS required distributions.
RMD = RMDBasis / RMDDivisor # RMD amount to withdraw from Stable Fund.
RequiredAfterTaxIncome = AFTER_TAX_INCOME * ((1 + INFLATION_RATE) ** (yr_counter - 1)) # The inflation adjusted desired after tax income.
RequiredBeforeTaxIncome = BEFORE_TAX_INCOME * ((1 + INFLATION_RATE) ** (yr_counter - 1)) # Pretax amount needed to get desired after tax income.
# Difference between required income and RMD
if RequiredBeforeTaxIncome == RMD: # RMD exactly equals the required income
RMDIncomeExcess = 0
RMDIncomeGap = 0
elif RequiredBeforeTaxIncome > RMD: # desired income exceeds RMD
RMDIncomeGap = RequiredBeforeTaxIncome - RMD # will withdraw this additional amount
RMDIncomeExcess = 0
elif RMD > RequiredBeforeTaxIncome: # RMD exceeds desired income
RMDIncomeGap = 0
RMDIncomeExcess = RMD - RequiredBeforeTaxIncome # will invest this amount in trading account
RMDStockSharesToSell = 0 # Number shares to sell to meet the RMD/Income requirement.
StableValueAfterRMD = StableValueAfterInterest - RMD - RMDIncomeGap # The amount in the Stable Fund after income distribution.
StockValueAfterRMD = StockValueAfterDividends # Value of the stock fund after income distribution.
### Reinvesting excess RMD in external trading account.
if RMDIncomeExcess > 0:
TradingAcctStockSharesBuy = RMDIncomeExcess / AdjustedClose # Number of stock fund shares to reinvest using excess income.
if i > 3: # if quarter is not the first 4th qrt.
TradingAcctStockShares = portfolio_value_df.iloc[i - 4, 24] + TradingAcctStockSharesBuy # Cummulative number of stock shares in trading account
# Dividends paid on previous quarter total shares
TradingAcctStockDividends = portfolio_value_df.iloc[i - 4, 24] * Dividend
# Additional shares being bought with the dividends
TradingAcctReinvestmentShares = TradingAcctStockDividends / AdjustedClose
else: # This is the first 4th qrt.
TradingAcctStockShares = TradingAcctStockSharesBuy
TradingAcctStockDividends = 0 # No prior 4th qrt to get dividends from
TradingAcctReinvestmentShares = 0 # No new shares from dividends possible
TradingAcctStockSharesFinal = TradingAcctStockShares + TradingAcctReinvestmentShares # Trading fund shares after reinvesting dividends.
TradingAcctStockValue = TradingAcctStockSharesFinal * AdjustedClose # TradingAcctStockSharesFinal * AdjustedClose
### Rebalance the portfolio. (Done at the end of every quarter)
TotalValueToRebalance = StableValueAfterRMD + StockValueAfterRMD # Rebalance this total amount
StableValuePctBeforeRebalance = round(StableValueAfterRMD / TotalValueToRebalance, 2)
# Positive RebalanceAmount means funds to be added to Stable Fund. Negative means funds to be removed.
if StableValuePctBeforeRebalance == STABLE_FUND_PROPORTION: # No change in balance needed
RebalanceAmount = 0
SharesToRebalance = 0
elif StableValuePctBeforeRebalance < STABLE_FUND_PROPORTION: # Need to increase Stable Fund.
# Amount to increase Stable Fund:
RebalanceAmount = (STABLE_FUND_PROPORTION * TotalValueToRebalance) - StableValueAfterRMD
# Number of stock fund shares to sell
SharesToRebalance = -1 * (RebalanceAmount / AdjustedClose) # stock fund shares to sell
elif StableValuePctBeforeRebalance > STABLE_FUND_PROPORTION: # Need to reduce Stable Fund.
# Amount to reduce Stable Fund:
RebalanceAmount = (STABLE_FUND_PROPORTION * TotalValueToRebalance) - StableValueAfterRMD
# Number of SPY shares to buy:
SharesToRebalance = -1 * (RebalanceAmount / AdjustedClose)
SharesAfterRebalance = 0 # StockSharesStart+StockBuyWithDividends-RMDStockSharesToSell-SharesToRebalance-IncomeSharesToSell
### Calculate ending quarterly value of the portfolio.
SharesAfterRebalance = StockSharesStart + StockBuyWithDividends - RMDStockSharesToSell + SharesToRebalance
SharesValueAfterRebalance = AdjustedClose * SharesAfterRebalance
StableValueAfterRebalance = StableValueAfterRMD + RebalanceAmount
StableValuePctAfterRebalance = StableValueAfterRebalance / (SharesValueAfterRebalance + StableValueAfterRebalance)
PortfolioValueEnd = SharesValueAfterRebalance + StableValueAfterRebalance
### Update the dataset row with the above calculated values.
portfolio_value_df.iloc[i, 5] = StockSharesStart # Number of shares before any purchases and sales during the quarter.
portfolio_value_df.iloc[i, 6] = StableValueStart # Value of the Stable Value Fund before adding paid interest
# Interest and Dividends
portfolio_value_df.iloc[i, 7] = StockSharesDividends # StockSharesStart times Dividend (current quarter)
portfolio_value_df.iloc[i, 8] = StableValueInterest # Interest received from stable fund.
portfolio_value_df.iloc[i, 9] = StockBuyWithDividends # Number of new shares to buy using paid dividends.
portfolio_value_df.iloc[i, 10] = StockValueAfterDividends # Value of stock fund after adding reinvested dividends
portfolio_value_df.iloc[i, 11] = StableValueAfterInterest # Value of stable fund after adding paid interest
# Desired After Tax Income & Required Minimum Distribution.
# Column "12" = Calendar quarter.
portfolio_value_df.iloc[i, 13] = RMDBasis # Value of portfolio that RMD based on. StableValueAfterInterest + StockValueAfterDividends
portfolio_value_df.iloc[i, 14] = RMDDivisor # divisor to determine the minimum dstribution per IRS.
portfolio_value_df.iloc[i, 15] = RMD # RMD amount to withdraw from Stable Fund
portfolio_value_df.iloc[i, 16] = RequiredAfterTaxIncome # The inflation adjusted desired after tax income.
portfolio_value_df.iloc[i, 17] = RequiredBeforeTaxIncome # Pretax amount needed to get desired after tax income.
portfolio_value_df.iloc[i, 18] = RMDIncomeGap # Gap between required income and RMD.
portfolio_value_df.iloc[i, 19] = RMDIncomeExcess # If RMD > desired income, reinvest excess in trading account.
portfolio_value_df.iloc[i, 20] = RMDStockSharesToSell # Number shares to sell to meet the RMD/Income requirement.
portfolio_value_df.iloc[i, 21] = StableValueAfterRMD # The amount in the Stable Fund after income distribution.
portfolio_value_df.iloc[i, 22] = StockValueAfterRMD # Value of the stock fund after income distribution
# Reinvest excess income.
portfolio_value_df.iloc[i, 23] = TradingAcctStockSharesBuy # Number of stock fund shares to reinvest using excess income.
portfolio_value_df.iloc[i, 24] = TradingAcctStockShares # Cummulative number of stock shares in trading account
portfolio_value_df.iloc[i, 25] = TradingAcctStockDividends # Dividends paid on previous quarter total shares
portfolio_value_df.iloc[i, 26] = TradingAcctReinvestmentShares # Shares being bought with dividends in trading account.
portfolio_value_df.iloc[i, 27] = TradingAcctStockSharesFinal # Trading fund shares after reinvesting dividends.
portfolio_value_df.iloc[i, 28] = TradingAcctStockValue # TradingAcctStockSharesFinal * AdjustedClose
# Rebalance the portfolio.
portfolio_value_df.iloc[i, 29] = TotalValueToRebalance # Rebalance around this total amount
portfolio_value_df.iloc[i, 30] = StableValuePctBeforeRebalance # Percent made up by Stable Fund brfore rebalancing.
# Positive RebalanceAmount means funds to be added to Stable Fund. Negative means funds to be removed.
portfolio_value_df.iloc[i, 31] = RebalanceAmount # Amount to change Stable Fund to maintain desired proportions
# Positive SharesToRebalance means sell stock, put proceeds in Stable. Negative means sell SPY using Stable Funds.
portfolio_value_df.iloc[i, 32] = SharesToRebalance # The number of SPY shares to buy or sell to balance the portfolio.
portfolio_value_df.iloc[i, 33] = SharesAfterRebalance # StockSharesStart+StockBuyWithDividends-RMDStockSharesToSell-SharesToRebalance-IncomeSharesToSell
portfolio_value_df.iloc[i, 34] = SharesValueAfterRebalance # SharesAfterRebalance times ClosePrice
portfolio_value_df.iloc[i, 35] = StableValueAfterRebalance # StableValueAfterInterest + RebalanceAmount
portfolio_value_df.iloc[i, 36] = StableValuePctAfterRebalance # Percent of portfolio made up by Stable Fund after rebalancing.
portfolio_value_df.iloc[i, 37] = PortfolioValueEnd # SharesValueAfterRebalance + StableValueAfterRebalance
yr_counter += 1
if yr_counter >= len(rmd_df): break
# if Quarter.quarter == 1: # Need to deduct mimimum distribution shares sold
# 342651.87
portfolio_value_df
Out[20]:
| ActualOpen | ActualClose | AdjustedOpen | AdjustedClose | Dividend | StockSharesStart | StableValueStart | StockSharesDividends | StableValueInterest | StockBuyWithDividends | StockValueAfterDividends | StableValueAfterInterest | Quarter | RMDBasis | RMDDivisor | RMD | RequiredAfterTaxIncome | RequiredBeforeTaxIncome | RMDIncomeGap | RMDIncomeExcess | RMDStockSharesToSell | StableValueAfterRMD | StockValueAfterRMD | TradingAcctStockSharesBuy | TradingAcctStockShares | TradingAcctStockDividends | TradingAcctReinvestmentShares | TradingAcctStockSharesFinal | TradingAcctStockValue | TotalValueToRebalance | StableValuePctBeforeRebalance | RebalanceAmount | SharesToRebalance | SharesAfterRebalance | SharesValueAfterRebalance | StableValueAfterRebalance | StableValuePctAfterRebalance | PortfolioValueEnd | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Date | ||||||||||||||||||||||||||||||||||||||
| 2002-03-31 | 6.290 | 6.240 | 1.922 | 1.907 | 0 | 346983.71 | 333000.00 | 0.00 | 2497.50 | 0.00 | 661697.93 | 335497.50 | 2002Q1 | 997195.43 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 335497.50 | 661697.93 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 997195.43 | 0.34 | -3431.42 | 1799.38 | 348783.09 | 665129.36 | 332066.08 | 0.33 | 997195.43 |
| 2002-06-30 | 6.230 | 6.000 | 1.904 | 1.834 | 0 | 348783.09 | 332066.08 | 0.00 | 2490.50 | 0.00 | 639668.19 | 334556.58 | 2002Q2 | 974224.76 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 334556.58 | 639668.19 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 974224.76 | 0.34 | -10139.73 | 5528.75 | 354311.84 | 649807.92 | 324416.85 | 0.33 | 974224.76 |
| 2002-09-30 | 5.990 | 5.680 | 1.831 | 1.736 | 0 | 354311.84 | 324416.85 | 0.00 | 2433.13 | 0.00 | 615085.36 | 326849.97 | 2002Q3 | 941935.33 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 326849.97 | 615085.36 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 941935.33 | 0.35 | -13185.51 | 7595.34 | 361907.18 | 628270.87 | 313664.47 | 0.33 | 941935.33 |
| 2002-12-31 | 5.670 | 5.880 | 1.733 | 1.797 | 0 | 361907.18 | 313664.47 | 0.00 | 2352.48 | 0.00 | 650347.20 | 316016.95 | 2002Q4 | 966364.15 | 26.50 | 36466.57 | 40000.00 | 50000.00 | 13533.43 | 0.00 | 0 | 266016.95 | 650347.20 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 916364.15 | 0.29 | 39132.31 | -21776.47 | 340130.71 | 611214.89 | 305149.26 | 0.33 | 916364.15 |
| 2003-03-31 | 5.870 | 6.010 | 1.794 | 1.850 | 0.042 | 340130.71 | 305149.26 | 14285.49 | 2288.62 | 2376.95 | 633639.18 | 307437.88 | 2003Q1 | 941077.06 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 307437.88 | 633639.18 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 941077.06 | 0.33 | 5940.78 | -3211.23 | 339296.43 | 627698.40 | 313378.66 | 0.33 | 941077.06 |
| 2003-06-30 | 6.020 | 6.250 | 1.853 | 1.961 | 0.041 | 339296.43 | 313378.66 | 13911.15 | 2350.34 | 2225.78 | 669725.07 | 315729.00 | 2003Q2 | 985454.07 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 315729.00 | 669725.07 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 985454.07 | 0.32 | 12427.20 | -6337.18 | 335185.04 | 657297.87 | 328156.21 | 0.33 | 985454.07 |
| 2003-09-30 | 6.240 | 6.210 | 1.958 | 1.987 | 0.041 | 335185.04 | 328156.21 | 13742.59 | 2461.17 | 2212.98 | 670409.86 | 330617.38 | 2003Q3 | 1001027.24 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 330617.38 | 670409.86 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1001027.24 | 0.33 | 2724.69 | -1371.26 | 336026.76 | 667685.17 | 333342.07 | 0.33 | 1001027.24 |
| 2003-12-31 | 6.220 | 6.380 | 1.990 | 2.080 | 0.040 | 336026.76 | 333342.07 | 13441.07 | 2500.07 | 2106.75 | 703317.70 | 335842.14 | 2003Q4 | 1039159.83 | 25.60 | 40592.18 | 40800.00 | 51000.00 | 10407.82 | 0.00 | 0 | 284842.14 | 703317.70 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 988159.83 | 0.29 | 44215.09 | -21257.25 | 316876.25 | 659102.61 | 329057.22 | 0.33 | 988159.83 |
| 2004-03-31 | 6.380 | 6.390 | 2.080 | 2.122 | 0.040 | 316876.25 | 329057.22 | 12675.05 | 2467.93 | 1983.58 | 676620.56 | 331525.15 | 2004Q1 | 1008145.71 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 331525.15 | 676620.56 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1008145.71 | 0.33 | 4187.37 | -1973.31 | 316886.52 | 672433.19 | 335712.52 | 0.33 | 1008145.71 |
| 2004-06-30 | 6.390 | 6.210 | 2.122 | 2.101 | 0.040 | 316886.52 | 335712.52 | 12675.46 | 2517.84 | 2041.14 | 670067.00 | 338230.37 | 2004Q2 | 1008297.37 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 338230.37 | 670067.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1008297.37 | 0.34 | -2467.34 | 1174.37 | 320102.02 | 672534.35 | 335763.02 | 0.33 | 1008297.37 |
| 2004-09-30 | 6.210 | 6.360 | 2.101 | 2.192 | 0.040 | 320102.02 | 335763.02 | 12804.08 | 2518.22 | 2013.22 | 706076.61 | 338281.25 | 2004Q3 | 1044357.86 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 338281.25 | 706076.61 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1044357.86 | 0.32 | 9489.92 | -4329.34 | 317785.90 | 696586.69 | 347771.17 | 0.33 | 1044357.86 |
| 2004-12-31 | 6.360 | 6.440 | 2.192 | 2.261 | 0.040 | 317785.90 | 347771.17 | 12711.44 | 2608.28 | 1973.83 | 722976.74 | 350379.45 | 2004Q4 | 1073356.19 | 24.70 | 43455.72 | 41616.00 | 52020.00 | 8564.28 | 0.00 | 0 | 298359.45 | 722976.74 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1021336.19 | 0.29 | 41745.50 | -18463.29 | 301296.43 | 681231.24 | 340104.95 | 0.33 | 1021336.19 |
| 2005-03-31 | 6.440 | 6.220 | 2.261 | 2.223 | 0.040 | 301296.43 | 340104.95 | 12051.86 | 2550.79 | 1937.60 | 674089.25 | 342655.74 | 2005Q1 | 1016744.99 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 342655.74 | 674089.25 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1016744.99 | 0.34 | -4079.66 | 1835.20 | 305069.23 | 678168.91 | 338576.08 | 0.33 | 1016744.99 |
| 2005-06-30 | 6.220 | 6.270 | 2.223 | 2.282 | 0.038 | 305069.23 | 338576.08 | 11592.63 | 2539.32 | 1848.90 | 700387.19 | 341115.40 | 2005Q2 | 1041502.59 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 341115.40 | 700387.19 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1041502.59 | 0.33 | 5704.96 | -2499.98 | 304418.16 | 694682.23 | 346820.36 | 0.33 | 1041502.59 |
| 2005-09-30 | 6.270 | 6.200 | 2.282 | 2.297 | 0.038 | 304418.16 | 346820.36 | 11567.89 | 2601.15 | 1865.79 | 703534.22 | 349421.52 | 2005Q3 | 1052955.73 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 349421.52 | 703534.22 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1052955.73 | 0.33 | 1212.74 | -527.97 | 305755.98 | 702321.48 | 350634.26 | 0.33 | 1052955.73 |
| 2005-12-31 | 6.200 | 6.170 | 2.297 | 2.328 | 0.038 | 305755.98 | 350634.26 | 11618.73 | 2629.76 | 1883.10 | 716183.77 | 353264.02 | 2005Q4 | 1069447.78 | 23.80 | 44934.78 | 42448.32 | 53060.40 | 8125.62 | 0.00 | 0 | 300203.62 | 716183.77 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1016387.38 | 0.30 | 38253.38 | -16431.87 | 291207.21 | 677930.39 | 338457.00 | 0.33 | 1016387.38 |
| 2006-03-31 | 6.170 | 6.160 | 2.328 | 2.366 | 0.038 | 291207.21 | 338457.00 | 11065.87 | 2538.43 | 1796.41 | 693246.56 | 340995.43 | 2006Q1 | 1034241.99 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 340995.43 | 693246.56 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1034241.99 | 0.33 | 3407.16 | -1440.05 | 291563.57 | 689839.41 | 344402.58 | 0.33 | 1034241.99 |
| 2006-06-30 | 6.160 | 6.020 | 2.366 | 2.355 | 0.038 | 291563.57 | 344402.58 | 11079.42 | 2583.02 | 1840.43 | 690966.43 | 346985.60 | 2006Q2 | 1037952.03 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 346985.60 | 690966.43 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1037952.03 | 0.33 | -1347.57 | 572.22 | 293976.22 | 692314.00 | 345638.03 | 0.33 | 1037952.03 |
| 2006-09-30 | 6.020 | 6.120 | 2.355 | 2.439 | 0.038 | 293976.22 | 345638.03 | 11171.10 | 2592.29 | 1825.34 | 721460.02 | 348230.31 | 2006Q3 | 1069690.33 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 348230.31 | 721460.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1069690.33 | 0.33 | 7976.57 | -3270.43 | 292531.14 | 713483.45 | 356206.88 | 0.33 | 1069690.33 |
| 2006-12-31 | 6.120 | 6.220 | 2.439 | 2.525 | 0.038 | 292531.14 | 356206.88 | 11116.18 | 2671.55 | 1787.17 | 743153.73 | 358878.43 | 2006Q4 | 1102032.16 | 22.90 | 48123.67 | 43297.29 | 54121.61 | 5997.93 | 0.00 | 0 | 304756.82 | 743153.73 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1047910.55 | 0.29 | 44197.39 | -17503.92 | 276814.39 | 698956.34 | 348954.21 | 0.33 | 1047910.55 |
| 2007-03-31 | 6.230 | 6.250 | 2.529 | 2.583 | 0.039 | 276814.39 | 348954.21 | 10795.76 | 2617.16 | 1727.32 | 719473.24 | 351571.37 | 2007Q1 | 1071044.61 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 351571.37 | 719473.24 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1071044.61 | 0.33 | 5086.49 | -1969.22 | 276572.50 | 714386.76 | 356657.86 | 0.33 | 1071044.61 |
| 2007-06-30 | 6.250 | 6.110 | 2.583 | 2.573 | 0.039 | 276572.50 | 356657.86 | 10786.33 | 2674.93 | 1765.36 | 716163.29 | 359332.79 | 2007Q2 | 1075496.08 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 359332.79 | 716163.29 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1075496.08 | 0.33 | -1192.59 | 463.50 | 278801.36 | 717355.89 | 358140.20 | 0.33 | 1075496.08 |
| 2007-09-30 | 6.110 | 6.070 | 2.573 | 2.606 | 0.039 | 278801.36 | 358140.20 | 10873.25 | 2686.05 | 1791.31 | 731224.49 | 360826.25 | 2007Q3 | 1092050.73 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 360826.25 | 731224.49 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1092050.73 | 0.33 | 2826.65 | -1084.67 | 279508.00 | 728397.84 | 363652.89 | 0.33 | 1092050.73 |
| 2007-12-31 | 6.070 | 5.900 | 2.606 | 2.582 | 0.038 | 279508.00 | 363652.89 | 10621.30 | 2727.40 | 1800.22 | 726337.82 | 366380.29 | 2007Q4 | 1092718.11 | 22.00 | 49669.00 | 44163.23 | 55204.04 | 5535.04 | 0.00 | 0 | 311176.25 | 726337.82 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1037514.07 | 0.30 | 34315.93 | -13290.45 | 268017.77 | 692021.88 | 345492.18 | 0.33 | 1037514.07 |
| 2008-03-31 | 5.900 | 5.650 | 2.582 | 2.521 | 0.038 | 268017.77 | 345492.18 | 10184.68 | 2591.19 | 1802.60 | 680217.15 | 348083.38 | 2008Q1 | 1028300.52 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 348083.38 | 680217.15 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1028300.52 | 0.34 | -5659.30 | 2244.86 | 272065.23 | 685876.45 | 342424.07 | 0.33 | 1028300.52 |
| 2008-06-30 | 5.650 | 5.560 | 2.521 | 2.530 | 0.038 | 272065.23 | 342424.07 | 10338.48 | 2568.18 | 1859.44 | 693029.42 | 344992.26 | 2008Q2 | 1038021.67 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 344992.26 | 693029.42 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1038021.67 | 0.33 | 668.96 | -264.41 | 273660.26 | 692360.46 | 345661.22 | 0.33 | 1038021.67 |
| 2008-09-30 | 5.550 | 5.090 | 2.526 | 2.365 | 0.037 | 273660.26 | 345661.22 | 10125.43 | 2592.46 | 1989.28 | 651911.16 | 348253.68 | 2008Q3 | 1000164.83 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 348253.68 | 651911.16 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1000164.83 | 0.35 | -15198.79 | 6426.55 | 282076.09 | 667109.94 | 333054.89 | 0.33 | 1000164.83 |
| 2008-12-31 | 5.050 | 4.270 | 2.346 | 2.036 | 0.037 | 282076.09 | 333054.89 | 10436.82 | 2497.91 | 2444.22 | 579283.34 | 335552.80 | 2008Q4 | 914836.14 | 21.20 | 43152.65 | 45046.50 | 56308.12 | 13155.47 | 0.00 | 0 | 279244.68 | 579283.34 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 858528.02 | 0.33 | 6645.15 | -3263.83 | 281256.48 | 572638.19 | 285889.83 | 0.33 | 858528.02 |
| 2009-03-31 | 4.280 | 4.370 | 2.041 | 2.132 | 0.035 | 281256.48 | 285889.83 | 9843.98 | 2144.17 | 2252.63 | 604441.41 | 288034.00 | 2009Q1 | 892475.42 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 288034.00 | 604441.41 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 892475.42 | 0.32 | 9160.31 | -4296.58 | 279212.52 | 595281.10 | 297194.31 | 0.33 | 892475.42 |
| 2009-06-30 | 4.370 | 4.900 | 2.132 | 2.441 | 0.034 | 279212.52 | 297194.31 | 9493.23 | 2228.96 | 1937.39 | 686286.95 | 299423.27 | 2009Q2 | 985710.22 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 299423.27 | 686286.95 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 985710.22 | 0.30 | 28818.23 | -11805.91 | 269344.01 | 657468.72 | 328241.50 | 0.33 | 985710.22 |
| 2009-09-30 | 4.910 | 5.310 | 2.446 | 2.699 | 0.036 | 269344.01 | 328241.50 | 9696.38 | 2461.81 | 1826.06 | 731888.01 | 330703.31 | 2009Q3 | 1062591.32 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 330703.31 | 731888.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1062591.32 | 0.31 | 23139.60 | -8573.40 | 262596.67 | 708748.41 | 353842.91 | 0.33 | 1062591.32 |
| 2009-12-31 | 5.300 | 5.470 | 2.694 | 2.839 | 0.039 | 262596.67 | 353842.91 | 10241.27 | 2653.82 | 1872.26 | 750827.30 | 356496.73 | 2009Q4 | 1107324.03 | 20.30 | 54547.98 | 45947.43 | 57434.28 | 2886.30 | 0.00 | 0 | 299062.45 | 750827.30 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1049889.75 | 0.28 | 50550.84 | -17805.86 | 246663.07 | 700276.46 | 349613.29 | 0.33 | 1049889.75 |
| 2010-03-31 | 5.480 | 5.560 | 2.844 | 2.942 | 0.038 | 246663.07 | 349613.29 | 9373.20 | 2622.10 | 1685.83 | 730642.46 | 352235.38 | 2010Q1 | 1082877.84 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 352235.38 | 730642.46 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1082877.84 | 0.33 | 8362.94 | -2842.60 | 245506.30 | 722279.52 | 360598.32 | 0.33 | 1082877.84 |
| 2010-06-30 | 5.560 | 5.450 | 2.942 | 2.924 | 0.038 | 245506.30 | 360598.32 | 9329.24 | 2704.49 | 1711.79 | 722865.67 | 363302.81 | 2010Q2 | 1086168.48 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 363302.81 | 722865.67 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1086168.48 | 0.33 | -1608.70 | 550.17 | 247768.26 | 724474.38 | 361694.10 | 0.33 | 1086168.48 |
| 2010-09-30 | 5.440 | 5.700 | 2.918 | 3.117 | 0.037 | 247768.26 | 361694.10 | 9167.43 | 2712.71 | 1608.32 | 777306.79 | 364406.81 | 2010Q3 | 1141713.60 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 364406.81 | 777306.79 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1141713.60 | 0.32 | 15783.82 | -5063.78 | 244312.79 | 761522.97 | 380190.63 | 0.33 | 1141713.60 |
| 2010-12-31 | 5.710 | 5.700 | 3.123 | 3.175 | 0.036 | 244312.79 | 380190.63 | 8795.26 | 2851.43 | 1543.03 | 780592.23 | 383042.06 | 2010Q4 | 1163634.28 | 19.50 | 59673.55 | 46866.38 | 58582.97 | 0.00 | 1090.58 | 0 | 323368.50 | 780592.23 | 343.49 | 343.49 | 0.00 | 0.00 | 343.49 | 1090.58 | 1103960.73 | 0.29 | 44250.42 | -13937.14 | 231918.68 | 736341.81 | 367618.92 | 0.33 | 1103960.73 |
| 2011-03-31 | 5.710 | 5.800 | 3.181 | 3.290 | 0.037 | 231918.68 | 367618.92 | 8580.99 | 2757.14 | 1479.48 | 767879.95 | 370376.06 | 2011Q1 | 1138256.01 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 370376.06 | 767879.95 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1138256.01 | 0.33 | 8663.19 | -2633.19 | 230764.97 | 759216.76 | 379039.25 | 0.33 | 1138256.01 |
| 2011-06-30 | 5.810 | 5.770 | 3.296 | 3.332 | 0.035 | 230764.97 | 379039.25 | 8076.77 | 2842.79 | 1399.79 | 773572.98 | 381882.05 | 2011Q2 | 1155455.03 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 381882.05 | 773572.98 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1155455.03 | 0.33 | 2884.48 | -865.69 | 231299.07 | 770688.50 | 384766.52 | 0.33 | 1155455.03 |
| 2011-09-30 | 5.780 | 5.460 | 3.338 | 3.211 | 0.035 | 231299.07 | 384766.52 | 8095.47 | 2885.75 | 1482.69 | 747462.22 | 387652.27 | 2011Q3 | 1135114.50 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 387652.27 | 747462.22 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1135114.50 | 0.34 | -9659.15 | 3008.14 | 235789.90 | 757121.37 | 377993.13 | 0.33 | 1135114.50 |
| 2011-12-31 | 5.420 | 5.690 | 3.188 | 3.408 | 0.035 | 235789.90 | 377993.13 | 8252.65 | 2834.95 | 1450.38 | 808514.86 | 380828.08 | 2011Q4 | 1189342.94 | 18.70 | 63601.23 | 47803.70 | 59754.63 | 0.00 | 3846.60 | 0 | 317226.85 | 808514.86 | 1128.70 | 1472.19 | 12.02 | 3.53 | 1475.72 | 5029.24 | 1125741.71 | 0.28 | 57645.14 | -16914.65 | 220325.62 | 750869.72 | 374871.99 | 0.33 | 1125741.71 |
| 2012-03-31 | 5.710 | 5.840 | 3.420 | 3.557 | 0.034 | 220325.62 | 374871.99 | 7491.07 | 2811.54 | 1282.72 | 788260.87 | 377683.53 | 2012Q1 | 1165944.40 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 377683.53 | 788260.87 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1165944.40 | 0.32 | 10575.95 | -2973.28 | 218635.06 | 777684.91 | 388259.48 | 0.33 | 1165944.40 |
| 2012-06-30 | 5.850 | 5.870 | 3.563 | 3.634 | 0.032 | 218635.06 | 388259.48 | 6996.32 | 2911.95 | 1191.88 | 798851.10 | 391171.43 | 2012Q2 | 1190022.53 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 391171.43 | 798851.10 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1190022.53 | 0.33 | 5106.07 | -1405.08 | 218421.86 | 793745.03 | 396277.50 | 0.33 | 1190022.53 |
| 2012-09-30 | 5.880 | 6.020 | 3.640 | 3.787 | 0.032 | 218421.86 | 396277.50 | 6989.50 | 2972.08 | 1161.05 | 831560.45 | 399249.58 | 2012Q3 | 1230810.03 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 399249.58 | 831560.45 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1230810.03 | 0.32 | 10610.16 | -2801.73 | 216781.17 | 820950.29 | 409859.74 | 0.33 | 1230810.03 |
| 2012-12-31 | 6.030 | 6.110 | 3.793 | 3.904 | 0.032 | 216781.17 | 409859.74 | 6937.00 | 3073.95 | 1135.35 | 850746.10 | 412933.69 | 2012Q4 | 1263679.79 | 17.90 | 70596.64 | 48759.78 | 60949.72 | 0.00 | 9646.92 | 0 | 342337.05 | 850746.10 | 2471.03 | 3943.22 | 47.11 | 12.07 | 3955.29 | 15441.45 | 1193083.16 | 0.29 | 54959.64 | -14077.78 | 203838.75 | 795786.46 | 397296.69 | 0.33 | 1193083.16 |
| 2013-03-31 | 6.120 | 6.130 | 3.910 | 3.975 | 0.031 | 203838.75 | 397296.69 | 6319.00 | 2979.73 | 1030.83 | 814356.57 | 400276.42 | 2013Q1 | 1214632.99 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 400276.42 | 814356.57 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1214632.99 | 0.33 | 4196.37 | -1055.69 | 203813.89 | 810160.20 | 404472.79 | 0.33 | 1214632.99 |
| 2013-06-30 | 6.130 | 5.910 | 3.975 | 3.888 | 0.030 | 203813.89 | 404472.79 | 6114.42 | 3033.55 | 1034.59 | 796450.88 | 407506.33 | 2013Q2 | 1203957.21 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 407506.33 | 796450.88 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1203957.21 | 0.34 | -6588.58 | 1694.59 | 206543.07 | 803039.46 | 400917.75 | 0.33 | 1203957.21 |
| 2013-09-30 | 5.920 | 5.930 | 3.895 | 3.960 | 0.030 | 206543.07 | 400917.75 | 6196.29 | 3006.88 | 1044.91 | 822048.39 | 403924.63 | 2013Q3 | 1225973.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 403924.63 | 822048.39 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1225973.02 | 0.33 | 4324.38 | -1092.02 | 206495.96 | 817724.00 | 408249.02 | 0.33 | 1225973.02 |
| 2013-12-31 | 5.930 | 6.030 | 3.960 | 4.086 | 0.030 | 206495.96 | 408249.02 | 6194.88 | 3061.87 | 1027.34 | 847940.22 | 411310.88 | 2013Q4 | 1259251.10 | 17.10 | 73640.42 | 49734.97 | 62168.72 | 0.00 | 11471.70 | 0 | 337670.47 | 847940.22 | 2807.56 | 6750.78 | 118.30 | 28.95 | 6779.74 | 27702.00 | 1185610.69 | 0.28 | 57137.89 | -13983.82 | 193539.48 | 790802.33 | 394808.36 | 0.33 | 1185610.69 |
| 2014-03-31 | 6.030 | 6.110 | 4.086 | 4.200 | 0.030 | 193539.48 | 394808.36 | 5806.18 | 2961.06 | 950.28 | 816856.99 | 397769.42 | 2014Q1 | 1214626.41 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 397769.42 | 816856.99 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1214626.41 | 0.33 | 6701.17 | -1595.52 | 192894.24 | 810155.81 | 404470.59 | 0.33 | 1214626.41 |
| 2014-06-30 | 6.110 | 6.160 | 4.200 | 4.293 | 0.029 | 192894.24 | 404470.59 | 5593.93 | 3033.53 | 908.11 | 831993.48 | 407504.12 | 2014Q2 | 1239497.60 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 407504.12 | 831993.48 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1239497.60 | 0.33 | 5248.58 | -1222.59 | 192579.76 | 826744.90 | 412752.70 | 0.33 | 1239497.60 |
| 2014-09-30 | 6.160 | 5.990 | 4.293 | 4.235 | 0.029 | 192579.76 | 412752.70 | 5584.81 | 3095.65 | 932.36 | 819523.80 | 415848.35 | 2014Q3 | 1235372.15 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 415848.35 | 819523.80 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1235372.15 | 0.34 | -4469.42 | 1055.35 | 194567.47 | 823993.22 | 411378.93 | 0.33 | 1235372.15 |
| 2014-12-31 | 6.000 | 5.970 | 4.242 | 4.280 | 0.029 | 194567.47 | 411378.93 | 5642.46 | 3085.34 | 945.14 | 836793.94 | 414464.27 | 2014Q4 | 1251258.20 | 16.30 | 76764.31 | 50729.67 | 63412.09 | 0.00 | 13352.22 | 0 | 337699.96 | 836793.94 | 3119.68 | 9870.46 | 195.77 | 45.74 | 9916.20 | 42441.34 | 1174493.90 | 0.29 | 53406.51 | -12478.16 | 183034.45 | 783387.43 | 391106.47 | 0.33 | 1174493.90 |
| 2015-03-31 | 5.970 | 6.000 | 4.280 | 4.361 | 0.029 | 183034.45 | 391106.47 | 5308.00 | 2933.30 | 884.67 | 802071.25 | 394039.77 | 2015Q1 | 1196111.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 394039.77 | 802071.25 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1196111.02 | 0.33 | 4265.20 | -978.03 | 182941.08 | 797806.05 | 398304.97 | 0.33 | 1196111.02 |
| 2015-06-30 | 6.000 | 5.910 | 4.361 | 4.355 | 0.028 | 182941.08 | 398304.97 | 5122.35 | 2987.29 | 866.73 | 800482.99 | 401292.26 | 2015Q2 | 1201775.25 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 401292.26 | 800482.99 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1201775.25 | 0.33 | -1101.10 | 252.84 | 184060.64 | 801584.09 | 400191.16 | 0.33 | 1201775.25 |
| 2015-09-30 | 5.920 | 5.670 | 4.363 | 4.239 | 0.028 | 184060.64 | 400191.16 | 5153.70 | 3001.43 | 908.94 | 784086.06 | 403192.59 | 2015Q3 | 1187278.65 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 403192.59 | 784086.06 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1187278.65 | 0.34 | -7828.80 | 1846.85 | 186816.43 | 791914.86 | 395363.79 | 0.33 | 1187278.65 |
| 2015-12-31 | 5.660 | 5.540 | 4.231 | 4.204 | 0.029 | 186816.43 | 395363.79 | 5417.68 | 2965.23 | 977.92 | 789487.46 | 398329.02 | 2015Q4 | 1187816.48 | 15.50 | 76633.32 | 51744.27 | 64680.33 | 0.00 | 11952.99 | 0 | 321695.70 | 789487.46 | 2843.24 | 12713.70 | 286.24 | 68.09 | 12781.79 | 53734.65 | 1111183.16 | 0.29 | 48328.29 | -11495.79 | 176298.56 | 741159.17 | 370023.99 | 0.33 | 1111183.16 |
| 2016-03-31 | 5.520 | 5.590 | 4.188 | 4.301 | 0.026 | 176298.56 | 370023.99 | 4583.76 | 2775.18 | 819.99 | 761786.92 | 372799.17 | 2016Q1 | 1134586.09 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 372799.17 | 761786.92 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1134586.09 | 0.33 | 5018.00 | -1166.70 | 175951.85 | 756768.92 | 377817.17 | 0.33 | 1134586.09 |
| 2016-06-30 | 5.590 | 5.700 | 4.301 | 4.448 | 0.028 | 175951.85 | 377817.17 | 4926.65 | 2833.63 | 864.32 | 786478.36 | 380650.80 | 2016Q2 | 1167129.16 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 380650.80 | 786478.36 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1167129.16 | 0.33 | 8003.21 | -1799.28 | 175016.89 | 778475.15 | 388654.01 | 0.33 | 1167129.16 |
| 2016-09-30 | 5.720 | 5.870 | 4.463 | 4.644 | 0.027 | 175016.89 | 388654.01 | 4725.46 | 2914.91 | 805.02 | 816516.96 | 391568.91 | 2016Q3 | 1208085.88 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 391568.91 | 816516.96 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1208085.88 | 0.32 | 10723.68 | -2309.15 | 173512.77 | 805793.28 | 402292.60 | 0.33 | 1208085.88 |
| 2016-12-31 | 5.870 | 5.830 | 4.644 | 4.679 | 0.029 | 173512.77 | 402292.60 | 5031.87 | 3017.19 | 863.10 | 815904.67 | 405309.79 | 2016Q4 | 1221214.46 | 14.80 | 82514.49 | 52779.15 | 65973.94 | 0.00 | 16540.55 | 0 | 322795.30 | 815904.67 | 3535.06 | 16248.76 | 368.70 | 78.80 | 16327.56 | 76396.66 | 1138699.97 | 0.28 | 56391.79 | -12052.10 | 162323.76 | 759512.88 | 379187.09 | 0.33 | 1138699.97 |
| 2017-03-31 | 5.840 | 5.880 | 4.687 | 4.785 | 0.029 | 162323.76 | 379187.09 | 4707.39 | 2843.90 | 800.58 | 780549.96 | 382030.99 | 2017Q1 | 1162580.95 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 382030.99 | 780549.96 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1162580.95 | 0.33 | 5108.46 | -1067.60 | 162056.74 | 775441.49 | 387139.46 | 0.33 | 1162580.95 |
| 2017-06-30 | 5.880 | 5.950 | 4.785 | 4.909 | 0.027 | 162056.74 | 387139.46 | 4375.53 | 2903.55 | 735.38 | 799146.53 | 390043.00 | 2017Q2 | 1189189.53 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 390043.00 | 799146.53 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1189189.53 | 0.33 | 5957.11 | -1213.51 | 161578.61 | 793189.42 | 396000.11 | 0.33 | 1189189.53 |
| 2017-09-30 | 5.950 | 5.990 | 4.909 | 5.009 | 0.027 | 161578.61 | 396000.11 | 4362.62 | 2970.00 | 728.32 | 812995.42 | 398970.11 | 2017Q3 | 1211965.54 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 398970.11 | 812995.42 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1211965.54 | 0.33 | 4614.41 | -921.22 | 161385.71 | 808381.01 | 403584.52 | 0.33 | 1211965.54 |
| 2017-12-31 | 5.980 | 5.920 | 5.000 | 5.016 | 0.027 | 161385.71 | 403584.52 | 4357.41 | 3026.88 | 736.05 | 813202.74 | 406611.41 | 2017Q4 | 1219814.14 | 14.10 | 86511.64 | 53834.73 | 67293.42 | 0.00 | 19218.22 | 0 | 320099.77 | 813202.74 | 3831.38 | 20080.15 | 438.72 | 87.46 | 20167.61 | 101160.74 | 1133302.50 | 0.28 | 57289.97 | -11421.44 | 150700.31 | 755912.77 | 377389.73 | 0.33 | 1133302.50 |
| 2018-03-31 | 5.920 | 5.760 | 5.016 | 4.945 | 0.028 | 150700.31 | 377389.73 | 4219.61 | 2830.42 | 732.57 | 748835.61 | 380220.16 | 2018Q1 | 1129055.77 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 380220.16 | 748835.61 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1129055.77 | 0.34 | -4244.59 | 858.36 | 152291.24 | 753080.20 | 375975.57 | 0.33 | 1129055.77 |
| 2018-06-30 | 5.750 | 5.700 | 4.936 | 4.964 | 0.028 | 152291.24 | 375975.57 | 4264.15 | 2819.82 | 748.10 | 759687.29 | 378795.39 | 2018Q2 | 1138482.67 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 378795.39 | 759687.29 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1138482.67 | 0.33 | 319.34 | -64.33 | 152975.01 | 759367.94 | 379114.73 | 0.33 | 1138482.67 |
| 2018-09-30 | 5.680 | 5.770 | 4.946 | 5.098 | 0.028 | 152975.01 | 379114.73 | 4283.30 | 2843.36 | 742.34 | 783651.04 | 381958.09 | 2018Q3 | 1165609.13 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 381958.09 | 783651.04 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1165609.13 | 0.33 | 6189.75 | -1214.15 | 152503.20 | 777461.29 | 388147.84 | 0.33 | 1165609.13 |
| 2018-12-31 | 5.780 | 5.430 | 5.107 | 4.876 | 0.031 | 152503.20 | 388147.84 | 4727.60 | 2911.11 | 870.64 | 747850.84 | 391058.95 | 2018Q4 | 1138909.79 | 13.40 | 84993.27 | 54911.43 | 68639.29 | 0.00 | 16353.98 | 0 | 306065.68 | 747850.84 | 3353.98 | 23434.12 | 622.48 | 127.66 | 23561.79 | 114887.27 | 1053916.53 | 0.29 | 44888.52 | -9206.01 | 144167.83 | 702962.32 | 350954.20 | 0.33 | 1053916.53 |
| 2019-03-31 | 5.440 | 5.760 | 4.885 | 5.247 | 0.028 | 144167.83 | 350954.20 | 4036.70 | 2632.16 | 700.82 | 760125.77 | 353586.36 | 2019Q1 | 1113712.13 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 353586.36 | 760125.77 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1113712.13 | 0.32 | 17279.78 | -3293.27 | 141575.37 | 742845.99 | 370866.14 | 0.33 | 1113712.13 |
| 2019-06-30 | 5.780 | 5.860 | 5.265 | 5.412 | 0.027 | 141575.37 | 370866.14 | 3822.54 | 2781.50 | 652.31 | 769736.23 | 373647.63 | 2019Q2 | 1143383.86 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 373647.63 | 769736.23 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1143383.86 | 0.33 | 7099.19 | -1311.75 | 140915.93 | 762637.03 | 380746.83 | 0.33 | 1143383.86 |
| 2019-09-30 | 5.870 | 5.890 | 5.421 | 5.513 | 0.027 | 140915.93 | 380746.83 | 3804.73 | 2855.60 | 645.96 | 780430.75 | 383602.43 | 2019Q3 | 1164033.17 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 383602.43 | 780430.75 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1164033.17 | 0.33 | 4020.62 | -729.30 | 140832.60 | 776410.13 | 387623.05 | 0.33 | 1164033.17 |
| 2019-12-31 | 5.890 | 5.960 | 5.513 | 5.652 | 0.026 | 140832.60 | 387623.05 | 3661.65 | 2907.17 | 614.37 | 799458.28 | 390530.22 | 2019Q4 | 1189988.50 | 12.70 | 93699.88 | 56009.66 | 70012.07 | 0.00 | 23687.81 | 0 | 296830.34 | 799458.28 | 4191.05 | 27625.17 | 609.29 | 107.80 | 27732.97 | 156746.77 | 1096288.62 | 0.27 | 68233.77 | -12072.50 | 129374.47 | 731224.51 | 365064.11 | 0.33 | 1096288.62 |
| 2020-03-31 | 5.970 | 5.260 | 5.661 | 5.054 | 0.026 | 129374.47 | 365064.11 | 3363.74 | 2737.98 | 639.49 | 657090.57 | 367802.09 | 2020Q1 | 1024892.66 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 367802.09 | 657090.57 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1024892.66 | 0.36 | -26512.83 | 5245.91 | 135259.87 | 683603.41 | 341289.26 | 0.33 | 1024892.66 |
| 2020-06-30 | 5.210 | 5.610 | 5.006 | 5.461 | 0.025 | 135259.87 | 341289.26 | 3381.50 | 2559.67 | 602.76 | 741945.86 | 343848.93 | 2020Q2 | 1085794.79 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 343848.93 | 741945.86 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1085794.79 | 0.32 | 17720.74 | -3244.96 | 132617.68 | 724225.12 | 361569.66 | 0.33 | 1085794.79 |
| 2020-09-30 | 5.630 | 5.780 | 5.480 | 5.694 | 0.024 | 132617.68 | 361569.66 | 3182.82 | 2711.77 | 550.66 | 758260.51 | 364281.44 | 2020Q3 | 1122541.95 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 364281.44 | 758260.51 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1122541.95 | 0.32 | 9525.03 | -1672.82 | 131495.52 | 748735.48 | 373806.47 | 0.33 | 1122541.95 |
| 2020-12-31 | 5.790 | 5.980 | 5.704 | 5.936 | 0.022 | 131495.52 | 373806.47 | 2892.90 | 2803.55 | 483.76 | 783429.01 | 376610.02 | 2020Q4 | 1160039.03 | 12.00 | 96669.92 | 57129.85 | 71412.31 | 0.00 | 25257.61 | 0 | 279940.10 | 783429.01 | 4254.99 | 31880.16 | 607.75 | 102.38 | 31982.55 | 189848.39 | 1063369.11 | 0.26 | 74161.81 | -12493.57 | 119485.71 | 709267.19 | 354101.91 | 0.33 | 1063369.11 |
In [21]:
# Save result as an Excel file
portfolio_value_df.to_excel(STOCK_FUND_SYMBOL + "_StableValue" + str(int(100 * STABLE_FUND_PROPORTION)) + "Pct.xlsx")
In [22]:
# Examine VBMFX dividends
vbmfx = yf.Ticker("VBMFX")
vbmfx_div_df = vbmfx.dividends
print(vbmfx_div_df.head())
Date 1987-01-15 0.07 1987-02-13 0.06 1987-03-13 0.07 1987-04-15 0.08 1987-05-15 0.07 Name: Dividends, dtype: float64