Deriscope

## The Excel Derivatives Periscope

PnL_Explain

This is a breakdown of the PnL of the referenced tradable between two given dates into various components, expressed as a sequence of key-value pairs.

Web blog example
here

The top pair corresponds to the PnL expressed in currency units as of the latest of the two dates, also referred as horizon date and represented by T
All quantities are expressed in terms of the seven variables S, H, I, C, F, Fᵣ, P as shown in the table
PnLExplain Table
Below are the descriptions of the rows in that table, keeping the original order:

PnL

The Profit and Loss (PnL) is defined as the sum of realized and unrealized profit incurred between the given spot date and the given horizon date T specified in

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

By default, the spot date equals the globally set trade date ( i.e. valuation date) that usually equals today's date.
But if the
Key Tradable::Price::As Of#1 entry to the function Function Tradable::Price exists, the latter date is then regarded as the spot date.
It is represented by the time 0 in the documentation context of

Precisely, PnL represents an amount regarded as being received at the horizon date T and defined as:
PnL = H + I - S/P, where
H is the tradable's price at T as described in
I is the actual income realized between the given spot date (denoted as 0) and T as described in
S is the tradable's price at 0 as described in
P is the discount factor for maturity T implied by the curve associated with the issuer supplied in
Key Advanced Pricing::Fund Crv Issuer as described in Advanced Pricing::Job Request::Funding DF

One may also define an equivalent PnL amount that is assumed to be payable at 0 and be equal to the product PnL*P

Note the quantity subtracted from H is not S, but rather S/P
This is the correct treatment for a P&L defined in an "economic" rather than "nominal" sense and satisfies the economic criterium that the P&L owes to be exactly 0 when the market volatility vanishes.
The alternative definition involving S rather than S/P leads to a "nominal" P&L that fails to satisfy this criterium, but still reported under

PnL Nominal
This is similar to the
Advanced Pricing::Job Request::PnL, with the only difference being that the S/P in the definition PnL = H + I - S/P is replaced by S

So the definition of PnL Nominal is:
PnL Nominal = H + I - S

Note, this definition is not "economically" correct, in the sense it leads to a non-zero result when the market volatility vanishes.
It is nevertheless important as it is generally used to determine margin requirements in daily P&L intervals.

Also note, the income part I is not treated in the same sense like S
Concretely, the added cash flow amounts are not evaluated at the time they are paid.
They are first compounded forward to the horizon time and then are summed over to form the income I, thus evaluated as of the horizon time.
In the context of daily P&L where the PnL Nominal is mostly used, this is of no concern since the involved cash flows occur exactly at the horizon time.

PnL Gross
This simply equals the
Advanced Pricing::Job Request::PnL after all exogenous cash flows, such as transaction fees, have been added to it.

Precisely, PnL Gross represents an amount regarded as being received at the horizon date T and defined as:
PnL Gross = PnL + E, where
PnL is the (endogenous, i.e. without inclusion of fees) PnL as described in
E is the exogenous income due to transaction-linked cash flows, such as fees, realized between the given spot date (denoted as 0) and T as described in
Advanced Pricing::Job Request::Cash Flows from Fees

PnL due to Resets

This is the part of the PnL described in
Advanced Pricing::Job Request::PnL due exclusively to the impact of the actual resets between the given spot date (denoted as 0) and the given horizon date T being different than what had been expected at 0

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

Specifically, it equals the hypothetical PnL that would have been produced under the assumption that all floating rate fixings before T equaled the historical fixings in the supplied horizon market, while the fixings after T equaled the forward rates implied by today's curves, rather than the curves in the supplied horizon market.
It is calculated as the sum:
(Carry Surprise) + (Fwd Surp due to Resets)
where the two terms are described at
Advanced Pricing::Job Request::Carry Surprise and Advanced Pricing::Job Request::Fwd Surp due to Resets

Plugging I - C for the first term and Fᵣ - F for the second, we get:
I - C + Fᵣ - F

The remaining part of PnL is given by
Advanced Pricing::Job Request::PnL due to Curves

PnL due to Curves

This is the part of the PnL described in
Advanced Pricing::Job Request::PnL due exclusively to the impact of the actual curves observed at the given horizon date T being different than what had been expected at the given spot date (denoted as 0).

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

Specifically, it equals the hypothetical PnL that would have been produced under the assumption that all floating rate fixings before T equaled the forward rates implied by today's curves, while the fixings after T equaled the forward rates implied by the curves in the supplied horizon market.
It is calculated as the difference:
(PnL) - (PnL due to Resets)
where the two terms are described at

Plugging H + I - S/P for the first term and I - C + Fᵣ - F for the second, we get:
H - S/P + C - Fᵣ + F

The remaining part of PnL is given by
Advanced Pricing::Job Request::PnL due to Resets

Carry Surprise

This is the part of the PnL described in
Advanced Pricing::Job Request::PnL due exclusively to the impact of the realized carry (i.e. income) until the given horizon date T being different than what had been expected at the given spot date (denoted as 0).

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

It is calculated as the difference:
(Income) - (Carry)
where the two terms are described at

Plugging I for the first term and C for the second, we get:
I - C

The remaining part of PnL is given by

Forward Surprise

This is the part of the PnL described in
Advanced Pricing::Job Request::PnL due exclusively to the impact of the realized forward value at the given horizon date T being different than what had been expected at the given spot date (denoted as 0).

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

It is calculated as the difference:
(Horizon Price) - (Forward Price)
where the two terms are described at

Plugging H for the first term and F for the second, we get:
H - F

The remaining part of PnL is given by

Fwd Surp due to Resets

This is the part of the Forward Surprise described in
Advanced Pricing::Job Request::Forward Surprise due exclusively to the impact of the actual resets between the given spot date (denoted as 0) and the given horizon date T being different than what had been expected at 0

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

It is calculated as the difference:
(Forward (Resets)) - (Forward Price)
where the two terms are described at

Plugging Fᵣ for the first term and F for the second, we get:
Fᵣ - F

The remaining part of Forward Surprise is given by
Advanced Pricing::Job Request::Fwd Surp due to Curves

Fwd Surp due to Curves

This is the part of the Forward Surprise described in
Advanced Pricing::Job Request::Forward Surprise due exclusively to the impact of the actual curves observed at the given horizon date T being different than what had been expected at the given spot date (denoted as 0).

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

It is calculated as the difference:
(Horizon Price) - (Forward (Resets))
where the two terms are described at

Plugging H for the first term and Fᵣ for the second, we get:
H - Fᵣ

The remaining part of Forward Surprise is given by
Advanced Pricing::Job Request::Fwd Surp due to Resets

Consistency Check

This is a boolean that is TRUE if and only if the
Advanced Pricing::Job Request::Fwd Surp due to Curves equals the Advanced Pricing::Job Request::PnL due to Curves

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

In fact, these two values must be equal and the boolean here must be TRUE
The reason is the following:
As shown in the table
PnLExplain Table, the following hold:
Fwd Surp due to Curves = H - Fᵣ
PnL due to Curves = H - S/P + C - Fᵣ + F
Therefore, these two values being equal is equivalent to:
S/P = C + F
which must hold as long as the spot price, carry and forward have been calculated correctly because:
S is the present value of all cash flows
C*P is the present value of those cash flows occurring at or before horizon
F*P is the present value of those cash flows occurring strictly after horizon

Horizon Price
Τhe price H of the referenced tradable at the given horizon date T

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.
Precisely, this is the NPV (Net Present Value) of the referenced tradable at T calculated using the market information available at T

Spot Price (Hor)

The
Advanced Pricing::Job Request::Spot Price after it has been compounded forward to the given horizon date T by being divided with the Advanced Pricing::Job Request::Funding DF

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

It equals S / P
where S is the spot price and
P is the discount factor from the given spot date (denoted as 0) to T mentioned above.

Spot Price

Τhe spot price S, which equals the NPV (Net Present Value) of the referenced tradable at the given spot date (denoted as 0).

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

By default, the spot date equals the globally set trade date ( i.e. valuation date) that usually equals today's date.
But if the
Key Tradable::Price::As Of#1 entry to the function Function Tradable::Price exists, the latter date is then regarded as the spot date.
It is represented by the time 0 in the documentation context of

The NPV is calculated using the market information available at 0

If the threshold date Tᵣ for assumed known fixings is specified in
Key Advanced Pricing::Fxd Resets Before, all floating rate fixings before Tᵣ are not implied by the provided curves but rather read from a supplied object of type Historical Values

Income

The income I received by holding the referenced tradable between the given spot date (denoted as 0) and the given horizon date T specified in

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

By default, the spot date equals the globally set trade date ( i.e. valuation date) that usually equals today's date.
But if the
Key Tradable::Price::As Of#1 entry to the function Function Tradable::Price exists, the latter date is then regarded as the spot date.
It is represented by the time 0 in the documentation context of

Precisely, I represents an amount regarded as received at T and defined as the sum of the present values as of T of the tradable's cash flows CFᵢ occurring between 0 (excluded) and T (included).
Formally:
I = Σ(CFᵢ(Pᵢ/P))
where:
CFᵢ are all realized cash flows occurring at times Tᵢ such as 0 < Tᵢ <= T, as implied by the market information available at T
Pᵢ are the respective funding discount factors, i.e. the discount factors seen at 0 with maturities Tᵢ, as implied by the curve associated with the issuer supplied in
P is the funding discount factor seen at 0 with maturity T, as implied by the curve associated with the issuer supplied in

This definition is very similar to that of
The only difference is that the cash flows CFᵢ in the case here are NOT implied by the information available at 0, but represent the ACTUAL (i.e. realized) cash flows in the referred time interval.
It turns out, the calculation of I requires the prior knowledge of all fixings that affect the cash flows occurring at or before T
These must be provided to the function
Function Tradable::Price as an input object of type Historical Values entered in association with the key Key Tradable::Price::Horizon Market

Cash Flows from Fees

The income I received exclusively due to exogenously defined transaction fees that stem from trading the referenced tradable between the given spot date (denoted as 0) and the given horizon date T specified in

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

By default, the spot date equals the globally set trade date ( i.e. valuation date) that usually equals today's date.
But if the
Key Tradable::Price::As Of#1 entry to the function Function Tradable::Price exists, the latter date is then regarded as the spot date.
It is represented by the time 0 in the documentation context of

Precisely, I represents an amount regarded as received at T and defined as the sum of the present values as of T of the transaction cash flows CFᵢ occurring between 0 (excluded) and T (included).
Formally:
I = Σ(CFᵢ(Pᵢ/P))
where:
CFᵢ are all realized transaction fees occurring at times Tᵢ such as 0 < Tᵢ <= T
Pᵢ are the respective funding discount factors, i.e. the discount factors seen at 0 with maturities Tᵢ, as implied by the curve associated with the issuer supplied in
P is the funding discount factor seen at 0 with maturity T, as implied by the curve associated with the issuer supplied in

This definition is very similar to that of
The only difference is that the cash flows CFᵢ in the case here are NOT implied by the tradable's contractual specifications, but must be exogenously supplied through an object of type
Ledger Entry that is carried by the tradble in association with its key Key Tradable::Ledger Entry

Forward Price

The forward price F of the referenced tradable observed at the given spot date (denoted as 0) wrt a maturity equal to the given horizon date T specified in

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

By default, the spot date equals the globally set trade date ( i.e. valuation date) that usually equals today's date.
But if the
Key Tradable::Price::As Of#1 entry to the function Function Tradable::Price exists, the latter date is then regarded as the spot date.
It is represented by the time 0 in the documentation context of

Precisely, F represents an amount payable at T and defined as the amount one agrees at 0 to pay at the maturity date T in order to acquire the tradable at T
It turns out, it equals the sum of the present values as of T of the tradable's cash flows CFᵢ occurring after T
Formally:
F = (1/P)Σ(CFᵢPᵢ)
where:
CFᵢ are all forecasted cash flows occurring at times Tᵢ such as Tᵢ > T, as implied by the market information available at 0
Pᵢ are the respective discount factors, i.e. the discount factors seen at 0 with maturities Tᵢ
P is the funding discount factor seen at 0 with maturity T, as implied by the curve associated with the issuer supplied in

In words, all cash flows CFᵢ are discounted down to 0 and then their sum Σ(CFᵢPᵢ) is compounded forward to T using the discount factor P

One may also define an equivalent forward amount that is assumed to be payable at 0 and be equal to the product F*P = Σ(CFᵢPᵢ)

Apart from an exception mentioned below, all floating rates are assumed equal to their forecast forward values observed at 0

The cash flows occurring on the horizon date are included only if

If the threshold date Tᵣ for assumed known fixings is specified in
Key Advanced Pricing::Fxd Resets Before, all floating rate fixings before Tᵣ are not implied by the provided curves but rather read from a supplied object of type Historical Values

Carry

The carry C of the referenced tradable observed at the given spot date (denoted as 0) wrt a maturity equal to the given horizon date T specified in

Web blog example
here

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

By default, the spot date equals the globally set trade date ( i.e. valuation date) that usually equals today's date.
But if the
Key Tradable::Price::As Of#1 entry to the function Function Tradable::Price exists, the latter date is then regarded as the spot date.
It is represented by the time 0 in the documentation context of

Precisely, C represents an amount regarded as received at T and defined as the sum of the present values as of T of the tradable's cash flows CFᵢ occurring between 0 (excluded) and T (included).
Formally:
C = (1/P)Σ(CFᵢPᵢ)
where:
CFᵢ are all forecasted cash flows occurring at times Tᵢ such as 0 < Tᵢ <= T, as implied by the market information available at 0
Pᵢ are the respective discount factors, i.e. the discount factors seen at 0 with maturities Tᵢ
P is the funding discount factor seen at 0 with maturity T, as implied by the curve associated with the issuer supplied in

In words, all cash flows CFᵢ are discounted down to 0 and then their sum Σ(CFᵢPᵢ) is compounded forward to T using the discount factor P

One may also define an equivalent carry amount that is assumed to be payable at 0 and be equal to the product C*P = Σ(CFᵢPᵢ)

Apart from an exception mentioned below, all floating rates are assumed equal to their forecast forward values observed at 0

The cash flows occurring on the horizon date are included only if

If the threshold date Tᵣ for assumed known fixings is specified in
Key Advanced Pricing::Fxd Resets Before, all floating rate fixings before Tᵣ are not implied by the provided curves but rather read from a supplied object of type Historical Values

Forward (Resets)
The conditional-on-known-resets forward price Fᵣ, which is similar to the forward price F described in
Advanced Pricing::Job Request::Forward Price with the twist that all floating rate fixings before the given horizon date T are not implied by the provided curves but rather read from a supplied object of type Historical Values

It is also part of the PnL Explain table
PnLExplain Table that is produced in association with the key PnL Explain when a tradable is priced with the Advanced Pricing model input.

Funding DF
This is the discount factor P observed at the the given spot date (denoted as 0) wrt the given horizon date T implied by the funding curve associated with the issuer supplied in