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:
 

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

Buy and Hold Beats Active Investing
    • General Discussion
2
Replies
46
Views
3K
Figuring out compounding interest
    • General Math
Replies
4
Views
2K
How can I calculate the weight of chocolate in a tank without a scale?
    • General Math
Replies
7
Views
3K
Calculate Reimbursement for Multiple Plans: A Healthcare Guide
    • Precalculus Mathematics Homework Help
Replies
2
Views
1K
How could I plot my data? (which software)
    • General Math
Replies
2
Views
2K
I with an Excell formula I cannot figure out.
    • Computing and Technology
Replies
2
Views
2K
I Compressible choked gas flow through an orifice -- Excel formula
    • Thermodynamics
Replies
1
Views
2K
How many steel profiles do I need to support a workshop roof in Santiago, Chile?
    • General Engineering
Replies
3
Views
978
Simulating rocket launches using excel
    • Aerospace Engineering
Replies
2
Views
5K
How can i use excel to solve some Statistics problems
    • Set Theory, Logic, Probability, Statistics
Replies
1
Views
3K

Hot Threads

Recent Insights

Back
Top