Pricing Equity Linked Notes in Excel: The Bonus Enhanced Note

In my previous article I showed how one can use Deriscope in Excel to calculate through Monte Carlo simulation the price of a European style equity linked note that paid once at maturity an amount linked to the minimum of two equity indices observed at that time.

With the current article I start a series of posts that address the valuation of much more complex equity linked notes, the payoff of which is path dependent, which means the return to the investor does not only depend on the terminal prices of the underlying stocks or indices, but also on the intermediate market prices observed from the time of issue until maturity.

Since this is the first post of a series on complex notes, I have chosen to deal with a relatively simple note that is commonly known in Asian Markets as Bonus Enhanced Equity Linked Note. In the U.S., similar payoff structures exist but under names like Booster Note, Enhanced Return Note, or more generally, under Enhanced Participation categories. In Europe, equivalent notes appear as Bonus Certificates or generic structured notes with barrier/bonus features, depending on issuer and region.

The mentioned path dependency is introduced through a time dependent knock-in feature, whereby a knock-in event is observed periodically at certain times before and optionally at maturity. If the knock-in event occurs at any observation time, the payoff at maturity changes as described in the next section.

In the current article I construct the Bonus Enhanced Note using a spreadsheet Deriscope formula specially designed for this exact type of note. But it is also possible to carry out the construction by treating the note as a generic Structured Product and stitching together generic building blocks. The latter construction is outlined in detail in my post Pricing Generic Bespoke Structured Products in Excel using Deriscope. Comparison with Murex, Numerix, LexiFi.

Below is an image of the front page of a typical term sheet, in this case the 4-month USD Denominated Worst of Bonus Note on Adobe Inc. and Advanced Micro Devices, Inc issued in 2025: 

Table Of Contents

1. Bonus Enhanced Note Summary Description
   
2. Underlying
3. Coupon
4. Knock-Out
5. Knock-In
6. Redemption
7. Redemption if a knock-in event has occurred
8. Redemption if no knock-in event has occurred
9. Creating the Bonus Enhanced Note
10. Pricing
11. Pricing using Sobol Random Generator
12. Pricing using Volatility Smile
13. Pricing using Time Dependent Yield Curve
14. Solving for the Fair Value of any Input Parameter
15. Inspecting the Cash Flows in the simulated Scenarios
16. Download Spreadsheet

Bonus Enhanced Note Summary Description

The note references an arbitrary number of underlying securities that can be individual stocks or equity indices. Its payoff occurs only at maturity and depends on the performance of the worst performing underlying, referred below as the minimum performance.

On the upside, the investor generally wins a fixed bonus but receives only the principal if the minimum performance falls below a fixed level known as call barrier.

On the downside, the investor loses proportionally to the minimum performance if the latter falls below a certain level.

There exist two variants that affect the upside: The digital where the investor only wins the fixed bonus, and the non-digital where the investor participates in the minimum performance hike if the latter becomes more profitable than the fixed bonus.

There is also a knock-in barrier that is observed throughout the note's life with a given frequency.

A potential knock-in event during the life of the note only affects the level of the minimum performance below which the downside comes into effect.

If knocked in, that level is set to the knock-in strike.

If not knocked in, that level is set to the somewhat lower knock-in barrier. 

Underlying

The note references n stocks or stock indices S₁ , S₂ , ... , Sₙ with n ≥ 1, all of the same currency.

At any given observation time t in the future, these stocks (or stock indices) will have corresponding market prices S₁(t) , S₂(t) , ... , Sₙ(t).

The note also defines fixed so-called reference prices S⁰₁ , S⁰₂ , ... , S⁰ₙ.

The payoff and the various triggers depend only on the performances P₁(t) , P₂(t) , ... , Pₙ(t) - also referred as growth factors - defined as follows:

P₁(t) , P₂(t) , ... , Pₙ(t) = S₁(t)/S⁰₁ , S₂(t)/S⁰₂ , ... , Sₙ(t)/S⁰ₙ

Explicitly:

P₁(t)= S₁(t)/S⁰₁

P₂(t)= S₂(t)/S⁰₂

...

Pₙ(t)= Sₙ(t)/S⁰ₙ

From a mathematical perspective, the performances P₁(t) , P₂(t) , ... , Pₙ(t) may be regarded as the note's underlyings. 

Coupon

This note does not pay a regular coupon.  

Knock-Out

This note does not have a knock-out provision.  

Knock-In

 A knock-in event may occur at any time t that is part of a predefined set of v knock-in observation dates Tᴷᴵ₁ , Tᴷᴵ₂ , ... , Tᴷᴵᵥ.

The dates Tᴷᴵ₁ , Tᴷᴵ₂ , ... , Tᴷᴵᵥ are typically set through the stipulation of a knock-in observation frequency, such as monthly.

The knock-in event depends on the minimum performance P_MIN(T) defined as:

P_MIN(t) = min( P₁(t) , P₂(t) , ... , Pₙ(t) )

A knock-in event occurs if:

P_MIN(t) ≤ B_KI

where B_KI is a fixed number - such as 80% - referred to as the knock-in barrier.

The occurrence, or non-occurrence, of a knock-in event affects the redemption payment at the note's maturity, as seen below.

Redemption

The redemption amount at maturity T depends on whether a knock-in event has been observed at or before T.

There exist therefore two scenarios: 

Redemption if a knock-in event has occurred

Let P denote the performance at T of the worst performing asset.

Equivalently, define P as:

P = P_MIN(T)

In this case the payoff diagram at T looks as below, where the horizontal axis spans the P and the vertical axis spans the payoff per unit notional. 

There exist 4 regimes depicted with different colors.

The fourth regime exhibits two variations, one where the investor participates in the equity upside and one where the investor receives a fixed digital payment.

The formulas for the various linear segments are as follows.

Equity Linked Loss segment (red line)

Payoff = (N/K)P if P < K

where N is the note's notional.

Note that in most term sheets this payoff is formulated differently - but equivalently - in terms of an allocation of shares to the investor, as follows:

At maturity T, a number of shares is allocated to the investor that equals the division:

N / (Strike Price of the Laggard underlying)

where

Strike Price of the Laggard underlying = K S⁰, where S⁰ is the reference price of the laggard underlying.";

where

K is the stipulated strike in percentage terms, eg K = 90%

S⁰ is the reference price of the laggard underlying

Note, the note's contract defines fixed reference prices S⁰₁ , S⁰₂ , ... , S⁰ₙ for all the referenced underlyings.

So, formally the investor receives N/(K S⁰) shares of the laggard underlying.

It can be proved that the value to the investor at time T of this transaction equals (N/K) P

where P is the performance at T of the laggard underlying, i.e. P = S/S⁰

where S is the price of the laggard underlying at T

Proof

The investor receives at T N/(K S⁰) shares of the laggard stock.

Since each of these shares has a value at T that equals S, it follows that N/(K S⁰) shares have a total value of N/(K S⁰) S.

The last expression can be written as (N/K) (S/S⁰).

But the fraction S/S⁰ equals the performance P, and therefore the total value becomes:

(N/K) P

Example 1

N 100 USD, K = 90%, P = 90%, then payoff = ((100 USD) / 90%) * 90% = 100 USD

Example 2

N = 100 USD, K = 90%, P = 85%, then payoff = ((100 USD) / 90%) * 85% = 94.44 USD

Principal Only, Zero Return segment (black line)

Payoff = N if K ≤ P < B_CALL

where B_CALL is a fixed number referred to as the call barrier.

Fixed Bonus segment (blue line)

Payoff = B if B_CALL ≤ P < B

where B is a fixed number referred to as the bonus level.

The last segment comes in the following two variants.

Equity Participation variant (inclined green line)

Payoff = NP if B ≤ P

Example

N = 100 USD, B = 113%, P = 120%, then payoff = (100 USD) * 120% = 120 USD

Digital variant (flat green line)

Payoff = B if B ≤ P

Redemption if no knock-in event has occurred

In this case the payoff diagram at T looks as below.  

The only difference between this diagram and the previous one is that in the case here the Equity Linked Loss segment (red line) has shorter length.

It extends to the right only until the x-coordinate equals the knock-in barrier B_KI.

It is therefore obvious that the absence of a knock-in event is advantageous for the investor.

The applicable formula for that segment is:

Payoff = (N/K)P if P < B_KI

Example

N = 100 USD, B_KI = 80%, K = 90%, P = 70%, then payoff = ((100 USD) / 90%) * 70% = 77.77 USD

Payoff = N if B_KI ≤ P < K

Example

N = 100 USD, B_KI = 80%, K = 90%, P = 85%, then payoff = 100 USD, because P > B_KI 

Creating the Bonus Enhanced Note

The following video shows how I let the Deriscope wizard generate the appropriate spreadsheet formula that creates an object of type ELN Bonus Enhanced.

The image below shows the generated spreadsheet formula =ds(A2:B20,A22:B24) in cell A1 that returns an error. The error message appears within the wizard's Browse Area on the right because cell A1 is selected.

According to the displayed message in the wizard's Info Area the error is caused because both underlyings in cells A23 and A24 share the same name. This is easily resolved by manually changing the name of the second underlying from %XYZ1|US to %XYZ2|US

To simplify the input data, I remove the two rows containing the optional keys Maturity and Last Fixing.

I also set Strike= 0.9, KI Obs Freq= Bimonthly and KI Barrier= 0.8.

The wizard has set the default value of 100 for the reference value of both underlyings. I change the value for the second underlying to 50 so that these two values are distinct. Later, I will also set the spot market values to 100 and 50 respectively.

Finally, I change the format of certain numerical inputs to percentage for better readability.

The image below shows that now the formula in cell A2 returns the handle name &ELN-BEN_A1:1.3 of the created Deriscope Object, of which the Deriscope Type is ELN Bonus Enhanced The wizard on the right displays the in-memory-held contents of that object. 

The input data are arranged as key/value pairs, where the keys carry the = suffix and the values are displayed in blue font. The two top keys Type= and Function= are universal (apply in all contexts) and define the applicable Deriscope Type and Deriscope Function respectively. The remaining keys supply the details of this specific Bonus Enhanced note.

Information on any key can be displayed in the wizard's Info Area by selecting the corresponding cell. For example, this is what the wizard displays when I select the cell A17 containing the key KI Obs at Maturity =: 

Pricing

The fair price – i.e. the fair NAV (Net Asset Value) - of this product can be calculated by applying the Price function on the object &ELN-BEN_A2:1.1.

The next video demonstrates how this is done with the help of the wizard: 

The wizard places all formulas one below the other in the same column. In the screenshot below I have rearranged them so that they are all visible. The note price of 98.6717 is returned by the formula =ds(P3:Q7) in cell P2.  

The entries appearing in green font are simple links to other cells. The arrows show the linked cell pairs.

The most important inputs to the pricing formula are the following two key-value pairs:

Models= &SimMdl_M2:1.1

Markets= &Mkt_M16:1.1

As is clearly shown, the object &SimMdl_M2:1.1 represents a Monte Carlo simulation consisting of 10 scenarios based on a pseudo-random number generator with a non-parallel execution in the sense that all simulated scenarios are processed one after the other in the same thread. This is highly inefficient, but it can be easily improved, as will be shown below.

The object &Mkt_M16:1.1 is of type Market Set and is just a container of 8 distinct objects listed at the bottom of column M. The meaning of each of these objects ought to be obvious in this screenshot. 

Pricing using Sobol Random Generator

The simulation model created by the wizard is very inefficient for two reasons: a) It defines a non-parallel execution and b) is based on a pseudo-random number generator which requires a very big number of scenarios – about 100,000 – to reach a satisfactory precision. A better approach is to use a Sobol – also known as Low Discrepancy – number generator that gives decent results with only about 1,000 scenarios, combined with parallel execution. More details on Sobol and parallel execution can be found at my blog article Combining Randomized Quasi Monte Carlo (Sobol) and Parallel Processing (Multithreading) when Pricing Derivatives in Excel.

I start by selecting the range P1:Q7 shown below. 

Next, I copy this range and paste it into cell S1. If my Excel is in automatic calculation mode, I end up with the following:  

So far, the formulas in cells P2 and S2 return the same number since they process the same input.

My aim is to modify the input in cell T6 so that the formula in cell S2 will return the note's price based on a Sobol random number generator with parallel processing.

To do that, I select the range M1:N13 shown below.

Next, I copy this range and paste it into cell S9. If my Excel is in automatic calculation mode, I end up with the following:  

Next, I change the value in cell T14 to specify the Sobol random number generator. If I select cell T14 while the wizard is open, I can a cell dropdown on which I can click and select the desired value out of a list of random number generator types. I see the following list of types in the cell dropdown:  

I select Low Discrepancy, which corresponds to Sobol, but this change causes the formula in cell S10 to return error, as below:  

Fortunately, the error message supplies me with the fix. If I click on the hyperlink at the word [$variant_], I see the following list of valid numbers:  

According to this list, a valid number of simulations close to 1,000 is the number 1,023. If I enter this number in cell T15, the error disappears.

Next, I select Parallel Seq in the dropdown in cell T21 to set parallel execution.

Finally, I ensure the link in cell T16 points to cell S18 and the link in cell T6 points to cell S10.

Below is the result, where I have changed the titles to properly label the Sobol-based pricing: 

The formula in cell S2 returns the price of 96.3116, which is based on a Sobol random number generator with 1023 scenarios processed in parallel. In fact, parallelization occurs in 24 threads because my PC has 24 logical processors. I can verify this fact by inspecting the contents of the object &Parallel_S18:1.1 within the wizard, as follows:  

Pricing using Volatility Smile

So far, I have assumed each underlying stock has constant volatility, both timewise and strikewise. In reality, stocks exhibit a volatility pattern that varies with both time and strike. While this is not relevant to the pricing of European options due to being dependent on exactly one maturity and one strike, it becomes important when structured notes, such as the Bonus Enhanced Equity Linked Note, are priced. The reason is that most structured notes pay the investor an amount the determination of which involves the observed level of the underlying price in relation to several strike levels.

The next video shows how I can use the wizard to create an object that represents a time-dependent volatility surface, which I can then pass as input to the pricing formula in lieu of a flat vol. 

The wizard has created a typical vol object that references MSFT (Microsoft) as its underlying. It has also set the vol table to contain two maturities set at 1 and 5 years.

After changing manually on the spreadsheet the underlying to refer to the one referenced by the note and setting the maturities at 3 and 6 months and adding one more vol section for the second underlying, the spreadsheet looks as below: 

The top row in each vol table contains the strikes expressed in forward moneyness units. Here forward moneyness is defined as the ratio K/F where K is the regular strike and F is the forward stock price for the corresponding maturity shown in the left column.

For example, the value 0.9 means K/F = 0.9 which means strike K equals 90% of the forward price. Concretely, the vol of 26% shown in both tables is the vol for a 6-month maturity and strike K = 90% F where F is the forward price for the respective stock at 6 months maturity.

Note the definition of moneyness here is the inverse of the definition F/K used in the pricing of a single call option where a strike of 90% F would indicate an in-the-money option and therefore a moneyness greater than 1. Indeed, in volatility surfaces it is conventional to define moneyness as the ratio K/F rather than F/K because it is desirable to have the variable quantity K in the numerator.

Putting everything together, the new pricing using the two vol surfaces looks as below: 

The new price of 93.8044 is calculated by the formula =ds(AF3:AG7) in cell AF2. The model being used is the same as that used previously, i.e the Sobol-based with 1023 scenarios. But the market collection differs with regard to the two vol elements shown in bold face in column AF, which now refer to the vol surfaces built in cells V2 and AA2 respectively.  

Pricing using Time Dependent Yield Curve

So far, the pricing has been based on a flat interest rate assumption.

For more accurate pricing, a proper yield curve needs to be constructed.

In the US market the short-term part of the yield curve is typically constructed using OIS rates or futures prices.

More details on USD curve building can be found in my article How to build a SOFR Yield Curve in Excel using QuantLib and Deriscope.

Below is the formula that builds the yield curve out of SOFR OIS rates. 

Below is the formula that builds the yield curve out of SOFR futures prices.  

Below is the previous pricing formula reconstructed in cell AP2 in such a way that it references a new market collection that contains the yield curve built out of OIS rates.

The returned note price is now 93.8179. 

Solving for the Fair Value of any Input Parameter

Practitioners are often more interested in knowing the fair value of certain note parameters, such as the Bonus Level or the Strike, that correspond to some given note NAV rather than calculating the fair NAV when all note parameters are known.

The Deriscope Price function can return the fair value for any chosen input parameter. The easiest way to construct the appropriate Price function is by using the wizard as the following short video demonstrates: 

Below is the pasted formula that returns the Bonus Level of 116.55% that implies the target note NAV of 95 USD specified in cell AT11.  

Inspecting the Cash Flows in the simulated Scenarios

Structured products can be so complicated that there are often misunderstandings even among practitioners about a) the exact meaning of the various conditions and b) the precise calculation of the various payoff amounts.

Such misunderstandings are easily resolved by inspecting the timeline containing all relevant events and cash flows that occur during the lifetime of the product in the course of the simulation.

This detailed information is generated by the Price function if the following two optional inputs are supplied:

• 1.The optional key Reported Scenarios= inside the object of type Model[Simulation] must be defined with a non-zero value. By default, Deriscope sets by default that value to 0 to avoid the overhead associated with generating information pertaining to the simulated scenarios.
• 2.The optional key Output= must be explicitly supplied as input to the Price function with its value set to Full.

Below is how the accordingly modified Price function looks like with Reported Scenarios= 46: 

Due to Output= Full, the Price function does not return a single number but rather an object with the shown handle name &variant__AV2:1.1. The contents of this object are shown in the wizard because cell AV2 is selected. The value of 93.82 is the same as the price of 93.8179 found earlier, but it appears in the wizard with only two decimals precision.

The cash flow data are contained inside the object associated with the key Sim Report=. I can see that object's contents by clicking on the little lens shown here in red circle.

Below is the result: 

The highlighted row at the top shows the dates where the knock-in condition is evaluated. The dates are as expected according to the defined bimonthly knock-in observation frequency.

For the cash flows I need to dig in a little bit further. They are contained inside the object associated with the key Simulated Values=. After I click on the little lens in the red circle and expand the width of the displayed table, I see the following: 

I have highlighted with a reddish color the column pairs that correspond to each scenario – also known as sample. The table is very big since it contains 46 samples. This screenshot displays only the first 4 samples.

For an in-depth understanding of this data, it can be helpful to read the part of another article that starts with the section The Deriscope Object Paradigm for Structured Products.

It will be easier to explain the various segments if I paste this table in the spreadsheet.

I select the empty cell AY2 and then click on the Go button and select the menu item "Paste Object Contents".

Below is the result. 

The formula in cell AZ2 is =ds("ObjectTools::Show","Reference=",$AV$2,"Key=","Sim Report","Simulated Values","ExpandFinal=",TRUE)

It references only cell AZ2 that contains the pricing output and returns a table in the form of a dynamic array. Therefore, if any relevant input in the spreadsheet changes, the returned table will be automatically refreshed to reflect the change.

To make this big table easier to read, I can select any arbitrary cell inside it and click on the ribbon button

while the wizard is open.

Below is the result showing the first 6 samples: 

Let us first take a detailed look at the first sample below. The attached notes explain the meaning of the various segments.  

Below is a similar description for the second sample: 

Below is a similar description for the fourth sample where a knock-in event is observed:  

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