Dividend Growth Calculator For Retirement Balance Projections

by: High Yield Investor

Summary

DGC [Dividend Growth Calculator] can be used for portfolio projections in the accumulation phase of investment.

DGC can be used for portfolio projections in the withdrawal phase of investment.

I firmly believe a retirement plan should be determined before selecting dividend stocks. The DGC can help any investor follow a path to achieve your goals.

The calculator is free and the download link is located in the Conclusion #4 section of the article.

In my first article "Retirement Planning Depends On When You Begin Your Dividend Income Strategy And/Or Your Beginning Portfolio Balance" located here, I outlined the process to determine what your retirement plan should be before you just randomly start selecting dividend stocks. If your portfolio goal is 5% yield with 9% growth, then selections of stocks should be geared to your overall portfolio goal. This is just a logical place to start. The example below demonstrates two different ways to use the calculator, one for accumulation phase and the second would be for the withdrawal phase to see if you're going to run out of income based on your set up parameters. As the years go by you would just update the parameter fields and run the application again and the closer you get to your target date the more accurate the final numbers become. I'm sure you're going to have lots of fun experimenting with different options for your What-If scenarios. I truncated the matrix display in this article for readability, but the matrix is a complete 12 by 12 percent yield. As a disclaimer this calculator is for entertainment only and not to be used for any financial planning.

The calculator described below runs a series of yield/growth calculations ranging from 0% to 12% with a step of 0.5%. This gives greater than 600 possibilities for your portfolio outcome using a varied range of yield possibilities. The calculator was created using Excel 2010 and the software is VB (Visual Basic) code that runs in the background. My field of employment is in the Test Engineering field and running Shmoo plots on electrical parameters is something we perform all the time to come up with operational parameter limits for semiconductors. The same principle applies for this two variable array. Since I'm three years away from retirement and in the accumulation phase, I ran this application to predict my total portfolio balance. Once I know what my beginning balance in retirement will be, and using the predictive expenses study in my first article mentioned above, I can just plug in my withdraw value and see where I need to be with yield and growth to achieve my goal.

For the first application, the DGC is used for the accumulation phase of building a retirement portfolio;

I'm going to give an example of a person turning 60 (year 2014) and realizing that they have 6 years left before retirement. They have 200K in a portfolio and they are adding 10K a year at a rate of 2% increases every year.

Fields to enter:

  1. "Start Year", the beginning year that this person turns 60, default 2014.
  2. "Total Years", the years of accumulation until retirement, default 6.
  3. "Balance", the starting balance of the portfolio, default 200,000.
  4. "Save/Withdraw", this person is saving 10K a year starting in 2014, default 10,000
  5. "Inc%Yr", is the incremental percent the 10K is increased each year, default 2%.

If you know ahead of time you can add chart1 and chart2 yield and yield growth you want to compare, but this can be done later when creating the charts. In the matrix table, yield and yield growth fields are used to highlight in yellow the cells when the "RUN Shmoo" button is pressed.

Now press the "RUN Shmoo" button and the matrix table will be created. At this point the numbers in the cells are the portfolio balance after 6 years. Each cell is the result of starting at 200K and increasing this value by 10K at a 2% incremental increase each year. In effect the contribution to the portfolio each year increases, year 1 = 10K, year2 = 10,200, year3 = 10,404… and it continues this way for the remaining years. In the matrix table example a portfolio with a 10% yield and 0% growth would have $434,987 at the end of 6 years. With a yield of 5% and a growth of 4% would end up with $348,873 as a portfolio balance. This table gives an excellent way to design a portfolio. Determine what balance you want to end up with and build an investment portfolio with yield and/or yield growth parameters to match your goal. Change the savings value or percent or your starting balance and run the study over and over again. Change the years in accumulation and watch the ending portfolio balance balloon. Are we having fun yet?!

TABLE-1, note this table is truncated for display visibility, the x-axis goes from 0 to 12%.

For a visual effect to see how your results plot over the years, two charts are generated. The first chart1 is the total portfolio balance and the second chart2 the incremental income for each year. Once you have found what yield and yield growth you want to select just enter two options in the "Yield" field and the "Growth" field. A comparison between two yield options will be charted for a comparison. The default settings are chart1, yield at 10%, growth at 0%, chart2, yield at 5% and growth at 4%. Remember if you change the yield values and you want to highlight the matrix cells in yellow you must press the "RUN Shmoo" button again. Now press the "create chart" button and two data tables are created below the matrix table used for creating the charts (Only in the Excel workbook, not displayed in this article). If you jump to "chart1" TAB at the bottom of the Excel workbook you will see the "Portfolio total remaining balance" chart that displays the portfolio growth each year for both yield/growth options you have chosen, see CHART-1.

CHART-1

If you press the "chart2" TAB you will see a chart titled "Save/Withdrawn Rate vs Dividend Income generated each year". This chart displays the dividend growth for each yield option you have chosen. It also displays the incremental monies you are adding to the portfolio increasing by 2% each year. The key legends indicate the yield options you have chosen. Please ignore the "Withdraw" label, I did not have a chance to change this, it should be savings not withdraw. The accumulation phase adds any dividend income back into the portfolio. Note the first year dividends is the simple yield * balance. In the starting year 2014, the green-triangle is 10% * 200K = 20K, the red-square is 5% * 200K = 10K, the purple-X is the savings each year starting at 10K and is incremented by 2% each year.

CHART-2

For the second application, the DGC is used for the withdraw phase of a retirement portfolio, will there be money left over while in retirement?

I'm going to give an example of a person turning 66 (year 2020) and beginning the withdraw phase of retirement. From the previous TABLE1 the ending value in the accumulation phase for 5% yield, 4% growth the portfolio balance is $348,873.

Fields to enter:

  1. "Start Year", the beginning year that this person will start retirement, default 2020.
  2. "Total Years", the years in retirement, default 24.
  3. "Balance", the starting balance of the portfolio in the first year of retirement, default $348,873, this is the 5.0% yield with a 4.0% yield growth.
  4. "Save/Withdraw", this person will start withdrawing income beginning in 2020, default $-22,000. The withdraw value must be negative number.
  5. "Inc%Yr", is the incremental percent the withdraw income will increase each year, just like receiving a raise each year, default 4%. This value is always positive number.

If you know ahead of time you can add chart1 and chart2 yield and yield growth you want to compare, but this can be done later when creating the charts. In the matrix table the yield and yield growth fields are used to highlight in yellow the cells with the "RUN Shmoo" button is pressed. It just makes it easy to locate the two yield options in the matrix table.

Now press the "RUN Shmoo" button and the matrix table will be created. At this point the numbers in the cells are the portfolio balance after 24 years. Each cell is the result of starting at $348,873 and decreasing this value by -22K at a 4% incremental increase each year. In effect the withdraw from the portfolio each year increases, year 1 = -22K, year2 = -22,880, year3 = -23,795… and it continues this way for the remaining years. So in the matrix table example a portfolio with a 10% yield and 0% growth would have $764,615 at the end of 24 years. With a yield of 5% and a growth of 4% would end up with -$153,466 as a portfolio balance, running out of money. This table gives an excellent way to design a portfolio by just looking at the matrix table. Determine what balance you want to end up with, and build an investment portfolio with yield and/or yield growth parameters. Change the savings value or percent or your starting balance and run the study over and over again. Change the years in accumulation and watch the ending portfolio balance change. Are you getting excited yet, the power of knowing where your are going and backing it up with real numbers gives you the power to be ahead of the typical investor. Notice the green cells in the matrix indicate money left over after 24 years and the red cells indicating running out of money after 24 years. In the engineering field this is a good example of a SHMOO plot indicating the parametric limits of varying levels of yield and yield growth. It tells you according to your inputs your range of operating conditions to have a successful portfolio. Now you can select your stocks to achieve your total portfolio goal.

TABLE-2, note this table is truncated for display visibility, the x-axis goes from 0 to 12%.

For a visual effect to see how your results plot over the years two charts are generated. The first chart1 is the total portfolio balance and the second chart2 the incremental withdraw for each year. Once you have found what yield and yield growth you want to select just enter two options in the "Yield" field and the "Growth" field. A comparison between two yield options will be charted for a comparison. The default settings are chart1, yield at 10%, growth at 0%, chart2, yield at 5% and growth at 4%. Remember if you change the yield values and you want to highlight the matrix cells in yellow you must press the "RUN Shmoo" button again. Now press the "create chart" button and two data tables are created below the matrix table used for creating the charts (Only in the Excel workbook). If you jump to "chart1" TAB at the bottom of the Excel workbook you will see the "Portfolio total remaining balance" chart that displays the portfolio growth each year for both yield/growth options you have chosen, see CHART-3. Remember the portfolio balance is after withdraw of income each year. In this example the 10% yield, 0% growth keeps growing and the 5% yield, 4% yield growth runs out of steam after the year 2041. Changing the yield even my 0.5% will have a big impact on your results.

CHART-3

If you press the "chart2" TAB you will see a chart titled "Save/Withdrawn Rate vs Dividend Income generated each year". This chart displays the dividend growth for each yield option you have chosen. It also displays the incremental monies you are withdrawing from the portfolio incrementing by 4% each year (purple-x). The key legends indicate the yield options you have chosen. The top green-triangle dividend income is from the 10% yield, 0% growth and the bottom red-square is from the 5% yield, 4% yield growth that runs out of money trying to support the 22K withdraw each year. Here again changing the yield, withdraw money (-22K), withdraw rate (4%), starting balance ($348K) and the years will have a big impact on the results. Notice the purple-X chart line that shows the withdraw amount incrementing by 4% each year. If you hover over with your mouse the red-square chart line the dollar amount will be displayed. Hover over the green line also displays the dollar amount. The chart options can be changed to your own personnel selections.

CHART-4

Running DGC:

  1. The matrix/charting software is built on the Microsoft Excel platform using Excel 2010.
  2. Create a copy of the excel (filename.xlsm), VB (Visual Basic) macro file to another name. This way if something blows up you will have the original file to start over again.
  3. When you first open up the workbook a message will be displayed, Macros have been disabled. "Enable Content" at the top of the excel workbook.
  4. This calculator may have bugs in it since I can't explore all possible combinations to fully check out its operation. This is rev-A , and when I have additions to make to the software I will up-rev the calculator.

Conclusion

  1. Step-1; if you're a number of years away from retirement, use the calculator to predict your portfolio balance. The parameters should be your current 401K or IRA/Roth total portfolio balance, yield/growth, and years before retirement.
  2. Step-2; determine what your total portfolio yield/yield growth should be at the beginning of your retirement plan. This is where you want to withdraw some income and see if you're going to run out of money based on the total years in retirement.
  3. Step-3; select stocks to achieve your goal based on the studies you conduct using the DGC (Dividend Growth Calculator). I have been championing this approach as a logical process before your stock selection based on your retirement goals. This calculator I hope will add value to your design process.
  4. Seeking Alpha does not have a location on their site to place this calculator. I created a link on the OneDrive located here. The file will be a .zip file, just unzip and open the Excel file.
  5. As a disclaimer this calculator is for entertainment only and not to be used for any financial planning. The DGC is for personal use only and may not be distributed for profit.

Disclosure: The author has no positions in any stocks mentioned, and no plans to initiate any positions within the next 72 hours.

The author wrote this article themselves, and it expresses their own opinions. The author is not receiving compensation for it (other than from Seeking Alpha). The author has no business relationship with any company whose stock is mentioned in this article.