Pricing Generic Bespoke Structured Products in Excel using Deriscope. Comparison with Murex, Numerix, LexiFi

Structured products occupy a distinctive space in modern capital markets, sitting at the intersection of derivatives engineering, quantitative finance, and investment product design. Unlike standard financial instruments such as vanilla options, swaps, or bonds—which are typically defined by relatively simple payoff formulas and well-established pricing models—structured products are highly customizable instruments engineered to match specific investment objectives, risk profiles, and market views.

At their core, structured products combine traditional financial components such as bonds, options, forwards, and swaps into a single packaged instrument with a bespoke payoff profile. These products may offer capital protection, enhanced yield, leveraged upside participation, conditional coupons, autocallability, or barrier-based downside exposure. Because of this flexibility, structured products are widely used by investment banks, private banks, asset managers, and institutional investors.

Among all categories, Equity and FX structured products are especially important due to their complexity and commercial relevance. These products are linked to equities, equity baskets, indices, foreign exchange rates, or hybrid combinations of equity and FX underlyings. Common examples include autocallables, barrier reverse convertibles, Phoenix notes, accumulator structures, dual-currency investments, and quanto products.

What makes bespoke structured products particularly challenging is not merely the complexity of their payoff formulas, but the multidimensional nature of their pricing problem. Pricing often requires sophisticated quantitative models, including local volatility, stochastic volatility, correlation modeling, and Monte Carlo simulation. Path dependency, multiple observation dates, barriers, early redemption features, and hybrid asset-class interactions significantly increase computational complexity.

This complexity creates a major operational challenge: enabling traders, structurers, and quantitative analysts to model, price, and risk-manage bespoke structured products efficiently—often under tight commercial deadlines. As a result, demand continues to grow for powerful structured product pricing platforms, especially those that combine advanced analytics with Excel integration, allowing users to design and price complex bespoke products within familiar spreadsheet environments.

Table Of Contents

1. Structured Product Pricing Platforms: LexiFi, Numerix, Murex and Deriscope
   
2. LexiFi Script for defining a Bonus Enhanced Note
3. Numerix Script for defining a Bonus Enhanced Note
4. Murex Script for defining a Bonus Enhanced Note
5. The Deriscope Object Paradigm for Structured Products
6. The Contingent Claim type
7. The Payoff Policy type
8. The Structured Product type
9. Default Construction and Pricing of a trivial Structured Product in Deriscope
10. Default Construction and Pricing of a trivial Structured Product with Knock-Out in Deriscope
11. Building a Complex Bespoke Structured Product in Deriscope
12. The Payoff Policy of a Bonus Enhanced Note
13. The Contingent Claim of a Bonus Enhanced Note
14. The Trigger Region of the KI Claim of a Bonus Enhanced Note
15. The Triggered Payoff of the KI Claim of a Bonus Enhanced Note
16. The Non Triggered Payoff of the KI Claim of a Bonus Enhanced Note
17. Pricing the Complex Bespoke Structured Product in Deriscope
18. Using a Functions-based Contingent Claim instead of a Payoffs-based KI Claim
19. Inspecting the Cash Flows in the simulated Scenarios
20. Download Spreadsheet

Structured Product Pricing Platforms: LexiFi, Numerix, Murex and Deriscope

Three major platforms dominate discussions around bespoke structured product pricing technology: LexiFi, Numerix CrossAsset XL, and Murex MX.3, each reflecting a distinct philosophy in structured product pricing and risk management.

LexiFi is particularly well regarded for mathematical rigor and contract expressiveness. Its Apropos platform uses MLFi, a functional domain-specific language rooted in formal contract algebra, enabling highly elegant and precise representation of complex structured payoffs. This makes LexiFi especially attractive for pricing bespoke equity-linked and hybrid products requiring robust payoff modeling. Its main strength lies in clarity, precision, and quantitative sophistication. However, LexiFi is less accessible to non-technical users, has limited Excel integration, and offers narrower asset class coverage than larger cross-asset platforms.

Numerix CrossAsset XL takes a markedly different approach, prioritizing flexibility, breadth, and accessibility. Delivered as an Excel-native add-in, it allows traders, structurers, and quants to define payoffs, build models, and run valuations directly within spreadsheets. Its broad model library and cross-asset coverage spanning equity, FX, rates, credit, and commodities make it highly versatile. The platform is especially attractive for desks that rely heavily on Excel-based workflows. Its main limitations are reduced formal rigor in payoff representation compared with LexiFi and less seamless enterprise lifecycle integration than full front-to-back systems.

Murex MX.3 occupies a different category altogether. Rather than functioning primarily as a pricing library, Murex is a full enterprise capital markets platform integrating trade capture, pricing, risk management, collateral, operations, and post-trade workflows. Its Python-based bespoke payoff framework provides strong flexibility for structured products across asset classes. Murex excels in scalability, governance, and enterprise-wide consistency. Its drawbacks are complexity, high implementation costs, and a steeper learning curve for desks focused primarily on fast pricing and structuring rather than infrastructure.Top of Form

Deriscope is an Excel-integrated derivatives pricing platform which, since 2026, has been enhanced with a generic structured products pricing capability.

Below is a comparative table: 

LexiFi Script for defining a Bonus Enhanced Note

To demonstrate how various systems define and price generic bespoke structured products, I will use as example the Enhanced Bonus Equity Linked Note I dealt with in my previous article, the Pricing Equity Linked Notes in Excel: The Bonus Enhanced Note.

The specific Enhanced Bonus Note — Worst-of XYZ1|US (ref=100) & XYZ2|US (ref=50) is defined by the following parameters: 

LexiFi uses a contract description language (also called MLFi or the LexiFi grammar), which is an elegant, composable way to describe financial contracts formally.

Everything in MLFi is built from a few primitives:

• zero — The contract that does nothing (pays nothing, ever). The identity element.
• one(currency) — Receive one unit of a currency now.
• give(contract) — Flip the direction: what you receive becomes what you pay.
• scale(observable, contract) — Scale a contract by some observable value (e.g., a stock price or a fixed number).
• when(condition, contract) — Execute a contract when a condition becomes true.
• anytime(condition, contract) — The holder may exercise the contract at any time while a condition holds (American-style optionality).
• until(condition, contract) — Knock out the contract if/when a condition is met.
• and(c1, c2) — Hold both contracts simultaneously.
• or(c1, c2) — The holder chooses one of the two contracts (i.e., an option).

MLFi also defines a few observables, of which the value varies over time:

For example:

• konst(5.0) — A constant value
• spot("AAPL") — The spot price of Apple stock
• libor("3M", "USD") — A floating interest rate
• date() — The current date
• before(date), after(date) — Boolean observables for time conditions

In the current example, the complete MLFi script is as follows. Any text between (* … *) is considered comments and is ignored by the MLFi processor.

Full LexiFi MLFi Grammar

(*

Enhanced Bonus Note — Worst-of on XYZ1|US (ref=100) & XYZ2|US (ref=50)

Notional = 100 USD | Tenor = 6M | Issue = 12-Jun-2026

*)

(* ── Observables ──────────────────────────── *)

let s1= observe "XYZ1|US" in(* spot price of XYZ1 *)

let s2= observe "XYZ2|US" in(* spot price of XYZ2 *)

let p1= s1 /. 100.0 in(* performance of XYZ1|US *)

let p2= s2 /. 50.0in(* performance of XYZ2|US *)

let pmin = min_obs p1 p2 in(* worst-of performance *)

(* ── Knock-In state variable ───────────────────── *)

(* A knock-in event occurs if pmin <= 0.90 on any KI observation*)

(* date. We track it as an accumulated boolean (once-true,*)

(* stays-true) over the two bimonthly observation dates.*)

let ki_trigger_1 =

at (date 2026 Aug 12)

(pmin <=. 0.90)(* obs date 1: 12-Aug-2026 *)

in

let ki_trigger_2 =

at (date 2026 Oct 12)

(pmin <=. 0.90)(* obs date 2: 12-Oct-2026 *)

in

(* knocked_in = true if triggered on date 1 OR date 2 *)

let knocked_in = ki_trigger_1 ||. ki_trigger_2 in

(* ── Terminal performance (observed at Last Fixing) ───────────── *)

let p_final = at (date 2026 Dec 12) pmin in

(* ── Redemption payoff (per unit notional, at payment date) ───── *)

(* Segment: fixed bonusB_C <= P < B→payoff = N*B = 110*)

let seg_bonus p =

if_obs (p >=. 1.00 &&. p <. 1.10)

(konst 110.0)

zero

in

(* Segment: equity participationP >= B→payoff = N*P = 100*P *)

let seg_equity_up p =

if_obs (p >=. 1.10)

(100.0 *. p)

zero

in

(* Segment: principal only(used for middle bands) *)

let seg_principal =

konst 100.0

in

(* ── Payoff when knocked IN ─────────────────── *)

let payoff_ki p =

(* red:P < 0.95→(100/0.95)*P *)

if_obs (p <. 0.95)

((100.0 /. 0.95) *. p)

(* black: 0.95 <= P < 1.00→100 *)

(if_obs (p <. 1.00)

seg_principal

(* blue:1.00 <= P < 1.10→110 *)

(if_obs (p <. 1.10)

(konst 110.0)

(* green: P >= 1.10→100*P *)

(100.0 *. p)))

in

(* ── Payoff when NOT knocked in ───────────────── *)

let payoff_no_ki p =

(* red:P < 0.90→(100/0.95)*P *)

if_obs (p <. 0.90)

((100.0 /. 0.95) *. p)

(* protected band: 0.90 <= P < 0.95→100 *)

(if_obs (p <. 0.95)

seg_principal

(* black: 0.95 <= P < 1.00→100 *)

(if_obs (p <. 1.00)

seg_principal

(* blue:1.00 <= P < 1.10→110 *)

(if_obs (p <. 1.10)

(konst 110.0)

(* green: P >= 1.10→100*P *)

(100.0 *. p))))

in

(* ── Combined conditional payoff ─────────────────── *)

let redemption_amount =

if_obs knocked_in

(payoff_kip_final)

(payoff_no_ki p_final)

in

(* ── Full contract: pay redemption_amount in USD on 16-Dec-2026 ─ *)

let contract =

when

(date 2026 Dec 16)(* payment date = last fixing + 2B{US} *)

(scale redemption_amount

(pay USD))

in 

Numerix Script for defining a Bonus Enhanced Note

The exact naming of the various functions in Numerix CrossAsset XL is not publicly available. The syntax is similar to the C language. From public sources, documentation leaks, and the MATLAB interface documentation, one can infer that the script would look like the one below. Lines starting with // contain comments and are fully ignored by the Numerix CrossAsset XL processor.

// Observation dates: 12-Aug-2026, 12-Oct-2026

// Fixing date:12-Dec-2026

// Payment date:16-Dec-2026

KNOCKED_IN = 0;

// KI observation 1: 12-Aug-2026

P1_KI1 = S1(12-Aug-2026) / 100.0;

P2_KI1 = S2(12-Aug-2026) / 50.0;

PMIN_KI1 = MIN(P1_KI1, P2_KI1);

IF (PMIN_KI1 <= 0.90) THEN KNOCKED_IN = 1;

// KI observation 2: 12-Oct-2026

P1_KI2 = S1(12-Oct-2026) / 100.0;

P2_KI2 = S2(12-Oct-2026) / 50.0;

PMIN_KI2 = MIN(P1_KI2, P2_KI2);

IF (PMIN_KI2 <= 0.90) THEN KNOCKED_IN = 1;

// Final performance: 12-Dec-2026

P1_F = S1(12-Dec-2026) / 100.0;

P2_F = S2(12-Dec-2026) / 50.0;

P= MIN(P1_F, P2_F);

// Payoff

IF (KNOCKED_IN == 1) THEN

IF (P < 0.95) THEN PAY = (100.0 / 0.95) * P

ELSE IF (P < 1.00) THEN PAY = 100.0

ELSE IF (P < 1.10) THEN PAY = 110.0

ELSE PAY = 100.0 * P

ELSE

IF (P < 0.90) THEN PAY = (100.0 / 0.95) * P

ELSE IF (P < 0.95) THEN PAY = 100.0

ELSE IF (P < 1.00) THEN PAY = 100.0

ELSE IF (P < 1.10) THEN PAY = 110.0

ELSE PAY = 100.0 * P;

PAYMENT(PAY, 16-Dec-2026, USD); 

Murex Script for defining a Bonus Enhanced Note

Murex's payoff language is based on Python. Its documentation is proprietary and not publicly published in full detail. However, the API token names used in the language are well established in the Murex practitioner community and follow consistent naming conventions. One can infer that the script would look like the one below. Lines starting with # contain comments and are fully ignored by the Murex processor.

# =============================================================================

# MUREX MX.3 USER PAYOFF LANGUAGE (Python)

# Product : Enhanced Bonus Note (Worst-of, 2 Equity Underlyings)

# =============================================================================

#

# PAYOFF LOGIC (per unit notional, i.e. results are in USD when N=100)

# ----------------------------------------------------------------------

# Define at maturity T:

#P_i(T) = S_i(T) / S0_i(normalised performance of each stock)

#P(T)= min(P_1(T), P_2(T))(worst-of performance)

#

# KI event: has occurred if, on ANY KI observation date t_j (EXCLUDING maturity),

#min(P_1(t_j), P_2(t_j)) <= BKI(= 0.90)

#

# Case A – KI NOT triggered during life:

#if P(T) <BKI=>Payoff = N * P(T) / K(loss, proportional)

#if BKI <= P(T) < K=>Payoff = N(principal protected)

#if K<= P(T) < BCALL=>Payoff = N(principal protected)

#if BCALL <= P(T) < B=>Payoff = N * B(bonus)

#if B<= P(T)=>Payoff = N * P(T)(equity participation, non-digital)

#

# Case B – KI HAS been triggered:

#if P(T) <K=>Payoff = N * P(T) / K(loss, proportional)

#if K<= P(T) < BCALL=>Payoff = N(principal protected)

#if BCALL <= P(T) < B=>Payoff = N * B(bonus)

#if B<= P(T)=>Payoff = N * P(T)(equity participation, non-digital)

#

# NOTE ON MUREX TOKEN CONVENTIONS

# --------------------------------

#spot(u, t): simulated spot price of underlying u at simulation date t

#getSchedule(key): returns a list of dates stored under schedule key 'key'

#getParam(key): returns a scalar trade parameter stored under key 'key'

#pay(amount, date): registers a cash payment of 'amount' at settlement 'date'

#setMemory(k, v): stores value v under memory key k (path-state variable)

#getMemory(k): retrieves the stored memory value for key k

#eventDate(): returns the current simulation/observation date

#maturityDate(): returns the note's maturity (valuation) date

#The script is executed event-by-event in chronological order by MX.3's

#Monte Carlo engine. The engine calls the script once per observation date.

# =============================================================================

# -----------------------------------------------------------------------

# SECTION 1: TRADE PARAMETERS

# (These would normally be read from the trade's parameter fields in MX.3.

#They are defined as literals here for clarity and can be replaced with

#getParam() calls once wired to the trade template.)

# -----------------------------------------------------------------------

N= 100.0# Notional in USD

K= 0.95# Strike (down-and-in loss level when KI has occurred)

BKI= 0.90# Knock-In barrier level (as a fraction of reference price)

BCALL= 1.00# Call Barrier

B= 1.10# Bonus Level

DIGITAL = False# Non-digital: investor participates above B

# Reference prices (initial fixings)

S0_1 = 100.0# XYZ1|US

S0_2 =50.0# XYZ2|US

# Underlying names as registered in MX.3's market data environment

UND_1 = "XYZ1|US"

UND_2 = "XYZ2|US"

# Settlement date key (2 business days after maturity, US calendar)

# In MX.3 this offset is handled by the payment date convention on the

# payoff; we reference it symbolically.

PAY_DATE = payDate()# MX.3 built-in: returns the contractual payment date

# (maturity + 2B{US} as configured on the trade)

# Memory key used to track whether a knock-in event has occurred

KI_FLAG_KEY = "ki_triggered"

# -----------------------------------------------------------------------

# SECTION 2: IDENTIFY THE CURRENT OBSERVATION DATE IN THE SCHEDULE

# -----------------------------------------------------------------------

today= eventDate()# Current simulation date being processed

mat_date= maturityDate()# The note's valuation date (12-Dec-2026)

# KI observation schedule (bimonthly, KI Obs at Maturity = FALSE).

# Dates: 12-Aug-2026 and 12-Oct-2026 only.

# These are stored in the trade's schedule field named "KI_DATES".

ki_dates = getSchedule("KI_DATES")

# -----------------------------------------------------------------------

# SECTION 3: KI MONITORING EVENTS (run on each KI observation date)

# -----------------------------------------------------------------------

if today in ki_dates:

# Retrieve current simulated spot prices

s1 = spot(UND_1, today)

s2 = spot(UND_2, today)

# Compute normalised performances

p1 = s1 / S0_1

p2 = s2 / S0_2

# Worst-of performance at this KI observation date

p_min = min(p1, p2)

# Check knock-in condition

if p_min <= BKI:

setMemory(KI_FLAG_KEY, 1.0)# Mark KI as triggered (persistent across events)

# -----------------------------------------------------------------------

# SECTION 4: MATURITY PAYOFF EVENT (run on the valuation date)

# -----------------------------------------------------------------------

if today == mat_date:

# --- 4a. Retrieve terminal spot prices ---

s1_T = spot(UND_1, mat_date)

s2_T = spot(UND_2, mat_date)

# --- 4b. Terminal performances ---

p1_T = s1_T / S0_1

p2_T = s2_T / S0_2

# --- 4c. Worst-of terminal performance ---

P = min(p1_T, p2_T)

# --- 4d. Was a knock-in event triggered during the monitoring period? ---

ki_flag = getMemory(KI_FLAG_KEY)# Returns 0.0 if never set, 1.0 if triggered

# --- 4e. Compute the redemption amount ---

if ki_flag == 1.0:

# ---- CASE A: KI HAS occurred ----

# The full strike K is the loss threshold.

if P < K:

# Equity-linked loss: investor participates proportionally below K

redemption = N * P / K

elif P < BCALL:

# Between K and BCALL: full principal returned

redemption = N

elif P < B:

# Between BCALL and B: fixed bonus paid

redemption = N * B

else:

# At or above B

if DIGITAL:

# Digital variant: fixed bonus regardless of upside

redemption = N * B

else:

# Non-digital variant: full equity participation

redemption = N * P

else:

# ---- CASE B: KI has NOT occurred ----

# The loss zone is narrower: it only applies below BKI.

# Between BKI and K the investor is still protected (principal only).

if P < BKI:

# Equity-linked loss (only for very deep moves below BKI)

redemption = N * P / K

elif P < K:

# Protected zone unique to the non-KI case: BKI <= P < K

redemption = N

elif P < BCALL:

# Between K and BCALL: full principal returned

redemption = N

elif P < B:

# Between BCALL and B: fixed bonus paid

redemption = N * B

else:

# At or above B

if DIGITAL:

# Digital variant

redemption = N * B

else:

# Non-digital variant: full equity participation

redemption = N * P

# --- 4f. Register the cash payment ---

# pay() schedules the computed redemption amount on the settlement date.

pay(redemption, PAY_DATE) 

The Deriscope Object Paradigm for Structured Products

Deriscope is implemented in C++ and exposes all building blocks as independent objects to both Excel and XML interfaces. Its architectural logic is declarative and compositional and in that respect is closer to LexiFi than Numerix. But if LexiFi can be compared with the Assembly language where the user can build any complex structure out of elementary "atoms", Deriscope provides the user with high-level components similar to how an object-oriented language like C++ provides a programmer with ready-made high-level constructs like classes.

A generic structured product is represented in Deriscope as an object of type Structured Product.

Below is a schematic diagram of the structure of the Structured Product type. It contains an arbitrary number N of underlyings plus something called Payoff Policy. The latter is a collection of Contingent Claims (see below) plus a timetable of observation schedules that defines when each Contingent Claim is observed. 

The Payoff Policy contains the conditional and payoff logic of the structured product. Below is a more detailed schematic diagram of its structure.

The central role to the definition of Payoff Policy possesses the concept of Contingent Claim. Below is a simplified schematic diagram of the main parts composing its structure. 

The above diagrams indicate that to fully understand a Structured Product requires the understanding of Payoff Policy and Contingent Claim. I start with the latter.  

The Contingent Claim type

This type represents a payoff stream conditional on the satisfaction of a condition on the market state observed at any observation time t.

Both the market state and the observation time t live outside the Contingent Claim definition.

A Contingent Claim object receives them as input to calculate the following three quantities:

a) A Boolean b (TRUE or FALSE) that determines whether the condition is satisfied.

b) A number ON amt that represents the payoff amount conditional on b being TRUE, which amount is referred below as the ON payoff.

c) A number OFF amt that represents the payoff amount conditional on b being FALSE, which amount is referred below as the OFF payoff.

Below is a detailed description of the above concepts. For a more detailed exposition, look at Contingent Claim.

Market state

The observation time t is some future date.

The market state at t is represented by u numbers x₁ , x₂ , ... , xᵤ.

In the context of a Structured Product referencing u underlyings, in most of the cases the x₁ , x₂ , ... , xᵤ are the market prices of those underlyings "observed" at t.

The word "observed" is in double quotes because t is in the future, when no market prices can be truly observed as of the present time.

The word "observed" applies in the context of a simulation where several scenarios are programmatically constructed - typically tens of thousands - with each scenario containing fixed market prices of the u underlyings that presumably apply at the future time t.

Within the context of one such scenario, one may refer to the numbers x₁ , x₂ , ... , xᵤ produced by the simulation as the market prices "observed" at time t.

Note the word "prices" here means the observed "values" of the underlyings, which are not necessarily regular prices, but may also be "rates", depending on the nature of the underlyings.

For example, assume the underlyings are two stocks named A and B with their respective share prices at t denoted as x_A , x_B.

Then u = 2 and x₁ , x₂ , ... , xᵤ become the array x_A , x_B.

But it could be that only the first underlying is the stock A, while the second underlying is the interest rate r of the 10-year US treasury.

Then x₁ , x₂ , ... , xᵤ become the array x_A , r.

While the above reflects the most common case, the market state can be generalized to also include numbers that do not directly correspond to any underlyings.

A good example is a note referencing the stock A that knocks out when the share price stays above a certain barrier during 3 consecutive observations.

Clearly in this case, at any observation time t, the note's calculated payoff does not depend only on the observed share price x_A.

One also needs to know an additional number, which can be denoted as k, which is defined to equal the number of consecutive observations up to and including the time t where the share price was observed to exceed the given barrier.

In this case, the market state x₁ , x₂ , ... , xᵤ become the array x_A , k.

Condition

Every Contingent Claim is associated with the concept of an event (or condition) of a specific type.

This association is implemented through a predicate function 𝘨 that is included in the Contingent Claim object.

Parenthetically note here that a predicate function is a function that returns a Boolean (TRUE or FALSE).

Concretely, the predicate function 𝘨 is represented by a Deriscope object of type Trigger that is defined through the key Trigger Fn.

The first task of a Contingent Claim at any observation time t is to determine whether the event occurs or not (equivalently, whether the condition is satisfied or not).

The Contingent Claim accomplishes this task by applying the predicate function 𝘨 on the market state array x₁ , x₂ , ... , xᵤ.

If the result is TRUE, the interpretation is that the event occurs (equivalently, the condition is satisfied).

If the result is FALSE, the interpretation is that the event does not occur (equivalently, the condition is not satisfied).

If no function 𝘨 is explicitly supplied through the key Trigger Fn, by default 𝘨 is set to a predicate function that always returns TRUE,

ON Payoff

Every Contingent Claim is associated with the concept of an ON payoff, which is the amount ON amt deemed to be payable if the claim's condition is satisfied at the observation time t.

In fact, ON amt represents a potential payoff because it is only potentially - but not necessarily - paid out, as explained in Payoff Policy.

During simulation, the amount ON amt is calculated only if the claim's condition is satisfied.

Technically, the calculation is performed by a function ƒ_ON represented by a Deriscope object of type Real Function defined through the key On Pmt Fn.

OFF Payoff

Every Contingent Claim is also associated with the concept of an OFF payoff, which is the amount OFF amt deemed to be payable if the claim's condition is not satisfied at the observation time t.

Like the ON amt, the OFF amt also represents a potential payoff because it is only potentially - but not necessarily - paid out, as explained in Payoff Policy.

During simulation, the amount OFF amt is calculated only if the claim's condition is not satisfied.

Technically, the calculation is performed by a function ƒ_OFF represented by a Deriscope object of type Real Function defined through the key Off Pmt Fn. 

The Payoff Policy type

This type contains the rules that define the payoff associated with a Structured Product.

It contains no financial information and no links to the underlyings referenced by the Structured Product.

For reference, below is once more the schematic diagram of its structure.

For a more detailed exposition, look at Payoff Policy.

The Payoff Policy primarily consists of a set of u Contingent Claims - denoted symbolically as - C₁ , C₂ , ... , Cᵤ - specified through the key Contingent Claims.

As explained above, each Contingent Claim Cᵢ specifies a condition to be valuated - expressed as a predicate function 𝘨 and two payoff functions ƒ_ON and ƒ_OFF.

But crucially the following questions remain open:

Question 1

When must the condition 𝘨 be valuated, i.e. what are the observation times t₁ , t₂ , ... , tᵣ corresponding to that condition?

Question 2

If more than one Contingent Claims are observed at the same observation time tᵢ, what is the priority order by which these Contingent Claims are processed at that time tᵢ?

Question 3

Given a specific observation time tᵢ, should the condition indeed be valuated or rather skipped due to prior events?

Question 4

Given an amount amt already calculated by the payoff function ƒ_ON, should that amount indeed be paid at the specified payment time or rather cancelled due to prior events?

Question 5

Given an amount amt already calculated by the payoff function ƒ_OFF, should that amount indeed be paid at the specified payment time or rather cancelled due to prior events?

The Payoff Policy answers all these questions, as follows:

Answer to 1

The Payoff Policy defines a time plan through the key Time Plan that lists all relevant Contingent Claims together with their respective observation times.

This time plan therefore associates every time in a sequence of observation times t₁ , t₂ , ... , tᵣ with at least one Contingent Claim Cᵢ.

This by itself does not mean that the Cᵢ will indeed be processed at tᵢ, as exceptions may arise due to the occurrence of prior events, as it will be explained below.

But it does specify the times when the Cᵢ is at least allowed to be processed.

Answer to 2

It may happen that the observation times of two or more of the C₁ , C₂ , ... , Cᵤ overlap.

In that case, on the overlapping observation times two or more Contingent Claims will apply.

Then the rule is that the relevant Contingent Claims will be processed in the order - from top to bottom - in which they are entered in the time plan table.

Answer to 3

The default rule is the Knock-Out rule according to which if any Contingent Claim Cᵢ is processed at some observation time tᵢ with its predicate function 𝘨 valuating to TRUE, then all other Contingent Claims immediately cease to exist and any amounts already calculated but not yet paid are immediately cancelled and never paid.

But an exception can be defined through the key Alive Triggers, where for each claim Cᵢ, a list of claims may be listed, of which the predicate functions are meant to survive an event associated with that claim Cᵢ.

According therefore to the default Knock-Out rule and the mentioned exception, the evaluation of a condition defined in a claim Cᵢ observed at a time tᵢ will be handled as follows:

a) If no other event has happened so far - i.e. no other prior condition has been evaluated to TRUE - the condition of the claim Cᵢ will be always evaluated.

b) If at least one other event associated with a claim Cₐ (note Cₐ may equal Cᵢ) has occurred previously, the claim's Cᵢ condition will be evaluated only if Cᵢ belongs in the Alive Triggers list of the claim Cₐ.

Note the word "previously" applies also to the case when the event associated with the claim Cₐ occurred at the same time tᵢ but recorded prior to processing the claim Cᵢ.

Answer to 4

When an event associated with a claim Cᵢ occurs - i.e. when that claim's predicate function valuates to TRUE - at some observation time tᵢ, the payoff function ƒ_ON associated with Cᵢ is valuated and the resulting number amt represents an amount payable at some specified payment time T, with T ≥ tᵢ.

This does not mean that the amount amt will be actually paid at T.

According to the default Knock-Out rule mentioned above, the payment will not take place if a prior event has been registered at or before T.

But an exception can be defined through the key Alive On Payoffs, where for each claim Cᵢ, a list of claims may be listed, of which all ƒ_ON -related amounts that have been calculated prior to that claim's event will be deemed payable and therefore not affected by that claim's event.

According therefore to the default Knock-Out rule and the mentioned exception, the amount amt calculated by the payoff function ƒ_ON and payable at T resulting from an event associated with a claim Cᵢ observed at a time tᵢ will be handled as follows:

a) If no other event has happened before or at T, the amount amt will be always paid at T.

b) If at least one other event associated with a claim Cₐ (note Cₐ may equal Cᵢ) has occurred previously, the amount amt will be paid only if Cᵢ belongs in the Alive On Payoffs list of the claim Cₐ.

Answer to 5

The answer here is the same as that above but replacing the Alive On Payoffs with Alive Off Payoffs.

Concretely, the amount amt calculated by the payoff function ƒ_OFF and payable at T resulting from an event associated with a claim Cᵢ observed at a time tᵢ will be handled as follows:

a) If no other event has happened before or at T, the amount amt will be always paid at T.

b) If at least one other event associated with a claim Cₐ (note Cₐ may equal Cᵢ) has occurred previously, the amount amt will be paid only if Cᵢ belongs in the Alive Off Payoffs list of the claim Cₐ. 

The Structured Product type

For reference, below is once more the schematic diagram of the structure of the Structured Product type.  

The Structured Product type represents a financial contract that references a set of underlyings and pays its holder a stream of payments in accordance with a Payoff Policy.

For a more detailed exposition, look at Structured Product.

It is implemented to contain a Payoff Policy plus u underlyings U₁ , U₂ , ... , Uᵤ that can be a) securities such as stocks or b) indices such as equity indices or fx rates.

The Payoff Policy specifies the future times t₁ , t₂ , ... , tᵣ - referred to as observation times - when the market prices - or rates - of these underlyings are observed and affect the payment stream received by the note holder.

For example, the contract may reference two different stocks, A and B, in which case u = 2.

Let t be one of the specified observation times.

At t, the observed market prices form the array of u real numbers x₁ , x₂ , ... , xᵤ.

In the two stocks example, the array would consist of the two share prices x_A , x_B observed at t.

The payoff stream received by the investor is calculated in accordance with the Payoff Policy as described above. 

Default Construction and Pricing of a trivial Structured Product in Deriscope

Just like LexiFi, Numerix and Murex, Deriscope also has a scripting language in XML format that allows product definition and pricing without the involvement of Excel, as described in my post Almost Corporate-Ready Unlimited-Currency FX Option Portfolio Pricing with and without Excel.

But, due to its tight integration with Excel, for several tasks such as prototyping a new structured product definition, it is much more productive to define the various components directly in Excel with the help of the Deriscope wizard.

The following short video shows how to create a simple structured product with a few mouse clicks by accepting the wizard's default settings.

The image below shows the Deriscope spreadsheet formula =ds(A3:B6) in cell A2 of a sheet named WizardDefProduct that returns the handle name &StrProd_A2:1.1 that uniquely identifies the corresponding Deriscope Object of Deriscope Type Structured Product. 

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 entries appearing in green font are simple links to other cells. The arrows show the linked cell pairs.

The interesting inputs to the formula in cell A2 are the following two key-value pairs:

Underlying= %XYZ1|US

Payoff Policy= &PayPol_D2:1.1

The wizard has created by default a simple structure that involves only one underlying, which is here a US stock identified with the label %XYZ1|US. Below I will show how one can instruct the wizard to involve an arbitrary number of underlyings.

The crucial entry is the object that is passed as input in cell B6 in association with the key Payoff Policy. This object is generated in cell D2, with the formula shown below: 

As is obvious, this Payoff Policy consists of only one Contingent Claim that carries the label TERMINAL and observed on only one date, the 12-Jun-2027, according to the Payoff Policy's time plan.

Below is the formula =ds(G3:H10) that creates the Contingent Claim object in cell G2: 

This is a very simple Contingent Claim that represents a certain payoff of 100 USD settled 2 business days after the corresponding event is observed. The event is defined by the trigger function object &RealBoolTransf_G12:1.1 in cell H6, which is a link to cell G12 containing the formula =ds(G13:H15) as below:  

The &RealBoolTransf_G12:1.1 object represents the constant predicate function that returns always TRUE. In other words, the corresponding condition identified by the label TERMINAL is satisfied whenever it is observed. The observation schedule is not part of the Contingent Claim. It is part of the Payoff Policy and consists of the single date 12-Jun-2027. Therefore, the TERMINAL condition is observed and satisfied on that date only. Equivalently stated, the TERMINAL event occurs on that date only.

Upon occurrence of the TERMINAL event, the ON payoff function &ConstFn_G17:1.1 in cell H7 gets activated and the corresponding amount is calculated. Here the payoff function is the object created in cell G17 by the formula =ds(G18:H20) as below: 

This clearly represents a constant function that returns 100.

Due to the entry On Pmt Delay= %2B{US} in cell H8, this amount will be paid 2 business days the observation date of 12-Jun-2027.

The formulas necessary to price this structured product are described in previous post titled Pricing Equity Linked Notes in Excel: The Bonus Enhanced Note.

Skipping the details, I place all required market and model objects in a separate sheet named MarketModel.

Below is the result with the pricing formula in cell J5 that returns the object &variant__J2:1.1 that contains the NPV (Net Present Value) of 96.0412 that is displayed in cell J2. 

As discussed in my previous post, the object &variant__J2:1.1 also contains details about each scenario (sample) in the first 46 scenarios (samples) generated during the simulation.

Below is the portion displaying the first 3 samples: 

Due to the simplicity of the structured product, which contains a single terminal Contingent Claim that pays flat 100, all samples appear identical except of the simulated stock price as of 14-Jun 2027 which is depicted here enclosed in red rectangles. Note the observation date for the TERMINAL condition is the 14-Jun 2027 because the input date of 12-Jun 2027 happens to be a Saturday. Furthermore, the payment of 100 USD is settled on 16-June-2027 due to the stipulated 2 business days settlement convention. The applicable discount factor in each sample is 0.9604 which multiplies the paid-out amount of 100 to give the PV of 96.0412.

Below is a visual breakdown of the first sample: 

Default Construction and Pricing of a trivial Structured Product with Knock-Out in Deriscope

The image below shows how I managed to build earlier with the help of the wizard the spreadsheet formulas that created a default Structured Product object.  

As seen in the above image, I started by navigating within the Deriscope wizard's Type Selector to the Deriscope type Structured Product.

Then the Deriscope wizard's Browse Area displays in blue font – which indicates the entries are editable – the key-value pairs out of which an object of the selected type can be constructed.

As indicated in the image, I can edit the displayed Payoff Policy object before deciding to click on the Go button. If I click on the pen-on-a-pad icon, I see the following: 

I may now check any of the boxes to enhance the default Payoff Policy with additional features.

To add an upper knock-out barrier, I check the -Add KO Upper Barrier. Then I click on the Ok button to generate the following spreadsheet formulas – after rearranging them a bit for better visibility and storing them in a sheet named WizardDefKoProduct: 

Now this new Payoff Policy consists of two Contingent Claims that are uniquely identified by the label TERMINAL and KO-UPPER-BARRIER respectively.

The TERMINAL claim is the same as before.

According to the Payoff Policy's time plan, the KO-UPPER-BARRIER claim is observed quarterly until the structure's maturity.

Below is the formula =ds(J3:K10) that creates the Contingent Claim object in cell J2: 

The ON payoff function &ConstFn_G17:4.1 in cell K7 is the same as before. It is a constant function that returns 100.

The different element here is the trigger function object &CompRealTrig_J12:4.1 in cell K6, which is a link to cell J12 containing the formula =ds(J13:K16) as below: 

This is an object of type Comp Real Trigger which represents a predicate function that returns TRUE only if the stock's growth factor equals or exceeds 120%. It is constructed as the composition of two functions: The real function object of type MinMax Fn created in cell J18 and the predicate function object of type Number Is Inside created in cell J25.

At each observation date t, the MinMax Fn real function maps the observed stock price S_t to the ratio S_t/R, where R is the reference value of 100 in cell K22. Then the Number Is Inside predicate function picks this ratio and maps it to TRUE only if it belongs in the interval [ 1.2 , +∞ ) specified in cell K28. Note the MIN specification in cell K21 is superfluous here, as it is truly needed only when there exist more than one underlying stock prices.

To summarize, if at the observation date t the ratio S_t/R equals or exceeds 120%, the KO-UPPER-BARRIER event occurs with the consequence that the ON payoff function &ConstFn_G17:1.1 in cell K7 gets activated and the corresponding amount of 100 is paid to the investor.

Below is the result with the pricing formula in cell M5 that returns the object &variant__M5:4.1 that contains the NPV (Net Present Value) of 96.5620 that is displayed in cell M2. The price is higher than before because now the knock-out condition acts favorably to the investor by conditionally redeeming the invested capital earlier than the note's maturity. 

As I did before, below is a screenshot with the first 3 samples of the simulation as displayed on the spreadsheet. The simulated stock prices are shown in red rectangles.

Due to the occurrence of the knock-out event, the last two samples indicate a premature exit. 

Below is a visual breakdown of the third sample:  

Building a Complex Bespoke Structured Product in Deriscope

Using the wizard to build a complex bespoke Structured Product can be quite daunting. An easier approach is to start with an already built-in product that is as close as possible to the intended structure and then modify it as needed.

In my previous article, the Pricing Equity Linked Notes in Excel: The Bonus Enhanced Note, I described such a built-in product, the Bonus Enhanced Note. Assuming my intended structure is a variation of a Bonus Enhanced Note, I can start by creating that same base product as explained in that article. Afterwards, it should be easy to alter its components or add a few new ones until I reach my intended product. In the end, I can verify that the final product behaves as expected by browsing through the cash flows in individual scenarios.

In a new sheet named "BEN using Payoffs" I create in cell A2 an object of type ELN Bonus Enhanced, as seen below: 

Next, I want to represent this product as a Structured Product instance constructed out of Contingent Claim constituents, which I can later edit as needed.

First, I select cell A2 so that the object's contents are displayed in the wizard, as shown below. 

Then I click on the wizard's Function Selector and choose the As Structured Product function.  

Next, I select cell D2 and click on the Go button. Below is the result:  

Next, I select cell D2 to display this object's contents in the wizard:  

Then I click on the wizard's Function Selector and choose the Create function.

I notice the object's contents within the wizard's Browse Area are now shown in blue font, which indicates they can be edited: 

Finally, I select cell G2 and click on the Go button to paste the formulas in the spreadsheet. After rearranging, it all looks as below:  

Let me explain the various components in detail.  

The Payoff Policy of a Bonus Enhanced Note

The Payoff Policy object is created by the formula =ds(J3:K5,J7,J8:K9,J11,J12:K12,J14,J15:K15,J17,J18:K18) in cell J2 as below:  

It is worth noticing it contains only one Contingent Claim object that is passed as input in cell K5.

The Time Plan input table in rows 8 and 9 specifies that the condition associated with that single Contingent Claim – identified as KI - is observed according to a bimonthly schedule defined in cell P9.

The function also references the three optional keys Alive Triggers=, Alive On Payoffs= and Alive Off Payoffs=, but their associated values are essentially empty. Therefore, these three optional keys can be removed from the spreadsheet formula without any effect on the output. 

The Contingent Claim of a Bonus Enhanced Note

The Contingent Claim object is created by the formula =ds(M3:N14,M16:N18) in cell M2 as below:  

The Type is defined in cell N3 as KI Claim instead of the expected Conditional Claim that has been described above. This is ok because KI Claim is one of several subtypes of Conditional Claim. Therefore, the object created here is also regarded to be of type Conditional Claim. Detailed information on the KI Claim type can be found at the documentation page for KI Claim.

The advantage of using KI Claim rather than Conditional Claim is that KI Claim carries a more direct financial interpretation albeit at the cost of being able to represent only a restricted family of claim types. Instead, the Conditional Claim is technically more complex, but it can represent all claim types.

The meaning of most of the entries is self-evident.

Note that the KI Claim does not make use of Function objects to define the trigger and payoff features. In place of the Trigger Fn=, On Pmt Fn= and Off Pmt Fn= keys of the Conditional Claim, it uses the keys Trigger Region=, Triggered Payoff= and Non Triggered Payoff= respectively, of which the associated values are non-Function types.

In the last section I will show how to convert the KI Claim to the corresponding Conditional Claim so that the Structured Note object makes use of Functions instead of Payoffs.

Below I describe in detail the values associated with the keys Trigger Region=,

Triggered Payoff= and Non Triggered Payoff=. 

The Trigger Region of the KI Claim of a Bonus Enhanced Note

The trigger region is a time-dependent interval in the axis of real numbers which is used here as follows:

If at least one of the growth factors S₁(t) / R₁ and S₂(t) / R₂ lies inside that interval at the observation time t, the KI event is triggered.

Here the interval is constant over time and equal to (-∞ , 0.9].

The interval is defined only on the times t implied by the Schedule object in cell P9, which is the same bimonthly schedule used in the definition of the Payoff Policy.

The Step Up Schedule entry here is superfluous since there is no step up defined and can be safely removed from the input.

Here the trigger region is represented by an object of type Scheduled Real Interval that is created by the formula =ds(P3:Q7) in cell P2 as below: 

As mentioned above, the trigger region corresponds to a predicate function associated with the key Trigger Fn= of the container type Conditional Claim.

In the last section where I use a Conditional Claim instead of a KI Claim, the corresponding predicate function is explicitly constructed and used.

But the predicate function can be also seen here in the wizard by browsing through the contents of the KI Claim object, as seen below. 

If I click on the lens shown in red circle, I see the following:  

Doing the same once more, I can see the top-level details of the predicate function:  

By clicking on the lens icons, I can dig deeper into the details of this function. 

The Triggered Payoff of the KI Claim of a Bonus Enhanced Note

The payoff contingent on the occurrence of a KI event is represented by an object of type Composite Payoff created in cell S2 as shown below:  

This payoff's four constituents correspond to the four linear segments of the note's payoff profile depicted below:  

The Composite Payoff type represents several different payoffs, i.e. objects of type Contingent Payoff acting concurrently in the context of Structured Note.

The payoffs are supplied as an array input through the key Payoffs.

The task of each payoff is to calculate the paid amount when the performance – or growth factor – of the applicable underlying has a certain observed value at any given time, provided the observed value falls within the payoff's defined domain.

The ordering of the payoffs in the array is important because in the case where the domains of any two payoffs overlap, the prevailing payoff is determined by a priority rule according to which a payoff lying higher in the array takes precedence.

Note, the domain of a Composite Payoff is the whole real axis (-∞ , +∞), but the payoff is not zero only on the union of the domains of the constituent payoffs.

More details on the Composite Payoff type can be found on the Composite Payoff documentation page.

The red and green line segments are represented by objects of type Linear Payoff which is described in detail in the Linear Payoff documentation page.

The black and blue horizontal line segments are represented by objects of type Digital Payoff which is described in detail in the Digital Payoff documentation page.

As mentioned above, the Triggered Payoff corresponds to a real function associated with the key On Pmt Fn= of the container type Conditional Claim.

In the last section where I use a Conditional Claim instead of a KI Claim, the corresponding real function is explicitly constructed and used.

The real function can be also seen here in the wizard by browsing through the contents of the KI Claim object, as was done above for the trigger function. 

The Non Triggered Payoff of the KI Claim of a Bonus Enhanced Note

The payoff contingent on the non-occurrence of a KI event is represented by an object of type Composite Payoff created in cell S2 as shown below:  

This payoff's four constituents correspond to the four linear segments of the note's payoff profile depicted below:  

As mentioned above, the Non Triggered Payoff corresponds to a real function associated with the key Off Pmt Fn= of the container type Conditional Claim.

In the last section where I use a Conditional Claim instead of a KI Claim, the corresponding real function is explicitly constructed and used.

The real function can be seen in the wizard by browsing through the contents of the KI Claim object, as was done above for the trigger function. 

Pricing the Complex Bespoke Structured Product in Deriscope

Below is the result with the pricing formula in cell Y5 that returns the object &variant__Y5:1.1 that contains the NPV (Net Present Value) of 93.7386 that is displayed in cell Y2.

As expected, the price is identical with that calculated in my previous post for the original object of type ELN Bonus Enhanced. 

Using a Functions-based Contingent Claim instead of a Payoffs-based KI Claim

If one prefers to define the claim in terms of more flexible lower-level Function objects instead of less flexible higher-level Payoff objects, one must convert the KI Claim to the corresponding Conditional Claim by using the Deriscope Local Function named As Conditional Claim similarly to how I used the Deriscope Local Function As Structured Note to convert the ELN Bonus Enhanced object to the corresponding Structured Note object. In the screenshot below, the Conditional Claim object is created in cell G2 in a new sheet named "BEN using Functions" with the formula =ds(G3:H17). 

The correspondence with the previous sheet is obvious:

Trigger Region → Trigger Function

Triggered Payoff → On Payment Function

Non Triggered Payoff → Off Payment Function

The new blocks define the trigger and the two payoffs in terms of elementary objects of type Function. This is clearly more technically demanding but also more flexible and powerful when it comes to incorporating new exotic trigger and payoff types.

The pricing of this Structured Note returns the same price of 93.7386 as expected. 

Inspecting the Cash Flows in the simulated Scenarios

I can see the cash flows per each scenario (i.e. sample) in the wizard as below: 

It is also possible to have this data returned dynamically by a function on the spreadsheet, as below:  

Instructions on how to create this table including a description of the appeared data can be found at my last post's section titled Inspecting the Cash Flows in the simulated Scenarios.  

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