7 minutes reading time (1493 words)

Pricing of Risk Scaling Options in Excel

cover

You are all familiar with simple European call options that are securities promising to pay their holders at some agreed future time T the difference S(T) – K between the price S(T) realized by a monitored underlying S at time T and a fixed amount K, under the condition that S(T) is greater than K. The last long sentence may be compressed to the simple mathematical expression max{ S(T) – K, 0 } for the payoff of the European call option at time T


Investments Seen as Risk Scaling Options 

Entering into an investment that is designed to proceed only after certain conditions are met is usually modelled as buying an exotic option the strike of which, rather than being constant, is a function ƒ(S) of the value S of the underlying asset.

A quick example is taking a privately held company public. Indeed investing in such a company prior to the IPO (Initial Public Offering) event is analogous to purchasing an option that pays at time T of the public offering the amount w*max{ S(T) – COST, 0 }, where w is the weight of the ownership of the company that results from the investment, S(T) is the company's total equity price after the IPO at time T and COST represents the collective fees of the investment bank involved in the underwriting.

Since the latter is an increasing function ƒ(S(T)) of the company value S(T), we end up with an option the strike of which is a function ƒ of the underlying price.

While valuing an option linked to a generic function ƒ can be handled only through time-expensive numerical algorithms, there exists a closed form formula when ƒ is the power function ƒ(S) = Sα, α being a constant. This is often a reasonable modelling assumption in real world situations, like the one described above. 


Risk Scaling Options (RSO)

In their paper in 2014, Blenman & Clark consider European options with payoff max{ ±(βS – λK1-αSα), 0 }, where β, λ, K, α are fixed parameters, S is the underlying price observed at expiry time T and ± distinguishes between the call (+) and put (-) version of the option. They refer to these options as "Risk Scaling Options" or RSOs.

Defining the exercise price as the quantity K1-αSα, the parameter α acts as an index of exercise price uncertainty.

As α tends to 0 the exercise price approaches the constant K. As α tends to unity the exercise price tends to λS, in which case – even though the exercise price becomes completely random – we will know from the outset whether the option will be exercised at maturity. For cases where β > 1 and α = 1, the option will always be exercised at maturity if the company is alive. Intermediate values of α generate an averaging function of K and S.

β and λ are participation parameters that define the payoff to the option buyer given the exercise price K1-αSα and asset price S. 


Calculating the RSO Price

As outlined in detail in the Blenman & Clark paper a closed form formula can be attained by a straightforward integration of the payoff function, when one assumes a lognormal distribution of the terminal underlying price S(T).

An alternative approach lends itself after realizing that an RSO is equivalent to a portfolio of two options with deterministic exercise prices. Specifically, one notes that the payoff can be expressed (non-uniquely) as the weighted difference between the payoffs of an ordinary call option and a fractional power option with carefully chosen weights.

The final result for the price C of the call RSO is the formula below, where the functional dependence of C on the terminal underlying price S, the time to expiry in annual units t, the average continuously compounded interest rate r, the lognormal volatility σ, the underlying's dividend yield δ and the product parameters α,λ,β,K is explicitly shown:

C(S,t,r,σ,δ;α,λ,β,K) = βe-δtSN(d1) - λe-αδtYN(d2)

where

d1 = [(1-α)ln(S/K) - lnλ + lnβ + (1-α)(r-δ+σ²/2)t] / [(1-α)σt½]

d2 = [(1-α)ln(S/K) - lnλ + lnβ + (1-α)(r-δ+σ²(α-½))t] / [(1-α)σt½]

Y = K1-αSαexp(α-1)(r+ασ²/2)t

and N(.)denotes the cumulative standard normal distribution function.

The put RSO price is given by

P(S,t,r,σ,δ;α,λ,β,K) = - βe-δtS[1-N(d1)] + λe-αδtY[1-N(d2)]


Creating a Risk Scaling Option in Excel

It is possible to represent a Risk Scaling Option in Excel by using Deriscope. In the short video below, I use the wizard to produce an object of type Stock Option with its Payoff Type set to RSO

The produced formulas are shown below. Because cell A1 is selected, the wizard displays the contents of the object created in that cell. 


Understanding the Two Formulas

Cell A1 contains the formula =ds(A2:B6), which takes one input argument and returns the text &SIE.DEOpt_A1:1.3

The prefix & indicates that &SIEB.DEOpt_A1:1.3 is the handle name of some object, which in this case is an object of type Stock Option as indicated by the entry in cell B2.

The input range A2:B6 contains the input, which is in the form of key/value pairs arranged in two columns. The first column contains the keys that are labels ending with the equal sign =, whereas the second column contains the respective values.

Some of the values may represent objects. Here the value associated with the key Payoff= is an abject of type Payoff represented by the handle name &Payoff_A8:1.3, which is actually created in cell A8 by the formula =ds(A9:B16).

The important ingredients here are the pair PayoffType= RSO followed by four pairs that define the four RSO payoff parameters β, λ, K, α. 


The Price Function

The following video shows how I use the wizard to generate the spreadsheet formulas required by the Price function:

Below is a spreadsheet image with the pricing formula =ds(D2:E4) in cell D1 that returns the RSO price of 99.03921056.

All market data are neatly grouped together in a separate object of type Option Mkt created in cell D6 and passed as input to the pricing formula in cell E4.

You may notice that the curves generated by the wizard are trivially flat and therefore not particularly realistic. It is very simple to replace these curves with more complex non-flat curves, without affecting the rest of the spreadsheet. For example, you may want to incorporate a yield curve bootstrapped out of market deposit, futures and swap rates by creating the respective Yield Curve object as described in this article. 


Additional Output Data

During the price calculation, QuantLib also computes certain values that can be of interest.

As you see at the previous image, when the cell D1 is selected, the wizard displays the sentence "To display extra pricing data click here" in its Info Area. If I click on the here hyperlink, I see the following: 

Beneath the price at the top, you can also see several greeks, in particular the delta, gamma, vega, theta, rho, warp, lambda gearing, beta gearing, K gearing, followed by other interesting quantities.

The exact definition of each quantity is displayed at the bottom when the respective cell is selected.

It is possible to bring these data in my spreadsheet by extending the pricing formula in cell D1 so that it takes the additional optional input key-value pair Output= Full. 


Convergence to Black Scholes

It is worthwhile to check if our RSO pricing gives the correct Black Scholes result if the RSO payoff parameters are chosen to correspond to a fixed strike option.

As a matter of fact, this happens when α is set to 0, which is the case here. Furthermore, as noted above, the effective strike is given by K1-αSα, which then equals K in our current case.

Then all you need, is a) to create a Stock Option object with vanilla payoff and strike set to K and b) price it using the exact same market data input.

The following image shows that the calculated price (in cell J1) of the Stock Option object (in cell G1) exactly equals that of the RSO (in cell D1), as expected:


Click on RSO.xlsx to download the spreadsheet produced with the above steps.

Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com

How to compute the VaR: Step-by-Step Excel Guide
Market Data in Excel from IEX