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

In summary, the conversation discusses using Excel to calculate potential dividends from buying Microsoft stock. The spreadsheet contains columns for "Years," "Shares," "Share Cost - Dividends," and "Dividends," with formulas for each column. The discussion also mentions a potential issue with the "Shares" formula causing a significant loss.
  • #1
moonman239
282
0
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?
 
Mathematics news on Phys.org
  • #2
moonman239 said:
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! :cry:
 

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

1. How do I use Excel to calculate my potential dividend earnings?

To use Excel for this purpose, you will need to have a list of your dividend-paying stocks and their corresponding dividend yields. Then, you can create a simple formula in Excel that multiplies the dividend yield by the number of shares you own for each stock. This will give you the potential annual dividend earnings for each stock. Finally, you can sum up all of the individual earnings to get your total potential earnings.

2. What other factors should I consider when using Excel to calculate my potential dividend earnings?

In addition to the dividend yield and number of shares, you may also want to consider any changes in the dividend payout ratio or any potential changes in the stock price. These factors can impact your potential earnings and should be taken into account when using Excel for this calculation.

3. Can Excel help me track my actual dividend earnings?

Yes, Excel can be a useful tool for tracking your actual dividend earnings. You can create a spreadsheet that lists your stocks, their dividend yields, and the dates when dividends are paid. As you receive dividends, you can input the amounts into the spreadsheet and use formulas to calculate your total earnings.

4. Is there a way to use Excel to analyze different dividend scenarios?

Yes, Excel has powerful tools for analyzing different scenarios. You can create different scenarios by changing variables such as the dividend yield, number of shares, or stock price. By doing this, you can see how these changes would impact your potential earnings and make informed decisions about your investments.

5. Can Excel provide visual representations of my potential earnings?

Yes, Excel has various charting and graphing features that can help you visualize your potential dividend earnings. You can create a pie chart to see the percentage breakdown of earnings from each stock, or a line graph to track your earnings over time. These visual representations can make it easier to understand and analyze your potential earnings.

Similar threads

Back
Top