Black-Scholes Option Pricing Excel Model (with add-on for Privately Held Companies)

Description
The Black-Scholes model is widely used to calculate the theoretical price of a European-style call or put option, using current stock prices, expected interest rates, the option's strike price, time to expiration and expected volatility.

A common challenge when using this model is determining a suitable volatility estimate. For publicly traded companies, this can be done by looking at option prices in the market, assuming the market has priced the option correctly, then rearranging the equation to solve for volatility (i.e. "implied volatility").

An alternative approach is to use "historical volatility", which is calculated by finding the standard deviation of stock price changes over a given time period. Common practice is to use monthly price data then annualizing the monthly volatility. Historical volatility is then used as an estimate for expected volatility.

The issue for privately held companies, however, is that they do not have option prices readily available in the market, let alone monthly trading data to determine historical volatility. This can be a problem because privately held companies (especially start-ups) often issue stock options to employees, and the value of these stock options may need to be calculated for accounting or taxation purposes.

This spreadsheet attempts to resolve this problem by using a basket of comparable companies from which to estimate volatility. It starts by finding the historical volatility for each comparable, re-levering the figures to match your company's capital structure, then taking the average of the basket.

Simply copy-paste the last 5 years of historical monthly adjusted closing prices for your basket of comparable companies, the debt-equity ratio of your company and each comparable, and the corporate tax rate, alongside all the other standard inputs of the Black-Scholes model.

Having trouble deciding what expected interest rate to use? Enter the yield curve for government bonds into the spreadsheet and it'll decide for you!

While this model can be very useful, please be aware of its limitations. Model assumptions include: market efficiency, zero transaction costs, known and constant risk-free rates and volatility, normally distributed returns on the underlying, and no dividends paid out during the life of the option. For more information about the model limitations, you are recommended to do your own research or seek independent advice.

Enjoy!
Jhett Koo