One of the premises of early retirement using a dividend portfolio, is that the dividend payments should be able to cover your expenses. Many people are used to receiving a consistent salary every month, so we can replicate that using dividends. Without a consistent dividend stream, it is difficult to budget our expenses after retirement. Today I’m going to use Excel Solver in conjunction with the Top 25 Dividend Stocks from VDIGX to create your own Monthly Dividend Portfolio so that you receive a consistent income stream to cover your expenses.
Feel free to download the spreadsheet here and follow along –> Monthly Dividend Portfolio.xlsx
Top 25 Stocks for the Monthly Dividend Portfolio
A quick recap from my previous post, here are the Top 25 Monthly Dividend stocks from VDIGX based on backtested data.
- V
- TJX
- SBUX
- VFC
- UNP
- NKE
- UNH
- ACN
- AMGN
- AMT
- NOC
- COST
- ADP
- MMC
- DHR
- MCD
- ECL
- MSFT
- LMT
- PSA
- HON
- JNJ
- WBA
- CB
- AXP
Using the dividend payment streams from these stocks, we can create a monthly dividend portfolio to cover our expenses.
Each of these stocks individually pay out dividends only once every quarter. But they don’t all pay dividends at the same time. Some might pay in March, some in April, etc.
However, if we buy the right amount of stocks from the list above, we can make it so that we will receive consistent dividend payments each month, from different companies.
Monthly Dividend Portfolio Schedule
To illustrate, let’s take a look at their dividend payment schedule:
As I explained earlier, these stocks all pay on different months throughout the year. So our goal is to buy the right amount of stocks so that we can create our own consistent monthly dividend portfolio.
First, in order to do that, we need to capture the dollar amount of each of these dividend payments.
These dollar amounts are based on the last payment according to Nasdaq. These amounts are expected to change every year. Therefore, we’ll also need to update our monthly dividend portfolio every year to reflect the changes.
Now that we know how much each stock is paying and when they’re paying, let’s move onto calculating how many shares of each to buy.
Assumed Total Monthly Expenses = $2,000
First, let’s assume that our total monthly expenses equal $2,000. Therefore, we will need our monthly dividend portfolio to generate at least $2,000 in dividend payments in order to cover our expenses.
At the bottom of my spreadsheet, highlighted in yellow, is where you will input your monthly expenses. I have filled out this column with $2,000.
Once you’ve filled out the monthly expenses column, click on the Data tab in excel and click on the Solver icon.
If you don’t have this Solver button, you may need to add it. I used this tutorial on how to do it.
Once you click on Solver, this window should pop up. I’ve pre-populated the parameters in order to create the portfolio but I’ll go over them here so you understand.
Excel Solver Parameters
Parameters
- Set Objective: I’ve set the objective box to the cell for the “Annual Total”. We want to find the minimum number of shares we need to buy in our monthly dividend portfolio in order to meet our objective of $2,000 per month. Therefore, our annual objective will be $24,000 per year. This is also why the “Min” button is checked.
- By Changing Variable Cells: This is what Solver’s magic will calculate for us. It will tell us how many shares of each stock we need to buy in order to obtain our objective of $2,000 per month.
- Constraints: Here we put in our constraints. I’ve set the following two constraints
- First, the number of shares we buy for each stock has to be greater than 1. It can’t be 0. I set this because I want to buy at least 1 share of each stock. This is a personal preference and can be changed or removed. Also, the number of shares can’t be negative (meaning we are shorting stock).
- Second, the monthly dividends has to be equal to, or greater than, $2,000. We set this because we want our monthly dividend portfolio to pay us $2,000 per month. With this constraint, we can be sure that our annual total will be at the very least, $24,000.
Excel Solver Magic Results
Once this is all set, leave everything else default and click on Solve.
You’ll notice that the “# Shares” column has been populated. Unfortunately these are all in decimal form. There’s a way to make them into integers but I decided to leave them in decimal form so that we can all determine if we want to own more (round up) or own less (round down) of specific company shares.
In the end however, this gives us an approximation of how much of each share we would have to buy in order to maintain a consistent dividend income stream.
Final Thoughts
Keep in mind that $2,000 per month requires a HUGE capital investment. Using the above data, this requires over $1 million in investment in order to obtain $2,000 per month. However, if you’re retired, then you should be expected to have a least a million or more in investment savings.
As we build our way towards saving for that goal, we can plan out our future ahead. Slowly accumulating these shares overtime will grant us the financial independence we deserve.
Again here is the link to download the spreadsheet
If you’re an Excel solver aficionado, feel free to make any modifications you’d like to the portfolio. Things you might want to consider:
- Removing some dividend stocks you don’t like.
- Adding dividend stocks you believe might do well.
- Removing the constraint where the number of shares has to be greater than or equal to 1. There might be some stocks not worth buying at all.
- Adding more constraints, like no more than 100 shares of a certain stock.
The sky is the limit. Experiment and feel free to create your own monthly dividend portfolio.
Thank you so much for explaining dividends, I am by no means a financial guru. You explain it so that a layman can understand.
Great post, I am glad I found it! Can you explain the benefit of doing this type of strategy vs. just investing in VDIGX (or other divided yielding mutual fund or ETF) assuming you didn’t need a consistent $2,000/month and rather just needed $24,000 a year (regardless of when the deposits were made)?
VDIGX has a very minimal expense fee that’s integrated into the fund. This isn’t much and is probably negligible for most people who want to invest passively in a dividend fund. Additionally, VDIGX also contains 42 stocks and 4 bonds currently. What stocks/bonds it contains is up to the fund manager’s discretion. VDIGX is good for the passive investor and pays out its dividends twice a year. If you don’t need dividend income to match your monthly expenses, VDIGX is a good passive way to invest.