7 minutes reading time (1488 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 8 spreadsheet formulas required by the Price function:

Below is a spreadsheet image where I have shuffled the various blocks around so that the top orange-colored block contains the pricing formula in cell G1 that returns the RSO price of 99.03846154.

At the center, the green-colored block creates the important object of type Market Set in cell G12, which groups together all market elements needed by the pricing formula in G1.

You can clearly see that this Market Set object is built out of 4 objects that contain the yield curve, dividend yield, volatility curve and the spot price of the underlying asset.

The yellow-painted cells contain wizard–generated values that I need to replace with actual current market data.

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. 


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 J15) of that Stock Option object (in cell J1) exactly equals that of the RSO (in cell G1), as expected: 


Additional Output Data

During the price calculation, QuantLib also computes certain values that can be of interest. When cell G1 is selected, the wizard displays the following in its Info Area:

If I click on the here hyperlink of the top sentence "To display extra pricing data click here", 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 – in this case warp – is selected.

I have already explained here how you may transfer these data in the spreadsheet. 


Advanced Topic: Following the Pricing Algorithm Step-By-Step 

It is also possible to track the pricing algorithm that has been run in order to produce the observed output and also see the actually used intermediate structures and numerical outputs as described in the respective section of my Interest Rate Swap article.


You may download the spreadsheet produced with the above steps here. 

File Name: RSO
File Size: 15 kb
Download File

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