Level 4: Valuation · Exercise 45 of 60 · Pro

Diluted shares with the treasury stock method

A financial modeling exercise from Modeldrill. Solve it with a live spreadsheet grid in your browser, with Fill right and Fill down, and it is checked the moment you submit.

The task

Four option tranches are listed with their strike prices. For each tranche fill the options that are in the money (column D: the option count when the strike is below the share price in B2, otherwise 0), the proceeds from exercise (column E), the shares bought back with those proceeds at the share price (column F) and the net new shares (column G). Then total the net new shares in B11, the diluted share count in B12 and the diluted equity value in B13.

What this exercise teaches

The treasury stock method counts only the net dilution, which is why a tranche struck just below the price adds almost nothing while a deep-in-the-money tranche adds most of its options. Equity value per share should always be built on the diluted count.

A hint if you get stuck

Only in-the-money options are exercised. The company is assumed to spend the exercise proceeds buying back shares at the current price, so the dilution is the options less the shares repurchased. Lock the share price.

Functions used: IF, SUM