Use a Pricing Model: Implement Black-Scholes in Excel

Options pricing is a critical element of financial trading and investment. Among the several models for valuing options, the Black-Scholes model stands out for its simplicity and efficiency. In 1973, economists Fischer Black and Myron Scholes introduced this groundbreaking model that revolutionized the world of finance. Today, it is one of the fundamental tools that every option trader, analyst, and financial student should understand. In this article, we’ll delve into the mechanics of the Black-Scholes model and illustrate how to implement it using Microsoft Excel. While there are dedicated financial software available for options pricing, the simplicity and ubiquity of Excel make it an excellent tool for understanding the underlying mathematics and gaining a hands-on experience.

Excel offers all the necessary functions to calculate the key variables of the Black-Scholes model: d1, d2, and finally the call or put option price. By the end of this guide, you’ll be able to build your own options pricing spreadsheet. This knowledge will help you in making informed investment decisions, whether you’re just dabbling in options trading or you’re managing a substantial portfolio.

Remember, the Black-Scholes model, like any financial model, is just an approximation of reality. It’s a lens to help us simplify the complex financial world. Although it has its assumptions and limitations, the model is nevertheless a valuable tool for understanding how various factors influence the price of an option.

So, let’s grab a virtual seat at our financial modeling desk, open Excel, and start our journey into the fascinating world of options pricing through the Black-Scholes model.

Setup Your Black-Scholes Excel Spreadsheet

Let’s go through the steps to set up a spreadsheet for the Black-Scholes Model in Excel.

1. Setting up the Input CellsFirst, you’ll need to create cells for each of your input variables. In a new spreadsheet, choose cells to contain the following information (or use these exact cells for simplicity’s sake):
   • B1: Title this cell ‘Stock Price (S0)’.
   • B2: Title this cell ‘Strike Price (K)’.
   • B3: Title this cell ‘Risk-Free Rate (r)’.
   • B4: Title this cell ‘Time to Expiration (T)’ (express this in years).
   • B5: Title this cell ‘Volatility (σ)’.
2. Setting up the Calculation CellsNow, create cells to contain the calculations for the Black-Scholes formula.
   • B7: Title this cell ‘d1’ and enter the formula: =(LN(B1/B2) + (B3 + POWER(B5,2)/2)*B4) / (B5*SQRT(B4))
   • B8: Title this cell ‘d2’ and enter the formula: =B7 - B5*SQRT(B4)
   You’ve now set up your cells to calculate the d1 and d2 values.
3. Setting up the Option Pricing CellsFinally, create cells to calculate the call and put option prices.
   • B10: Title this cell ‘Call Option Price (C)’ and enter the formula: =B1*NORM.S.DIST(B7, TRUE) - B2*EXP(-B4*B3)*NORM.S.DIST(B8, TRUE)
   • B11: Title this cell ‘Put Option Price (P)’ and enter the formula: =B2*EXP(-B4*B3)*NORM.S.DIST(-B8, TRUE) - B1*NORM.S.DIST(-B7, TRUE)

And there you have it! You’ve set up your Excel spreadsheet for calculating option prices using the Black-Scholes model. Now, you can enter your input variables into cells B1 to B5 and see the calculated d1, d2, and option prices update automatically.

Remember to ensure the calculation is set to ‘Automatic’ in Excel for the results to update as soon as you input the variables. If you’re unsure, you can check this by going to ‘Formulas’ > ‘Calculation Options’ > ‘Automatic’ on the Excel toolbar.

Always bear in mind that while the Black-Scholes model is a powerful tool, it makes certain assumptions and oversimplifications. It’s always a good idea to use it in conjunction with other models and investment strategies.

An Example of Use Case

let’s say that you are an options trader and you are considering purchasing a call option. The underlying stock is currently trading at $50 per share, and the strike price of the option is $55. The option will expire in six months, the volatility of the stock is 30% per year, and the current risk-free rate is 5% per year.

In this situation, you would enter the following information into your spreadsheet:

• B1 (Stock Price, S0): $50.00
• B2 (Strike Price, K): $55.00
• B3 (Risk-Free Rate, r): 5% (0.05 when expressed as a decimal)
• B4 (Time to Expiration, T): 0.5 (this represents six months as a fraction of a year)
• B5 (Volatility, σ): 30% (0.30 when expressed as a decimal)

After inputting these values, Excel will automatically compute the values for d1, d2, and the prices for a European call and put option based on the formulas you’ve provided.

The resulting option prices will help you assess whether the call option is over- or under-priced based on the Black-Scholes model. For instance, if the model’s price for the call option is higher than the current market price, you might consider it a good deal and decide to buy.

However, keep in mind that real market prices can deviate from the theoretical Black-Scholes prices due to factors such as market sentiment, supply and demand, and other considerations not accounted for in the model. This example illustrates the application of the Black-Scholes model but should not replace comprehensive financial analysis and risk assessment.

Hypothesis Behind Black Scholes

The Black-Scholes model is based on several assumptions or hypotheses. Here are the key ones:

1. European Options: The Black-Scholes model assumes that the option can only be exercised at the time of expiration. This is characteristic of European options. American options, which can be exercised at any time before expiry, are not accurately priced by the Black-Scholes model without adjustments.
2. No Dividends: The model assumes that the underlying asset does not pay dividends during the life of the option. Although there are modified versions of the Black-Scholes model that do take dividends into account.
3. No Arbitrage: The model assumes that markets are efficient, meaning there are no opportunities for arbitrage. This assumption implies that all assets are correctly priced, and there are no “free lunches” available.
4. Risk-Free Interest Rate: The Black-Scholes model assumes that the risk-free interest rate is constant and known for the term of the option contract. In reality, the risk-free rate can change over time.
5. Lognormal Distribution: The model assumes that the returns on the underlying asset are normally distributed. This is a simplification because in reality, asset returns often exhibit skewness and kurtosis that deviate from a normal distribution.
6. Constant Volatility: The model assumes that the volatility of the underlying asset is constant over the life of the option. However, in reality, volatility often changes over time.
7. Frictionless Markets: The model assumes there are no transaction costs or taxes, and trading of the asset is continuous. In reality, transaction costs exist and can impact the profitability of option strategies.

Each of these assumptions simplifies the reality to make the model mathematically tractable. In actual practice, many of these assumptions are violated. For instance, many options are American options and can be exercised early. Additionally, assets often pay dividends, markets are not always efficient, and volatility is rarely constant. Nonetheless, the Black-Scholes model remains a valuable tool for understanding option pricing and providing a benchmark for market prices.

Conclusion

Implementing the Black-Scholes model in Excel is a powerful tool for anyone involved in options trading or financial analysis. The process provides an understanding of the mathematical underpinnings of options pricing and offers a simple way to estimate the fair price of an option.

By leveraging Excel’s built-in functions, we can break down the complex Black-Scholes formula into manageable parts. This makes the model accessible even for those with limited exposure to finance or mathematics. A well-designed Excel spreadsheet can serve as a versatile tool for modeling various options scenarios and testing different assumptions.

However, it’s essential to remember that the Black-Scholes model, like any model, is an approximation of reality. The assumptions of constant volatility, no dividends, and a risk-free interest rate, among others, are simplifications. In the real world, these conditions are often not met.

As such, while the Black-Scholes model provides a theoretical value for an option, actual market prices may vary due to factors not included in the model, such as the liquidity of the option, the possibility of early exercise, and market sentiment. Therefore, the model should be used as a part of a broader suite of tools and not as a standalone decision-making tool.

n.b: this is not financial advice