Using Excel to figure out how much money I could make if I traded my dividends?

[moonman239]

I want to use Excel to figure out how much money, in dividends, I could make if I bought a stock in Microsoft, whose stock has recently stayed at $30-$31. They pay $0.80 in dividends/share.

So far, I have a spreadsheet with columns "Years," "Shares," "Share Cost - Dividends," and "Dividends."

The "Shares" column has the formula shares=1+ROUNDDOWN(((0.8*A4)*B3)/30.5,0.1), the first part of the equation (.8*A4)*B3 representing the dividends paid out to me the previous year.

The "Share Cost - Dividends" column has the formula = (30.5)*# of Shares - Amount in Dividends Paid Out The Last Year.

The "Dividends" column has the formula = (.8*Shares)*Years.

Does this look good or do I need to change anything in the spreadsheet?

 

[OmCheeto]

I want to use Excel to figure out how much money, in dividends, I could make if I bought a stock in Microsoft, whose stock has recently stayed at $30-$31. They pay $0.80 in dividends/share.

So far, I have a spreadsheet with columns "Years," "Shares," "Share Cost - Dividends," and "Dividends."

The "Shares" column has the formula shares=1+ROUNDDOWN(((0.8*A4)*B3)/30.5,0.1), the first part of the equation (.8*A4)*B3 representing the dividends paid out to me the previous year.

The "Share Cost - Dividends" column has the formula = (30.5)*# of Shares - Amount in Dividends Paid Out The Last Year.

The "Dividends" column has the formula = (.8*Shares)*Years.

Does this look good or do I need to change anything in the spreadsheet?

Why would "Shares" require a formula?
When I plugged your formula into my spreadsheet, my shares dropped from 1000 to 1 by the 4th year. I lost over $30,000!