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:

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

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_V of 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_I of April 3, 2019 and similarly for the T_V in relation to the maturity T_M.

Let S₀ be the minimum rescaled index on the pricing date T_P and therefore known to the investors who buy the note on or after its issuance on 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 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₀)/ S₀ (upside leverage), where 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 X will generally equal the principal N (downside protection), but only if S_T does not drop too much. More specifically, if S_T < φS₀, where φ 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₀)/ S₀ realized by the minimum index 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_V on the 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.

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