Help with calculation of dividends

[VidOs]

Hi all,

I need assistance in devising a formula to calculate dividends for various types of securities that are interdependent. In my example, Shareholders A and B are to divide a specified sum of money amongst themselves ($100, $200, and $400). The distribution of funds is based on the number of shares and options held by the shareholders.

Shares are entitled to dividends from the start (a dividend per security of 0), while options are entitled to dividends only when the dividend per security is 1 or greater. This is because the options have a strike price of $1, which must be paid to convert the option to a share. In essence, options will always be valued $1 less than a share, and only if the dividend per share exceeds the $1 strike price will the options be converted to shares and thus be eligible for dividends.

This implies that until the point where the dividend per security reaches 1, all dividends shall be allocated to the shares. After the dividend per security is 1 or greater, the dividends shall be split, taking into account the -1 cost of options. In the attached sheet, I essentially need assistance with populating the yellow and orange cells.

I assume this involves relatively straightforward mathematics, but I am struggling to comprehend it as I consistently obtain an incorrect distribution of proceeds with my faulty (and very limited) formulas.

Thanks in advance,
VidOs

[JeteMc]

It may help if you could manually fill the yellow cells in rows 13:14 and 17:18 on the Prio 1 sheet, so that someone will have expected values as a guide to use in attempting to write formulas/code to automate the process.
Let us know if you have any questions.

[VidOs]

Thank you - I think I found a way around the problem.