Another Black-Scholes function for Excel

There are many of these out there on the web, so what the heck, why not another one. (What surprises me is some people charge for these things, wonder who buys from them, I probably would spend an hour or two building my own.)

This custom Excel based formula calculates option values for a European option with no dividends. What I like about this (other than the fact that I wrote it myself) is that it calculates everything in one go.

Excel based Black Scholes calculation - array formula

Well, why talk about it - here is what a sample calculation with my formula looks like.


With one single formula, you get the call value, the put value, and all the greeks, plus a mathematical proof of the put-call parity in one go.

You can download the spreadsheet from this link. The spreadsheet is unprotected, so you can look at the code and change it.

Using the function
1. The formula BS takes the format '=BS(stock price, option exercise price, volatility, time to expiry, risk free rate)'
2. Enter the values for p, x, v, t and r anywhere in your spreadsheet.
3. Enter the formula '=BS(p,x,v,t,r)', referring the correct cells for the values of p, x, v, t and r respectively.
4. Click on the cell where you entered the formula above, then press shift and select 12 rows by 4 columns, press F2, and then Ctrl+Shift+Enter together.
5. That's it!

This formula uses an annually compounded exchange rate, and not a continuously compounded rate.

If you only know the continuously compounded rate, you will have to derive the annual rate and use that instead of the continously compounded rate. It's easy to do that: to convert a continously compounded rate to an annually compounded rate, use the Excel formula "=EXP(r)-1" where r is the continuously compounded rate.

(It is equally easy to convert from a given annual rate to continuous rate - just use the Excel function "=LN(1+annual rate)")