Please Note: Blog posts are not selected, edited or screened by Seeking Alpha editors.

Portfolio Allocation For Equal Income From Each Position Using Excel

|Includes: AGNC Investment Corp. (AGNC), DLR, GIS, JNJ, KMB, LMT, LTC, MCD, MMM, MO, O, PEP, PG, PM, SBRA, VZ, WPC, WU

This article describes how to allocate a portfolio so that each position generates the same amount of income, using Excel. This technique is an alternative to the popular method of investing the same dollar amount in each position.

The rationale for equal income allocation is to protect dividend income. Compare a portfolio using equal income allocation with a portfolio using equal capital allocation. Consider what can happen if one position were to reduce or suspend its dividend.

First, an illustration of a portolio with $30,000 spread equally among 3 stocks.

Equal capital allocation
Symbol Yield $ Invested Annual
AGNC 13.45% $10,000 $1345
MO 3.93% $10,000 $393
PEP 2.80% $10,000 $280
Totals 6.7% $30,000


What would happen to the portfolio income if AGNC were to suspend its dividend? The total income would decrease to $673, a decrease of 67%. Not a good outcome.

Instead, invest the same total capital in the same 3 stocks, but size individual positions so that each generates the same income.

Equal income allocation
Symbol Yield $ Invested Annual
AGNC 13.45% $3,249 $437
MO 3.93% $11,120 $437
PEP 2.80% $15,631 $437
Totals 4.37% $30,000 $1,311

With this allocation, portfolio yield decreases from 6.7% to 4.37%. However, a dividend suspension by AGNC would cause income to decrease to $874, a decrease of 33%. Not desirable but a lot better than with the equal capital allocation.

The tradeoff is between total income and income protection.

How did I determine the equal income allocations?

Following is a snapshot of the spreadsheet I use to track a number of dividend stocks. One of its uses is to calculate hypothetical portfolio allocations using equal income allocations. I show how a portfolio worth $100,000 can be allocated. (Some of the rows and columns are hidden for clarity.)

Column AB is a simple binary switch that lets me include or exclude a stock from the study. In this example the 3 stocks used in the previous illustrations are selected.

Column AD computes the weighting factor for each selected stock. The formula for cell AD3 is as follows:

  • =IF(AB3>0,MAX(H$3:H$58)/H3,0)

Column AE computes the portfolio allocation percentage for each selected stock. The formula for cell AE3 is as follows:

  • =AD3/AD$60

Cell AN60 is the weighted 5 year compound dividend growth rate of 5.5% for the selected portfolio. You can combine this with the portfolio yield of 4.37% to get a Chowder number for the portfolio of 9.87. I am in the RMD phase so I am interested in seeing the dividend growth rate keep ahead of the annual increase in RMD percentage. This portfolio appears to fill this need, but the negative growth rate for AGNC, if it persists, is a big red flag.

Column L has the Morningstar Financial Health grade for each stock. If I were to build a portfolio that contains only those stocks with a grade of A (the top grade) then the result would look like this:

This portfolio has a lower yield of 3.63% but a higher 5 year dividend growth rate.

In reality I mix some higher yielding stocks having lower financial health grades in with the lower yielding stocks having higher financial health grades. Of the stocks illustrated I own AGNC, CCP, DLR, GIS, JNJ, LTC, MO, O, PEP, PG, PM, WPC, and WU, as of this writing. A portfolio containing only these stocks would look like this:

In this portfolio, if AGNC were to suspend its dividend, total income would decrease by $324 to $3891, a decrease of 7.69%. The negative contribution of AGNC to portfolio 5 year dividend growth rate is much less than with the 3 stock portfolio. CCP has not existed for 5 years so its growth rate is zero. Even with a negative number and a zero number, the portfolio 5 year DGR is a healthy 8.36%.


To the extent that higher yielding stocks tend to have more dividend risk than lower yielding stocks, this strategy automatically assigns more capital weight to stocks with less dividend risk.

Disclosure: I am/we are long AGNC, CCP, DLR, GIS, JNJ, LTC, MO, O, PEP, PG, PM, WPC, WU.