Calculate Compound Dividends with a Spreadsheet

Many people fail to recognize the power of compounding interest or dividends. Even if recognized, calculating the rate of compounding is advanced math for others. Compounding is very powerful and over time, lucrative. In this post, as a follow up to Investing in Preferred Stocks, I will demonstrate how to calculate compound interest using a spreadsheet.

In my previous post, I wrote about a preference for preferred stocks. In the post, I used my holding – PGX – as an example to show how compounded investments grow. This article explains how to use a spreadsheet to do the same.

Parameters and Assumptions

I used PGX, Powershares Preferred ETF

My holding is as of 10–12–2016, 102 shares

The dividend per share for September 2016 was $.07067

For this table, I kept the price of PGX at a fixed $15.00 for 24 months. The price of PGX will vary over time.

I used Apple’s “Numbers” program, but Microsoft Excel or any other spreadsheet will produce the same results.

The Cells and Formulas

The header cells are obvious and simply for structure. Since PGX pays monthly, the first column shows months 1 to 24. Use any range you wish.

The new total is the original total added to the shares purchased by the dividends each month. Formulas follow:

New total: Original share total + (dividend/share * shares held)

= 102 + (15*.07067)

= 102 + ..51 shares added or 102.51…. This amount becomes the new original share total, replacing the 102 shares.

Dividend: Multiply # of shares (102) * monthly dividend per share (.07067)

The New Total is calculated as above.

Drag the cells all the way down to fill in each cell for the range desired.

Note the new total after 24 months is 115.10 shares and the dividend amount increases to 8.67. This is a 12.3% increase in 24 months. No additional money was added or additional shares purchased.

If this were continued for 5, 10 or 20 years, the amount would be significant.

Take a look at the year by year chart – the 2nd one.

After 20 years without adding any money or buying more shares, the monthly payout is over $300 a month and over 500 shares. Even better, until touched, this amount keeps growing.

compound calcuation

compound interest