Monte Carlo Pricing of any European Structured Product in Excel: Revisiting the Morgan Stanley Trigger Plus 2024 Note

In my previous article I showed how one can use Deriscope in Excel to calculate the price of a particular type of a structured product that pays the minimum of two equity indices observed at some specified future terminal time. As example was used the Trigger Plus note issued by Morgan Stanley on April 1, 2019, which is based on the value of the worst performing of the Dow Jones Industrial Average and the Russel 2000 Index due April 4, 2024 and a payoff diagram as below: 

Recommended for Deriscope starters: The Overview and Quick Guide pages.

The pricing approach was based on the observation that the note's payoff happened to be exactly the same with the one arising from a properly constructed portfolio of elementary options. One could then build either the equivalent portfolio out of the constituent options or a tradable product of type Asset2 Linked, which is a special Deriscope type designed to represent this particular structured note. In both cases, the price was calculated as the sum of the prices of the constituent options. Since the latter were produced from closed-form formulas of the Black Scholes type, the resulting structured note price was both efficient and precise.

Below you see the three spreadsheet formulas that were needed to create an object of type Asset2 Linked in cell B2, an object of type Asset2 Linked Mkt in cell E7 and the final output price in USD of 996.95 in cell E2:

Unfortunately, not all structured note payoffs can be broken down into elementary option payoffs that can be priced with exact formulas. For example, the payoff may depend on something as complicated as the minimum of certain sub-maxima or even a non-linear function of the underlying asset prices. All such cases can be handled by Monte Carlo, whereby a) the payoff is defined as a function of the underlying variables, b) the future values of all underlying variables as of the given maturity are simulated, c) per each simulation, the payoff amount is calculated by evaluating the payoff function and finally d) the structured note's price is calculated as the discounted average of all evaluated payoff amounts. 

As an example of this general technique, I will now reproduce the above price of 996.95 USD of one share of the Trigger Plus note. 

The Trigger Plus Payoff as a Function ƒ of Two Variables 

Let me denote by x the (simulated) quote of the Dow Jones Industrial Average index at maturity T, i.e. x = DJI(T), where DJI(t) is the Dow Jones Industrial Average index observed at time t.

Similarly, denote by y the (simulated) quote of the Russel 2000 index at maturity T, i.e. y = RUT(T), where RUT(t) is the Russel 2000 index observed at time t.

Then one share of the Trigger Plus note would result in a payoff at maturity that can be expressed as a certain function ƒ of the variables x and y. The crucial part is to write down the correct expression for the function ƒ.

It turns out that ƒ can be written as the composite function:

ƒ = N⸰g⸰min⸰ℎ

where:

ℎ is the array function that maps the pair (x,y) to the pair (x/x_0, y/y₀) with x₀ = DJI(0) and y₀ = RUT (0) are the spot prices of the two indices as observed today,

min is the function that returns the minimum of its input array of numbers

and

g is a piecewise linear function that maps the number produced by min⸰ℎ to the final payoff (per unit notional) according to the following graph: 

Finally, N represents the notional (or principal) per share, expressed here as a simple multiplier, i.e. a linear function that multiplies its input with the given principal constant, which equals 1,000 in our case.  

Deriscope Functions as Excel Objects 

Deriscope has a type called Function that represents a generic mapping from anything to anything.

The type Function contains a subtype called Real Function that is restricted to mappings from K real variables (single numbers) into N real variables (single numbers).

In turn Real Function contains several concrete subtypes that represent functions that do a specified job.

Some of these subtypes are instrumental in building more complex functions out of simple ones.

For example:

The Linear Fn subtype represents a linear multi-variate function that maps u real variable numbers x₁, x₂, ..., xᵤ to one number.

The Comp Real Fn subtype represents the composition of r constituent functions ƒ₁, ƒ₂, ..., ƒᵣ.

The Integral subtype assigns to any number x the integral of a given function from a fixed origin to x.

The Order Fn subtype returns the input variables re-ordered according to specified criteria.

The Projection Fn subtype maps an array of u real variable numbers x₁, x₂, ..., xᵤ to an array of v numbers xₑ₍₁₎, xₑ₍₂₎, ..., xₑ₍ᵥ₎ the elements of which already exist in the input array.

The Union1D Fn subtype represents a function produced by r functions ƒ₁, ƒ₂, ..., ƒᵣ that are defined on disconnected subsets of the real axis.

Each and every function ƒ supports the Evaluate method that returns the value ƒ(p) calculated by the caller function ƒ at the point p located in the function's domain.

Every function that maps a single number to a single number supports the Integrate method that calculates the integral between two given points and the Solve method that effectively inverts the function.

I can select any function type in the wizard as shown below and then have the respective formula generated and pasted in the spreadsheet by hitting the Go button. 

Spreadsheet Construction of the Array Function ℎ 

As mentioned above, ℎ is defined as ℎ: (x, y) → (ℎ₁(x, y), ℎ₂(x, y)) = (x/x_0, y/y₀)

Deriscope provides a special type called ArrayFn that creates an object representing an array function (i.e. a function that returns an array consisting of several real numbers), out of a set of given scalar (i.e. returning a single number) functions that return the components of the output array.

In our case, the two component functions are:

h₁: (x, y) → x/x₀

and

h₂: (x, y) → y/y₀

Function h₁ is a linear function that maps the two variables (x, y) to a number produced by multiplying the first variable x by a constant coefficient 1/x₀ and ignoring the second variable y. It can be represented by the Deriscope type LinearFn, as shown below: 

The coefficient shown as 3.86877E-05 is just the ratio 1/x₀, where x₀ is the initial Dow Jones Industrial index, assumed here as 25,848.

Function h₂ is a linear function that maps the two variables (x, y) to a number produced by multiplying the second variable y by a constant coefficient 1/y₀ and ignoring the first variable x. It can also be represented by the Deriscope type Linear Fn, as shown below: 

The coefficient shown as 0.000643915 is just the ratio 1/y₀, where y₀ is the initial Russel 2000 index, assumed here as 1,553.

Here the optional Vars= input must be explicitly supplied with the value 2 in order to indicate that the corresponding function acts on an array of at least two variables and the supplied coefficient should multiply the second of the input variables.

Now I am ready to construct the function ℎ as follows: 

Spreadsheet Construction of the Function min 

min is defined as min: (x, y) → minimum of (x_, y)

Deriscope provides a special type called MinMaxFn that creates an object representing the function that acts on any number of input variables and returns a number equaling their minimum or maximum.

This is shown below: 

Spreadsheet Construction of the Function g 

g is defined as g: x → g(x)

in a piecewise linear fashion according to the graph presented above.

This can be handled by the Deriscope special type Union1DFn that creates an object representing a function g defined as the union of n simpler functions g_1, g_2, …, g_n, where each g_i, i = 1,…,n maps one real variable from a domain D_i to one real variable and all domains D₁, D₂, …, D_n are disjoint.

The complete construction looks as follows:

The function object &g.1 is created in cell H21 as an object of type Union1DFn with the 3 nodes 0.6, 1 and 1.1825 that partition the x axis into four disjoint segments.

The first segment is the open interval (-∞, 0.6), on which the linear function &LinFn_E21:2.1 created in cell E21 applies.The interval is open on 0.6 because the corresponding Included= value is set to 0, which means the corresponding node is not included in the interval that ends at that node.

The second segment is the half-open interval [0.6, 1), on which the linear function &LinFn_E26:2.1 created in cell E26 applies.

The third segment is the half-open interval [1, 1.1825), on which the linear function &LinFn_E31:2.1 created in cell E31 applies.

Finally, the last segment is the half-open interval [1.1825, +∞), on which the linear function &LinFn_E37:2.1 created in cell E37 applies. 

Spreadsheet Construction of the Function N 

N is defined as N: (x) → notional * x = 1,000 x

where notional (or principal) is a given constant that in our case equals 1,000.

It is represented below as a linear function that multiplies its single real variable input with the constant coefficient of 1,000:

Spreadsheet Construction of the Payoff Function ƒ 

Now I am ready to build the object that represents the payoff function ƒ.

We know that ƒ is defined as the composition of the already built functions N, g, min and ℎ.

Formally:

ƒ := N⸰g⸰min⸰ℎ

I make use the special Deriscope type Comp Real Fn, which creates a composite function out of the array of its constituent functions, as shown below:

Verification that the Payoff Function ƒ is Correct 

At this stage it is important to ensure that the constructed function ƒ truly represents the prescribed payoff function of the two terminal prices of the DJI and RUT indices.

Deriscope provides a function called Evaluate that can be called off any object of type Function. The following short video shows how I use the wizard to paste the initial function template:

Below you see the formula =ds(K14:L15,K17,K18:L18) pasted by the wizard in cell K13. It calls the function named Evaluate on the function object &f.1 with one single input key named x. This key stands for the input variable to the function, which variable may also be multi-dimensional. The wizard knew that the given function &f.1 expects a two real numbers as input and has therefore assigned the two-cell range K18:L18 containing the numbers 0 and 0 as the array value associated with the key x.  

My next job is to shift the output cell K13 next to the input cells K18 and L18 as shown below:  

and finally extend the row 18 by copying and pasting as shown below, whereby I reenter the formula this time as an array formula that references the extended range K18:L23. This array formula evaluates the function &f.1 repeatedly R times, where R is the number of rows in range K18:L23. Each row supplies a pair of numbers that are used as input to the respective evaluation. The end result of this multiple evaluation is returned as an array in the output range M18:M23.  

I must now replace the zeros with sensible values that will allow me to judge the correctness of the function ƒ.

Remember that ƒ expects as input a pair of two variables (x, y), where x is the terminal price of the DJI index and y is the terminal price of the RUT index.

Since the payoff amount depends on the minimum of the two returns realized by the two indices at maturity, it would be easier to assume that one index, for example the first one, has a very high terminal price so that the payoff amount will effectively depend only on the terminal price of the second index.

My final table is shown below, where I have used the fixed number of 50,000 for the value associated with the first input variable, i.e. the terminal price of the DJI index.

I have also set the varying values of the second input variable – the terminal prices of the RUT index – so that they start with zero and increase by a fixed amount equaling 10% of the spot price of 1,553. 

It now becomes obvious that function ƒ indeed returns the payoff expected by my structured note's term sheet.

For example, rows 62 to 66 indicate that when the terminal price of the RUT index varies from 932 to 1,553, i.e. from 60% to 100% of its spot price, the payoff amount stays flat at 1,000 $.

Then at row 67, when the RUT terminal price becomes 110% of its initial spot price, realizing thus a total return of 10%, the note's payoff becomes 1,400 that represents a 40% gain in relation with the initial investment of 1,000. This is in perfect agreement with the stipulated multiplier of 4 as long as the earnings that do not exceed the upper barrier of 73%.

The latter barrier is crossed in the bottom three rows, where the payoff function returns the flat number 1,730.

Here is the chart constructed out of the above numbers: 

Spreadsheet Construction of the Trigger Plus Structured Note 

The following short video shows the few mouse clicks that instruct the wizard to insert in the designated spreadsheet cell the Deriscope formula that creates an object of type Structured Note.

As usually, the wizard uses default input values for demonstration purposes, so it will be my job to replace these values afterwards. 

Below is what the wizard created: 

As you see, the Create function of the type Structured Note expects three key-value pairs.

The first key is called Payoff Fn and its associated value is an object of type Real Function. Here the wizard has created a temporary object of type LinearFn called &LinFnC5:2.1, which I will shortly replace with my already constructed &f.1 object.

Note this key corresponds to the most important property of the Structured Note object since it contains the payoff's mathematical logic, which essentially characterizes the respective note.

The second key is called Eval Date and its associated value is the date when the note's terminal payoff amount is calculated.

Note that Deriscope allows the optional specification of a later payment date, when the payoff amount is paid. Here no such date is entered, which means the amount is paid on the same date when it is evaluated.

The third key is called Underlyings and expects an array of objects, the types of which should derive from either Tradable or Quotable. Here the wizard has created an array of two objects: one named %RDSB.L|GB, which is of type Stock and represents the Royal Dutch Shell plc and one named &FXB10:2.1, which is of type FX and represents the fx rate USD/GBP. I will shortly replace them with %^DJI|USD|P and %^RUT|USD|P that are of type Stock Index Portfolio and represent my two equity indices. Note though, that the name parts DJI and RUT do not convey any special meaning and only used by Deriscope for identification purposes when the input spot prices, vols and correlations are processed. I could – for example – replace everywhere DJI with XYZ and still get the same price for my structured note.

This is how my final formula looks like after I have made all replacements: 

Monte Carlo Pricing of the Trigger Plus Structured Note 

In order to calculate the fair price of my Structured Note, I will have to supply several market data as input. For example, in the current Trigger Plus case, I would need the following 8 separate market data:

• 1) Spot quote of DJI
• 2) Spot quote of RUT
• 3) Expected dividend yield of DJI until maturity
• 4) Expected dividend yield of RUT until maturity
• 5) Expected vol of DJI until maturity
• 6) Expected vol of RUT until maturity
• 7) Expected correlation between DJI and RUT until maturity
• 8) USD discounting interest rate yield curve until maturity

It is almost a miracle (!) that I do not have to create the corresponding 8 spreadsheet formulas by hand. As a matter of fact, I do not even need to know the exact number and type of the required market data! The wizard is smart enough to know what market data are needed by analyzing the structure of the supplied Structured Note object. It then creates all required spreadsheet formulas with default flat input numbers for simplicity. It will be my job to replace the wizard-generated default flat values with realistic and perhaps non-flat (i.e. maturity-dependent) numbers.

The following video shows how I can use the wizard to create within seconds all spreadsheet formulas required for the pricing of my Structured Note object. 

After some shuffling around and replacing all wizard-generated default values with my original market data, I end up with the pricing formula in cell Q2 that returns the price of 977.57 as shown below.

Columns N and O are dedicated for the market data objects, while the formula in cell Q10 creates the object of type Model[Structured Note] that contains the specification parameters of the Monte Carlo simulation.

The shown price of 977.57 differs substantially from 996.95 due to the very small number (10) of Monte Carlo scenarios specified in cell R13. 

Convergence to the Exact Black Scholes Price 

If everything is correct, the Monte Carlo price ought to converge to the price of 996.95 calculated earlier off the Asset2 Linked object.

While I can manually set a much bigger number in cell R13 and recalculate the spreadsheet, it would be instructive to show you how I can set up a table that shows the dependence of the Monte Carlo price on the number of scenarios.

For that I would first need the object of type Valuation that corresponds to the price output in cell Q2.

I can achieve this by changing the Output= value in cell R7 from Price to Price+Input. Actually, I undertake this change on a replicated region so that I keep my current results intact.

By the way, note the choice Price+Input is listed on the validation dropdown that appears when I select the respective cell R7. while the Deriscope task pane is open.

Below you see that my ds formula in cell T2 returns the object &StrNoteVal_T2:1.1, which is of type Valuation and contains not only the price, but also the model and market inputs supplied in cells U5 and U6 that were used towards the calculation of the price. 

My next step is to let the wizard create a formula that revalues this Valuation object using modified input, as the next video shows.  

The resulting formula is shown below in cell T10:  

The wizard created the above formula by using certain default inputs. In particular, it revalued the Valuation object &StrNoteVal_T2:1.1 by modifying the contents of only one object, the $StrNote_K14:1.1 specified in cell U13. It has also picked up the key EVALDATE of that object and replaced its current value with the date 04/04/2024 specified in cell U15.

Quite obviously, all I need to do is replacing these wizard-generated choices with appropriate entries that will implement my task at hand.

Below is the same formula after my manual changes that results in a price output of 976.19 due to a modified Scenarios value of 20: 

At this point, it would be trivial to set up a table, where the left column contains different values for the Scenarios key and the right column contains the corresponding prices.

But I would also like to include two additional columns that report the Monte Carlo error estimate (one standard deviation) and the time in seconds taken by each price calculation.

Unfortunately, the Revalue function above only returns the price as a real number in cell T10.

The solution is to modify slightly the input by inserting one row containing the optional key named Target Key, with its associated value set to Extra Data, as shown below.

Since the output cell T18 is selected, the contents of the object &Variant_T18:1.1 are shown in the wizard. Apart from the note's price, they also include the standard deviation, variance and processing time in seconds associated with the Monte Carlo calculation. 

Now it is easy to fill up a column with spreadsheet formulas that all reference the same range T19:U23, but differ with respect to their dependence on the number of scenarios, i.e. the value associated with the key Ref Values.

Below is how the final table looks like.

The cell X3 is selected so that its formula is visible in the formula bar and the dependency arrows are also displayed.

The formulas in the next three columns simply return the respective numerical element contained in the referenced object in column X. These formulas have been generated by the wizard in the usual manner. Finally, all first-row formulas have been copied down to the remaining rows.

As you see, running a Monte Carlo with 200,000 scenarios results to a price of 997.03, which still differs by 0.08 from the exact price of 996.95. Nevertheless, the estimated standard deviation is 0.61, so that this result is well within the margin implied by one standard deviation. 

This later calculation has taken 1.09 seconds on my 2.6 GHz pc.

I may run a one-time calculation using 10,000,000 scenarios, which takes about one minute and gives me the result shown below.

This calculation took 56 seconds and produced a price of 996.96 with a standard deviation of 0.09.

With the given notional of 1,000, the standard deviation of 0.09 represents a bit less of a basis point, which represents the 1/10,000 of 1,000, i.e. 0.1.

In other words, I would need to run a Monte Carlo with 10,000,000 scenarios in order to reduce the standard deviation down to one basis point. 

I may improve the accuracy of the Monte Carlo simulation by replacing the current pseudo-random random generator with a low discrepancy (Sobol) random generator.

All I need to do is repeat the latter calculations with a new Model[Structured Note] object, which is a clone of the original one, albeit with its Random Engine property set to Low Discrepancy, as shown below: 

Putting everything together, the following chart is constructed.

It shows clearly that the Low Discrepancy (Sobol) method delivers much better results than the Pseudo Random method, as the respective grey curve converges faster and with fewer oscillations than the blue curve to the horizontal orange line (exact price) as the number of scenarios increases.

You may notice that the grey Low Discrepancy curve seems to oscillate around the orange exact price line. In particular, it seems to deviate again from the orange curve at the far right extreme when the number of scenarios grows beyond 180,000.

In fact, the observed oscillation is endemic to the Sobol algorithm, which gives reliable results only when the sample size is a power of 2.

You may read my article about Pseudo Random versus Sobol Monte Carlo for a more detailed treatment of this structured note using Sobol and also for more explanations on general questions regarding Low Discrepancy simulations.

In that article you will also find out how to compute the standard error in a Sobol simulation by applying the technique of Randomized Low Discrepancy Monte Carlo. 

You will also find out how to force parallel processing so that all logical CPUs are utilized in a multithreading environment and the simulation is completed in a much faster time. 

Click on StructProdTriggerPlus2.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