compare date in one column to dates in another and add shares associated with that date

[rpage4]

Hello,

Here is my project:

The ultimate goal is to create a column that summarizes the outstanding shares of equity at any point in time.

 

The outstanding shares (granted + dividends - lapses - forfeitures) vest at 25% per year over a 4 year period. One person gets one grant (grant ID). Say 100 shares. They would vest at 25 shares (+ dividends) over 48 months. I have broken out each lapse into tranches and assigned a tranche ID for each lapse based on units granted, but this does not take into account any dividends.

Dividends are paid at a quarterly frequency and each time a dividend is paid, I need to update the lapse total to include the dividends issued before the lapse date. I was thinking something along the lines of if the dividend date is less than the lapse date, then add the dividend shares for that grant ID to the shares to lapse.

Where I struggle is the lapse date and dividend date are in different columns and could be in any order. Any ideas on how to approach this problem?

Thanks!

[mceleavey]

Hi @rpage4 ,

Is there any way you can simplify this? Most people reading this have no interest in tranches, lapses etc. You want to show a summary of "Shares of Equity" but there's no column for that. I guess I'm struggling to understand your question.

Can you give us an example of what you have and mock up what you want? Tell us in terms of columns what it is you're trying to achieve. 

Thanks.

[rpage4]

Thanks for the response and apologies for the confusion.

In the screenshot, I am trying to update the column 'sum_lapses_by_tranche'. I need to compare the 'dividend_date' column to the 'lapse_date_column'. If the dividend date falls between the lapse dates, the number of shares in the 'sum_dividend_shares' column need to be added to the 'sum_lapses_by_tranche' column. 

So for the lapse date of 4/29/20, the 'sum_lapses_by_tranche' should be 109 (107 original + 2 dividends). Does that make any more sense?