# Pricing Structured Products in Excel: The Morgan Stanley Trigger Plus 2024 Note

Various structured products can be precisely priced in Excel using Deriscope and its underlying QuantLib analytics. In this article I will focus on the *Trigger Plus* product issued by Morgan Stanley on April 1, 2019. It is based on the value of the worst performing of the Dow Jones Industrial Average and the Russel 2000 Index due April 4, 2024. It offers limited capital protection and upside leverage, as the excerpt of Morgan Stanley's preliminary prospectus below indicates:

If you are not familiar with Deriscope, visit the Quick Guide for an introductory tour to the most important Deriscope features.

###
Structured Note Description

For a given agreed principal amount **N**, eg **1,000$**, committed by the investor, the Trigger Plus 5-year note pays an amount **X** on the maturity date **T _{M}** of

**April 4. 2024**and no intermediate coupons.

The amount **X** depends on the minimum of the two indices, **DJI** and **RUT**, as observed on the pricing date **T _{P}** of

**March 29, 2019**and the valuation date

**T**of

_{V}**April 1, 2024**. Both indices are assumed rescaled so that both start at the same level – eg

**1,000**– on the pricing date

**T**.

_{P}Note that **T _{P}** is

**3**business days earlier than the issuance date

**T**of

_{I}**April 3, 2019**and similarly for the

**T**

_{V}_{ }in relation to the maturity

**T**.

_{M}Let **S _{0}** be the minimum rescaled index on the pricing date

**T**and therefore known to the investors who buy the note on or after its issuance on

_{P}**T**.

_{I}Let **S _{T}** be the minimum rescaled index that will be observed on the – almost - maturity date

**T**.

_{V}With regard to the upside, if **S _{T}** >

**S**, the amount

_{0}**X**is defined in such a way that the positive return to the investor

**R = (X - N)/N**will be

**m**times the percentage increase of (

**S**-

_{T}**S**)/

_{0}**S**(upside leverage), where

_{0}**m**is the agreed multiplier –

**4**in this case. An upper bound limit

**θ**of about

**70%**is nevertheless imposed on the return

**R**, which means that

**X**cannot exceed a certain value.

With regard to the downside, if **S _{T}** <

**S**, the amount

_{0}**X**will generally equal the principal

**N**(downside protection), but only if

**S**does not drop too much. More specifically, if

_{T}**S**<

_{T}**φ**

**S**, where

_{0}**φ**is a specified loss trigger that equals

**60%**in this product, then all protection is lifted and

**X**is determined so that the negative return

**R = (X - N)/N**to the investor will exactly equal the – also negative – return (

**S**-

_{T}**S**)/

_{0}**S**realized by the minimum index

_{0}**S**.

_{T}Assuming that both **DJI** and **RUT** indices have been rescaled to equal **1,000** on the pricing date **T _{P}**, I may plot the minimum index realized on maturity (valuation date)

**T**on the

_{V}**x**axis and the respective note payout

**X**on the

**y**axis, with the principal amount

**N = 1,000$**:

### Breakdown into Simpler Products

I can better understand the *Trigger Plus* note by considering it as being equivalent to a portfolio consisting of a *Zero Bond* that pays a guaranteed flat amount equal to the principal **N** (eg **1,000$**) at maturity plus various other options that account for the conditional non-flat payments.

In order to determine the "various other options", I realize that their combined payoff at maturity must equal that of the *Trigger Plus* note minus the fixed amount of **1,000$** due to the zero bond.

In other words, their combined terminal payoff should look like the previous diagram after it has been shifted downwards by **1,000**.

The resulting diagram is plotted below, where one can see two non-vanishing parts:

The left non-vanishing part corresponds to a basket consisting of a short *Put* with a strike of **600** and a short *Cash-Or-Nothing Digital Put* with the same strike of **600** and cash amount of **400**.

The right non-vanishing part corresponds to a *Call Spread* consisting of a long *Call* with a strike of **1,000** and a short *Call* with a strike of **1,175**. Both options in that *Call* *Spread* reference the underlying minimum index with an applied multiplier of **4**.

The conclusion is that the *Trigger Plus* note on an underlying being the minimum of DJI and RUT indices rescaled to equal **1,000** at inception, with a principal of **1,000$**, underlying loss trigger at **600** and underlying max profit threshold at **1,175,** is equivalent to a portfolio consisting of the following five elementary products:

- A
*Zero Bond*paying flat**1,000**at maturity - A short
*Put*with a strike of**600** - A short
*Cash-Or-Nothing Digital Put*with a strike of**600**and cash amount of**400** - A long call with a strike of
**1,000**and an underlying multiplier of**4** - A short
*Call*with a strike of**1,175**and an underlying multiplier of**4**

### Creating the Trigger Plus Note

It is possible to use Deriscope to create the five constituent objects in Excel, calculate their prices and add them together to reach the final price of the *Trigger Plus* note. Since the underlying refers to the minimum of two equity indices, the appropriate type to choose in the Deriscope *Type Selector* when I create each constituent is the *MinMax Option*.

A much simpler approach is to utilize the special type *Asset2 Linked*, which inherits from the more general *Structured* type, which in turn inherits from the even more general *Portfolio* type.

*Asset2 Linked*.

The image below shows both the generated spreadsheet formula in cell **B2** and the wizard on the right, which displays the in-memory-held contents of the object referenced by the handle name &Ass2Lnk_B2:1.1 in the currently selected cell **B2**.

The wizard pasted the formula **=***ds(B3:C13)* in cell **B2** that takes as input the single range **B3:C13** and returns the text &Ass2Lnk_B2:1.1 which is the handle name of a newly created object of type *Asset2 Linked*.

The input data are arranged as *key/value* pairs, where the *keys* are on the first column and carry the = suffix and the *values* are on the second column. The two top *keys*

*Type=* and *Function=* are universal (apply in all contexts), whereas the remaining *keys* supply the details of this particular structured note.

The wizard displays a few key-value pairs that are not present in the spreadsheet range. In particular, the keys *Tradables*= and *Weights*= below the key *In parent Portfolio*= provide the equivalent portfolio representation of the current object. These two keys are part of any object of type *Portfolio* and the reason they are shown here is because the type *Asset2 Linked* of the current object inherits from (i.e. is a special case of) the type *Portfolio*.

It is immediately seen from the handle names prefixed with the **$** sign that the portfolio representation of our *Asset2 Linked* object comprises four objects of type *MinMax Option* and one object of type *Zero Bond*. The weights are also different, whereby a negative number signifies a short position on the respective product.

I can inspect the contents of these objects by clicking on their lens sign.

For example, I see the following after I click on the **$MinMaxOpt#4**:

This is an option on the minimum (as betrayed by the unchecked box next to the *Is Max*= key) on the two indices **DJI** and **RUT**. The respective weights have been set to **0.03869** and **0.6439** in order to make the corresponding weighted shares of **DJI** and **RUT** having a value of **1,000**, when the indices **DJI** and **RUT** attain the values given by the assumed base index levels of **25,848** and **1,553** shown in the previous image. Indeed **0.03869 * 25,848 = 1,000** and **0.6439 * 1,553 = 1,000**.

The expiry of this *European* option is **4/4/2024** and its strike is hidden inside the **$Payoff#2** object. When I click on the latter, I see this is a **Call** option with a strike of **1,000**:

Going back to my spreadsheet, I can get instant information on any key by selecting the corresponding cell. For example, this is what the wizard displays when I select the cell **B11** containing the key *Loss Trigger*=:

###
Pricing

Creating a simple formula that calculates the price of this product is straightforward, as the next video demonstrates:

Below you see the two formulas as pasted by the wizard in cells **E2** and **E7**. All market data are grouped neatly together in a separate object of type *Asset2 Linked Mkt* created in cell **E7**. This object is then fed into the formula in cell **E2** that returns the price of **986.6228163**.

### Additional output data

The *Info Area* of the wizard in the image above displays the sentence "*To display extra pricing data click here". *If I click on the *here* hyperlink, I see the following:

If I then click on the lens sign next to *Breakdown***=**, I obtain the table below, where I can see the contribution of each component to the final price.

It turns out that the biggest contribution by far comes from the Zero Bond, the value of which is **887.29**.

Quite significant is also the contribution from the first item **$MinMaxOpt**, which obviously refers to the long **Call** option with strike **1,000**.

The smallest contribution comes from the third item, which is a *MinMax* *Option* valued at **-4.675**. If I inspect the contents of the respective object **$MinMaxOpt#2**, I will find out that it refers to the short **Put** option with strike **600**.

I may also access all these data within the spreadsheet by calling the pricing formula with the extra *key-value* pair *Output*= **Breakdown **as shown below.

Then the output becomes an object, the contents of which become visible in the wizard as soon as the containing cell **E2** is selected.

Any portion, or all, of the displayed data may be transferred in the spreadsheet as explained in my introductory article about Deriscope

### Solving for the Fair Value of any Input Parameter

It is possible to instruct the *Price Simple* function to return the value of a chosen input variable that leads to a target output result.

For example, I can have the *Price Simple* function returning the appropriate correlation between **DJI** and **RUT** so that the price of the *Trigger Plus* note today equals exactly **1,000**.

As usually, the easiest way to set up the spreadsheet formula that does this job is through the wizard, as the next video demonstrates.

Below you see the formula pasted in cell **H2** that returns the fair correlation value of **0.571970** that implies the target note price of **1,000** specified in cell **I9**.

By appropriately changing the input to that formula, it is possible to calculate the fair value of literally any parameter that has an impact on the note price.

### Plotting the Payoff at Maturity

There exists a variation of the *Price Simple* function that returns the price as if the valuation had taken place on a specified future date. I may therefore set that date to the maturity of the *Trigger Plus* note of **April 4, 2024** and derive the corresponding price in accordance with the market data defined in the input object in cell **E7**.

But what I really want is to setup a list of index spot values and calculate the corresponding note prices as of **April 4, 2024**.

Rather than creating a separate market object for each index spot pair, I can use the optional *Edit Input* key to modify the values of a select set of input parameters that affect the final price.

In the following screenshot you see that formula entered in cell **E28** and pasted in the cells below it to produce the terminal note prices for the index spots defined in columns **C** and **D**.

The payout chart on the right bolsters my confidence that no error has occurred and that the current product correctly represents the *Trigger Plus* note.

### Exposing the Portfolio Structure in the Spreadsheet

Every object the type of which inherits from *Portfolio* has a function called *As Portfolio* that returns its embedded *Portfolio* part.

The next video shows how I use this function to set up a spreadsheet formula that produces the *Portfolio* embedded inside my original *Asset2 Linked* object.

I can easily get the portfolio's constituents explicitly created in the spreadsheet by selecting the newly created *Portfolio* object and then invoking its *Create* function, as the next video shows:

###
Pricing the Portfolio Structure Directly

Since I now have an object of type *Portfolio*, I can invoke its *Price* function, which calculates the price by summing up the prices of its constituent elements. This process is blind to the fact that the calling *Portfolio* object has emerged from an object of type *Asset2 Linked*. The next video shows the required mouse clicks:

The following image shows the state of my spreadsheet so far:

In cell **B2** an object of type *Portfolio* is created by means of the *As Portfolio* function of the calling *Asset2 Linked* object.

This object is then recreated in cell **E2**, this time from explicit input data supplied in the underneath ranges.

Finally, the *Portfolio* object is priced in cell **H2**. Unfortunately, no *Price Simple* function is available for objects of *Portfolio* type, so the more elaborate *Price* function is used instead.

The reason the shown price of **3.868763006** is so far off from the correct price we have seen earlier is that the input market data (not shown in the above snapshot due to their location in the lower rows of the spreadsheet) have been set by the wizard to different values. But if I change manually the market data so that they match those that I used in the *Asset2 Linked *pricing, I will get the exact same price of **986.6228163**.

Click on **StructProdTriggerPlus.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