2

My goal is to calculate the total end stock price (equals to the cumulative dividend till that date + stock close price)

  1. I have a table with dividend pay dates and dividend amount

enter image description here

  1. I have a table with daily stock close price

enter image description here

So for example, if the first dividend is paid on 01.02.2018 and the amount is 0.85, and the 2nd dividend is paid on 27.02.2018, and the 3rd dividend is paid on 15.06. 2018

I would like to add a column my daily stock close price called total stock price

which from 01.02.2018 to 28.02.2018 the value would be stock close price + first dividend and from 28.02.2018 to 15.06. 2018 the value would be stock close price + first dividend + 2nd dividend.

teylyn
  • 22,498
  • 2
  • 40
  • 54
Aries
  • 33
  • 4

1 Answers1

3

Assuming that the dividend data is in a table called DividendsTable, you can use this approach:

=C3+SUMIFS(DividendsTable[Amount],DividendsTable[Ex Date],"<="&B3)

copy down. The formula uses structured references for the table, but you can use column letters instead.

enter image description here

teylyn
  • 22,498
  • 2
  • 40
  • 54
  • Nice answer. This assumes that `DividendsTable` contains only dividends paid after the stock was purchased. Otherwise there would need to be a cell containing the purchase date and `SUMIFS` would also need to exclude dividends paid before that date. I am assuming (as you are) that the dividends are not reinvested. – Blackwood Jan 03 '18 at 03:32
  • Thank you for answering, but now I have another problem. I want to distribute the amount of dividend during the period, lets say from 26/01/2011 to 27/04/2011. so each day the stock holder gets 4/ days (between 26/01/2011 to 27/04/2011) of dividends. how should i approach this problem? – Aries Jan 07 '18 at 20:04
  • Add a column to the dividends table that calculates the daily value and use the daily value instead of the Amount value. – teylyn Jan 08 '18 at 03:21
  • Hi Teylyn, I would like the cumulative distributed dividend. For Example on 01/27/2011, the final price would be last price+2+4/(days from 01/27/2011 to 04/27/2011)*(days between 01/27/2011 and 01/26/2011). And for on 01/28/2011, the final price would be last price +2 + 4/(days from 01/27/2011 to 04/27/2011) *(days between 01/28/2011 and 01/26/2011) How would you recommend I approach this problem? – Aries Jan 09 '18 at 08:52
  • Ask a new question. This does not communicate in comments and your initial question has been answered. – teylyn Jan 10 '18 at 03:54
  • Hi teylyn, I have created the question a while ago here. https://superuser.com/questions/1283397/add-distributed-dividend-cumulative-total-on-to-a-column-for-different-dates?noredirect=1#comment1897516_1283397 – Aries Jan 10 '18 at 14:14