How to Create Investment Table for Annual 5% Dividend Cumulative
We have here a tutorial for creating an investment table for the annual 5% dividend with a monthly savings or investment of 10,000. Accumulated for one year, and up to 5 years.
Out tutorial is done on Google Sheets, but you can also follow the tutorial and do it on Excel or other spreadsheets. The formula stays the same, it is simple and the steps by step guide is still the same.
How to Create Investment Table for Annual 5% Dividend
Step-by-Step Guide:
Open Google Sheets, Set Up Your Spreadsheet: Start by opening a new Google Sheets document. Label the columns as follows:
- Column A: Time Period (Year/Month)
- Column B: Initial Investment
- Column C: Monthly Investment
- Column D: Total Investment
- Column E: Monthly Dividend
- Column F: Cumulative Dividend
First Input Your Data: In cell B2, enter your initial investment of $100,000, and in cell C2, enter your monthly investment of $10,000.
Calculate Monthly Dividends
In cell E2, use the formula =B2 * 5% / 12 to calculate the monthly dividend from the initial investment. The 5% annual dividend is divided by 12 to get the monthly rate.
Track Total Investment Over Time
After the above data, you can now Track Total Investment Over Time. In column D, track the growing total investment. In D2, simply enter =B2 for the initial amount. In D3 and onward, use =D2 + C2 to add the monthly contribution to the total investment.
Calculating Dividends
In column F, keep track of your cumulative dividend. In F2, enter =E2 for the first month’s dividend. In F3 and onward, use =F2 + E3 to accumulate the dividends.
If you want to extend the investment for more than 5 years, You can Extend for Multiple Years, by copying the formula to next cells.
Drag the formulas down the rows to extend them for the desired timeframe (1 year = 12 months, 5 years = 60 months). This allows you to track your dividends as your investment grows.