Black & Scholes for Puts/Calls in a Single Excel Cell

Sometimes an online option calculator isn’t enough and you’d like to implement the Black & Scholes (B&S) option pricing equations in Excel.  If you’re just playing around it doesn’t matter how you structure the calculation.  In fact, for clarity’s sake, it’s probably a good idea to spread out the calculation across multiple cells.  However, if you’re planning to do some serious work with multiple B&S calculations then having the formula condensed into one cell is very useful.  For example, it enables a simple copy and paste operation to create a two-dimensional array of option price calculations.

This post describes a parameterized single-cell B&S calculation that handles either put or call options and shows how it can be used as a building block for several different types of option simulations.  At the end there’s a procedure for downloading a free copy of the spreadsheet containing the single-cell formula and several example usages.  For a nice tutorial treatment of the B&S equations in Excel see this MacroOption post.  

The Equations

The Black & Scholes Option Price Equations, including dividends for calls (C) and puts (P) are:

Where:

The parameters/symbols / abbreviations are:

Elaborations:

• (% p. a.) = Annualized percentage
•  e^x  = Euler’s number to the X^th power, implemented as exp() in Excel
• ln(x) = Natural Logarithm of x, implemented as ln(x) in Excel
•  N(x)  = Cumulative Distribution Function (CDF), of a standard normal distribution (mean of zero and standard deviation of one) implemented as Norm.S.Dist (x,1) in Excel

An Excel Implementation

• The put and call versions of the Black & Scholes equation are shown as separate equations above but the two equations can be merged into a single equation by adding an additional parameter which has the value of 1 for calls and -1 for puts.
• In the worksheet “Single” of the Excel spreadsheet I created, I use absolute cell references (e.g., $B$5) for all 7 parameters. Using absolute cell references simplifies making more complex arrangements later.
  • $B$4:  Interest Rate (1% per year = 0.01)
  • $B$5:  Annualized Dividend Rate (2% per year = 0.02)
  • $B$6: Put/Call Switch (Put = -1, Call = 1)
  • $B$7: Annualized Volatility (10% = 0.1)
  • $B$8: Days until Expiration
  • $B$9 Strike Price (exercise price of the option)
  • $B$10 Underlying Security Price
• The B&S pricing formula in the Single sheet
  • =$B$6*$B$10*EXP(-$B$5*$B$8/365)*NORM.S.DIST($B$6*(LN($B$10/$B$9)+($B$4-$B$5+$B$7^2/2)*$B$8/365)/($B$7*SQRT($B$8/365)),1)-$B$6*$B$9*EXP(-$B$4*$B$8/365)*NORM.S.DIST($B$6*((LN($B$10/$B$9)+($B$4-$B$5+$B$7^2/2)*$B$8/365)/($B$7*SQRT($B$8/365))-$B$7*SQRT($B$8/365)),1)
• The Single worksheet of the spreadsheet looks like this:

Modeling Option Prices with the Black & Scholes Equations

To show how this single-cell implementation of the B&S can be useful, I’ll go through a detailed example copying this single sheet and then modifying it to set up a two-dimensional simulation of option prices, varying both the value of the underlying and the time until expiration. 

Steps

1. Copy & paste the entirety of the “Single” sheet to another sheet, my example sheet is “2D example”
2. On the new sheet, drag (or Cut and paste) the B&S equation cell from B13 to F13.  Since all the parameters use absolute references (e.g., $B$5) the parameters all stay the same.
3. Drag (or Cut & paste) the Days Until Expiration cell (DTE), $B$8 to F12.  When you do this operation Excel will change the Days until Expiration references in the equation from $B$8 to $F$12.  It is important to do cut & paste, not copy & paste because Excel will not modify the formula if you just do a copy operation.
4. Drag (or Cut & paste) the Underlying cell $B$10 to Days to E13. 

The spreadsheet now looks like this:

• Now I create the range of the Underlying (UL) and DTE values that I want to use.  I type ‘=D13+1’ in cell E14 (no quotes) and then copy cell E14 from E15 to E33, and I type ‘=F12-5’ into G12 and then copy G12 into Cells H12 through L12.
• Next, I want to change the UL and DTE references in the formula from absolute cell references to be relatively referenced.  This allows the formula to access variables placed in the appropriate rows and columns respectively.  To do this I use Excel’s find and replace function to exchange all UL references, $E$13 in the formula with $E13 and all DTE references, $F$13 with F$13. These references are used a total of 10 times in the formula.  Doing these changes manually even once is error-prone. The sheet now looks like this.
  
  

Now, after those changes, I can copy & paste the B&S formula in F13 into F13 though L33 and Excel will automatically change the cell references for UL and DTE to reference the appropriate cells.  After doing this copy & paste, changing the Strike price to $100 in cell b9, changing the Days to Expiration to 31 in F12, and doing a little formatting/charting, I have the following result.

Changing the Call/Put parameter B6 from 1 to -1 gives the put dynamics:

Any of the B&S parameters can be made into a variable using the approach I demonstrated above.  The next picture shows the Put/Call parameter turned into a variable also.  The sheet is named “Puts + Calls”.

Modeling Issues

Probably the biggest weakness of the B&S model is that it assumes that options will exhibit the same implied volatility regardless of what strike they are at, or how long they have until expiration.  The market knows this is a bad assumption.  For example, out-of-the-money (OTM) puts will tend to have a higher price than the B&S approach would predict.

You can compensate for implied volatility dynamics in simulations by making the volatility parameter, a computed variable (e.g., you could make volatility dependent on the difference between the underlying and the strike price).  Unfortunately, it’s challenging to model implied volatility reliably.  For example, if the market does have a correction sometimes the implied volatility of an OTM put stays the same even when the underlying price has dropped considerably.

The good news is that unless you’re trying to do some pretty sophisticated modeling a constant volatility assumption is probably good enough.

Conclusion

Developing an investment strategy that uses options is a difficult exercise.  Options strategies require selection of strike prices, expiration dates, and roll procedures.  Once developed it’s tough to backtest strategies that include options because historical options data is seldom free, usually has errors, and often lacks important information (e.g., Greeks).

While far from a perfect tool, using the Black & Scholes equation for simulating option strategies is relatively straightforward, requiring estimates of only three parameters, implied volatility risk-free interest rates, and annualized dividends.  It’s tough to prove something will work, but simulation can often demonstrate that a strategy likely won’t work.   

To obtain a free downloaded version of my spreadsheet press the “Add to Cart” button at the bottom of this post, then press the purple “Proceed to Checkout”, “Place Order” (scroll down), and “Single Excel Black & Scholes” buttons on successive screens. The file should then download to your system.