# 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_{0})** with

**x**

_{0 }=**DJI(0)**and

**y**

_{0 }=**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) → (ℎ _{1}(x, y), ℎ_{2}(x, y))** =

**(x/x**

_{0, }y/y_{0}) 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 _{1}: (x, y) → x/x_{0}**

and

**h _{2}: (x, y) → y/y_{0}**

Function **h _{1}** 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

_{0}**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 _{0}**, where

**x**is the initial

_{0 }*Dow Jones Industrial*index, assumed here as

**25,848**.

Function **h _{2}** 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

_{0}**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 _{0}**, where

**y**is the initial

_{0 }*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**, where each

_{n}**g**maps one real variable from a domain

_{i}, i = 1,…,n**D**to one real variable and all domains

_{i}**D**are disjoint.

_{1}, D_{2}, …, D_{n}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