ESOP & Stock Option Pool Model: Vesting, IFRS 2 Expense & Exit Value (Excel)

Description
Most option trackers report the pool as granted against authorised and stop there. That number is wrong the moment somebody leaves.

Forfeited options return to the pool and can be granted again. Exercised options leave the pool permanently and become ordinary shares. This model tracks all four states and reports what is genuinely available - then answers the question a founder needs before the next hiring round: enter the options you expect to grant over the next twelve months, and it tells you whether the pool survives, how large a top-up you need, and what that top-up costs in dilution.

The dilution block shows the pool three ways: counting the whole authorised pool, counting only what has been granted, and counting only what has actually vested. Investors quote the first, employees feel the third, and the gap between them is worth understanding before a negotiation.

VESTING, CLIFFS AND LEAVERS
A grant vests nothing before its cliff. At the cliff the whole elapsed portion vests at once, and then vesting continues monthly. Advisor grants with no cliff vest from month one. A leaver stops vesting in their leave month and forfeits everything unvested, which returns to the pool. Four integrity checks police exactly this: vesting never exceeds the grant, vested plus forfeited equals granted grant by grant, nothing vests before its cliff, and vesting never goes backwards for anyone who stayed.

THE EXPENSE YOUR AUDITOR ASKS FOR
IFRS 2 and ASC 718 both require the grant-date fair value to be charged over the period the employee earns it, whether or not the option is ever exercised and whether or not it ends up in the money. That fair value is not the intrinsic spread, which is usually zero at grant. It is an option pricing value.

This model computes a Black-Scholes value for every grant from the scenario's volatility, risk-free rate and expected term, against the fair market value at that grant's own date. The expense is recognised straight-line and trued up when someone leaves, which is why the monthly line occasionally goes negative - and why that is correct rather than a bug.

THE WORKED EXAMPLE
Twenty-eight grants across seven teams including two leavers, on a two million option pool that ends up 82% committed, with a total programme cost of 1.82 million.

Nine tabs, forty grant rows, sixty months of vesting, three scenarios from one cell. Twelve integrity checks, all of which must read PASS. No macros, no locked cells, no passwords.

Share plan accounting and taxation are jurisdiction-specific. This workbook models the arithmetic; it is not accounting, legal or tax advice.