Black-Scholes and RunWorkbook

This topic covers the same ground as the RunWorkbook: Black-Scholes and Values at Risk screencast.

Black Scholes is a mathematical model for options pricing in the financial world.

The example spreadsheet implements the Black-Scholes model so that it can be re-used from another spreadsheet—so demonstrating RunWorkbook, one of the key features enabling you to make your Resolver One spreadsheets easy to maintain and understand.

RunWorkbook enables you to separate data from the several different calculations that use it, and to put your common calculations into worksheets that can be re-used.

You will learn

  • How to use RunWorkbook to access the functionality of other workbooks.

The Black-Scholes Pricing Model

The BlackScholes.rsl workbook has an implementation of the Black-Scholes options pricing model:

The Black-Scholes model implemented in a Resolver One spreadsheet (showing the Fair Value of European Call)

We can edit the values in the yellow square (current stock price, exercise price, risk-free interest rate, duration of the option and the volatility of the stock market). The sheet then calculates d1 and d2, and uses the result to calculate C—the Fair Value of the European Call.

You can see that our option is priced at 84 pence when our stock has a current price of £49. If, say, we change the value of the current stock price to £60, C rises to £6.88.

The formula for d1

The formula calculating d1 uses variables like "S" and "X". These meaningful (to statisticians, at least!) variable names refer to cell locations setup in the 'Edit Names' dialog, for example S comes from cell C2 of the Calculator sheet:

Editing the names that point to cells or cell ranges

The formula for d2

d2 is calculated using the result for d1, sigma and the Square Root built-in function applied to t.

The formula for C

C, the Fair Value of European Call, is calculated using several variables, the exponent function and a call to N, the 'standard normal cumulative distribution function'.

Tip

If you are interested in a Python implementation of the Black-Scholes algorithm then you can see the generated code from the formulae in the 'Formula Code' section of the code pane.

The Black-Scholes User Spreadsheet

These Black-Scholes calculations are non-trivial. You probably want only one person to maintain the sheet, and share the debugged version with the users. You definitely don't want the users to 'improve' the calculations, resulting in buggy copies of the spreadsheet being passed around, with different people's pricing models using different versions.

For these reasons, it's best to keep the Black-Scholes calculations walled off in their own workbook, and have users reference them via the RunWorkbook command.

That's what the next workbook, BlackScholesUser.rsl, demonstrates. It lets us value our derivatives portfolio using, but not opening or editing, the critical BlackScholes.rsl workbook:

The spreadsheet that uses the Black-Scholes implementation via RunWorkbook

Each row in this spreadsheet contains the input values for the Black-Scholes calculation and a resulting Fair Value. As you'd expect, changing the input values triggers a recalculation.

Fair Value calculation

The Fair Value is calculated by calling the previous BlackScholes workbook, passing in the row's input values and extracting the Fair Value. The command is similar to calling a function or method from a regular programming language, e.g.:

=RunWorkbook("BlackScholes.rsl", S:=B7, X:=C7, t:=D7, sigma:=E7, r:=$D$4).C

Explanation

RunWorkbook
First we call the RunWorkbook function.
"BlackScholes.rsl"
Specifies which workbook to call.
S:=B7, X:=C7, t:=D7, sigma:=E7, r:=$D$4
Passes the values for S, X, sigma, r and t.
.C
Identifies the value to be returned.

So, RunWorkbook enables us to treat a workbook as a function.

Conclusion

We implemented a complex pricing model in a single spreadsheet and then reused it from another; passing in input data and retrieving the result.

The BlackScholes.rsl workbook can be stored on a shared, secure network machine, under source-code control. All the users could share this file directly over the network. If the file is updated, then everyone references the new version and you don't have to worry about buggy copies floating around people's hard-drives.

The second half of the screencast shows a different way of using RunWorkbook. You can read more about this in the Value at Risk and RunWorkbook topic.

Comments