Finance in Excel + VBA. Black-Scholes Option Calculator
The article is addressed and will be useful primarily to those who have begun to study options and want to understand their pricing. Well, secondly, those who have not yet used the VBA tool in their calculations in Excel, but want to learn - you will see how simple it really is.
To begin with briefly about the essence and pricing options. An option has four main parameters:
1. Underlying asset
2. Type of option (Call or Put)
3. Strike price (option exercise price)
4. Expiration Date (Expiration) of the Option
For an option buyer, it is the right to buy (Call option) or sell (Put option) the underlying asset at the strike price on the expiration day. For the option seller, it represents the obligation to sell (a call option) or buy (a put option) the underlying asset at the strike price on the expiration day. In fact, an option is insurance against a change in the price of the underlying asset (BA) from the moment of the transaction to the expiration date - the seller acts as the insurer (in the event of an adverse change in the BA price, he pays insurance to the option buyer), and the policyholder is the insurer (he pays for the insurance to the seller ).
')
Like the price of insurance, the price of an option is fully determined by the probability of an “insured event”, i.e. execution of an option (execution of the right of the option buyer) The main components that influence this probability and the option price, the cost of insurance, which the buyer pays and the seller receives:
Moreover, the dependence of the option price for each of these three components is non-linear . The generally accepted option pricing formula for these key factors was derived by Fisher Black and Myron Scholes in 1973.
The Black-Scholes formula has the following form (you can see it in detail on Wikipedia):
Price of the (European) call option:
The price of the (European) put option:
Legend:
C (S, t) - the current value of the call option at the time t before the expiration of the option (before expiration);
S - the current price of the underlying asset;
N (x) is the probability that the deviation will be less in the conditions of the standard normal distribution (and thus limit the range of values ​​for the function of the standard normal distribution);
K - option exercise price;
r - risk-free interest rate;
T - t is the time until the option expires;
- volatility of return (square root of the variance) of the underlying asset.
To assess the sensitivity of the option price to the BA price, volatility, and time to expiration, coefficients called Greeks are used (coefficients are generally indicated in Greek letters, with the exception of vega).
The Greeks in the Black-Scholes model are calculated as follows:
1. Delta (
) - the rate of change of the option price from the change in the BA price For an option call delta equals 
for put option 
. Delta shows the current slope of the option value curve, depending on the BA price.
2. Gamma (
) - the rate of change of the option price from the change in Delta (or acceleration from the change in the BA price). Gamma is equal to 
.
3. Vega (
) - describes the dependence of the option price on the change in BA volatility: 
. Vega reflects the number of points of an option value change for each percentage point (1%) of the change in volatility.
4. Theta (
) - describes the reduction in the price of an option depending on the time to expiration. For Call - 
, for Puta - 
.
The above formulas are valid for the general case, including the case of stock options. For the calculation of options on futures contracts, the risk-free rate r is not applicable. Since futures options are traded on the Moscow Stock Exchange; further, we do not take into account the interest rate in the calculations.
So, the implementation of the Black-Scholes model in Excel + VBA.
For convenience, we will create a function for each variable from the BS model. Each function will have input variables:
S - BA price
X - strike price
d - the number of days before expiration
y is the number of days in a year
v - volatility
OptionType - type of the option “Call” or “Put” (only for calculating the price and delta)
Writing a regular function in VBA is as follows:
Such a function can be called from other functions or from an Excel sheet.
Functions are written to the created Module (we start VBA in Excel, for example by pressing Alt + F11, select Insert -> Module):
Ready Excel file can be downloaded from the link .
Now, in the Excel cell, we can call any prescribed function, for example, by entering in the cell = OptionPrice (“Put”; 76870; 90000; 13; 365; 0.47) we will get the theoretical price of the Put option at the price of the underlying asset 76870, strike 90000, the estimated volatility 45 % and 13 days before expiration.
So, we have obtained a working option calculator on VBA, which can be used both to study the properties of options (build diagrams of price dependencies and Greeks on various market parameters), and use them for trading and building more complex programs.
Basics of options
To begin with briefly about the essence and pricing options. An option has four main parameters:
1. Underlying asset
2. Type of option (Call or Put)
3. Strike price (option exercise price)
4. Expiration Date (Expiration) of the Option
For an option buyer, it is the right to buy (Call option) or sell (Put option) the underlying asset at the strike price on the expiration day. For the option seller, it represents the obligation to sell (a call option) or buy (a put option) the underlying asset at the strike price on the expiration day. In fact, an option is insurance against a change in the price of the underlying asset (BA) from the moment of the transaction to the expiration date - the seller acts as the insurer (in the event of an adverse change in the BA price, he pays insurance to the option buyer), and the policyholder is the insurer (he pays for the insurance to the seller ).
')
Like the price of insurance, the price of an option is fully determined by the probability of an “insured event”, i.e. execution of an option (execution of the right of the option buyer) The main components that influence this probability and the option price, the cost of insurance, which the buyer pays and the seller receives:
- The difference between the strike price and the price of the underlying asset . Those. when buying a Call, the higher his strike is, the cheaper it is (since the probability of a lower value of the strike price at the time of expiry of a BA is reduced)
- Base asset volatility . The higher the volatility (rough range of price fluctuations) of the BA, the higher the probability of achieving a strike before expiration.
- Expiration time . The more time before expiration of an option, the higher the probability that during the purchase of a call, the price of the underlying asset will go higher than the strike price, and the option price is higher.
Moreover, the dependence of the option price for each of these three components is non-linear . The generally accepted option pricing formula for these key factors was derived by Fisher Black and Myron Scholes in 1973.
The Black-Scholes formula has the following form (you can see it in detail on Wikipedia):
Price of the (European) call option:
The price of the (European) put option:
Legend:
C (S, t) - the current value of the call option at the time t before the expiration of the option (before expiration);
S - the current price of the underlying asset;
N (x) is the probability that the deviation will be less in the conditions of the standard normal distribution (and thus limit the range of values ​​for the function of the standard normal distribution);
K - option exercise price;
r - risk-free interest rate;
T - t is the time until the option expires;
- volatility of return (square root of the variance) of the underlying asset.Option Greeks
To assess the sensitivity of the option price to the BA price, volatility, and time to expiration, coefficients called Greeks are used (coefficients are generally indicated in Greek letters, with the exception of vega).
The Greeks in the Black-Scholes model are calculated as follows:
1. Delta (
) - the rate of change of the option price from the change in the BA price For an option call delta equals for put option . Delta shows the current slope of the option value curve, depending on the BA price.2. Gamma (
) - the rate of change of the option price from the change in Delta (or acceleration from the change in the BA price). Gamma is equal to .3. Vega (
) - describes the dependence of the option price on the change in BA volatility: . Vega reflects the number of points of an option value change for each percentage point (1%) of the change in volatility.4. Theta (
) - describes the reduction in the price of an option depending on the time to expiration. For Call - , for Puta - .The above formulas are valid for the general case, including the case of stock options. For the calculation of options on futures contracts, the risk-free rate r is not applicable. Since futures options are traded on the Moscow Stock Exchange; further, we do not take into account the interest rate in the calculations.
Model implementation in MS Excel
So, the implementation of the Black-Scholes model in Excel + VBA.
For convenience, we will create a function for each variable from the BS model. Each function will have input variables:
S - BA price
X - strike price
d - the number of days before expiration
y is the number of days in a year
v - volatility
OptionType - type of the option “Call” or “Put” (only for calculating the price and delta)
Writing a regular function in VBA is as follows:
Function Name Functions (comma separated input variables)
... calculations ...
Function Name = ... Calculate ...
End function
Such a function can be called from other functions or from an Excel sheet.
Functions are written to the created Module (we start VBA in Excel, for example by pressing Alt + F11, select Insert -> Module):
Function d_1 (S, X, d, y, v)
T = d / y
d_1 = (Log (S / X) + (0.5 * (v ^ 2)) * T) / (v * (T ^ 0.5))
End function
Function d_2 (S, X, d, y, v)
T = d / y
d_2 = d_1 (S, X, d, y, v) - v * (T ^ 0.5)
End function
Function Nd_1 (S, X, d, y, v)
Nd_1 = Application.NormSDist (d_1 (S, X, d, y, v))
End function
Function Nd_2 (S, X, d, y, v)
Nd_2 = Application.NormSDist (d_2 (S, X, d, y, v))
End function
Function N_d_1 (S, X, d, y, v)
N_d_1 = Application.NormSDist (-d_1 (S, X, d, y, v))
End function
Function N_d_2 (S, X, d, y, v)
N_d_2 = Application.NormSDist (-d_2 (S, X, d, y, v))
End function
Function N1d_1 (S, X, d, y, v)
T = d / y
N1d_1 = 1 / (2 * Application.Pi ()) ^ 0.5 * (Exp (-0.5 * d_1 (S, X, d, y, v) ^ 2))
End function
Function OptionPrice (OptionType, S, X, d, y, v)
If OptionType = "Call" Then
OptionPrice = S * Nd_1 (S, X, d, y, v) - X * Nd_2 (S, X, d, y, v)
ElseIf OptionType = "Put" Then
OptionPrice = X * N_d_2 (S, X, d, y, v) - S * N_d_1 (S, X, d, y, v)
End if
End function
Function Delta (OptionType, S, X, d, y, v)
If OptionType = "Call" Then
Delta = Application.NormSDist (d_1 (S, X, d, y, v))
ElseIf OptionType = "Put" Then
Delta = Application.NormSDist (d_1 (S, X, d, y, v)) - 1
End if
End function
Function Theta (S, X, d, y, v)
T = d / y
Theta = - ((S * v * N1d_1 (S, X, d, y, v)) / (2 * (T ^ 0.5))) / y
End function
Function Gamma (S, X, d, y, v)
T = d / y
Gamma = N1d_1 (S, X, d, y, v) / (S * (v * (T ^ 0.5)))
End function
Function Vega (S, X, d, y, v)
T = d / y
Vega = (S * (T ^ 0.5) * N1d_1 (S, X, d, y, v)) / 100
End function
Ready Excel file can be downloaded from the link .
Now, in the Excel cell, we can call any prescribed function, for example, by entering in the cell = OptionPrice (“Put”; 76870; 90000; 13; 365; 0.47) we will get the theoretical price of the Put option at the price of the underlying asset 76870, strike 90000, the estimated volatility 45 % and 13 days before expiration.
Some points that I would like to mention
- The values ​​of the theory of price in our program are almost identical to those that are broadcast by the Mosbirge, this means that the exchange uses the BS model in its calculations.
- In fact, an option (like insurance) does not have a true fair value - it is for everyone, and depends on what volatility is assumed or, for example, what number of days to consider (whether to take into account weekends, what weight to take different days of the week, how many days in a year use in the formula), etc.
- The Greeks have a remarkable property - to get the value of the Greeks for a portfolio of futures and options you just need to add the corresponding Greeks for the individual assets of the portfolio. Those. we can easily calculate, for example, how much basic futures need to be bought / sold so that the total portfolio value does not change when the price of this futures changes (the so-called Delta alignment or delta hedging).
- In spite of its prevalence, the BS model is based on the assumption that the return on an asset has a normal distribution, that it never does in the real market.
Total
So, we have obtained a working option calculator on VBA, which can be used both to study the properties of options (build diagrams of price dependencies and Greeks on various market parameters), and use them for trading and building more complex programs.
Source: https://habr.com/ru/post/248949/
All Articles