So I have been trying something but I just cannot get it working. Hope you can help me out. I have a forecast overview (see Stock forecast image). In this overview I want to see the dividends per stock per month. Of course I could fill in everything manually but I'd like to automate it a bit.
Currently in this forecast I want to see the upcoming 12 months. For the months I use the following formula: =EDATE(TODAY(),0). This results in december 2023.
For this to work I need a different table where I specify in which month a stock is paying dividend. Also I need to specify how many dividend each company is paying per month. Somewhere I also need to multiply the amounts of stocks and the dividend per month. In the first image the stocks symbols are in column A but for the seperate table I think it is better to have them as columns. I use the following formula for this: =TRANSPOSE(A1:A5)
See the image below for this table.
For the sake of this example let's say I have these 4 stocks and each stock is paying $1 dividend each month. I own 5 stocks of each company.
Now my question is: what formula do I need to calculate how many dividend is being paid per month per stock displayed in the forecast?
I tried so many things but just can't figure it out.
I tried many different formulas. Also used ChatGPT but that cannot help out either. Here is one example:
=IFERROR(SUMIFS(1, 2, 3, INDEX(4, 0, MATCH(5, 6, 0)), 7), 0)
- Range containing dividends (second table)
- Range containing the months in a list (second table)
- Reference to the month in the row of the forecast overview (first table)
- Stock per column (second table)
- Stock in the row of the forecast overview (first table)
- Stock per column (second table)
- Stock in the row of the forecast overview (first table)
Maybe this example is totally wrong. I also tried other things but I think that is not relevant. You should get a good idea of what I'm trying to achieve here.