 Deriscope

## The Excel Derivatives Periscope

##### Coverage Bond_Functions

Function Start Date within Bond returns the bond's start date.
It equals the first date in the accrual schedule and does not necessarily coincide with the payment date of the first coupon.

Function Maturity Date within
Bond returns the bond's maturity date.
It equals the last date in the accrual schedule and does not necessarily coincide with the payment date of the last coupon.

Function Dirty Price within
Bond returns the bond's dirty price (based on a notional of 100) at a given settlement date T
It is defined as the present value of all cash flows received after T when the bond's notional is assumed to equal 100.
There exist 3 distinct methods for obtaining the dirty price:
1) From a given clean price.
2) From a given flat yield that acts as the discount zero rate for all cash flows.
3) From a given discounting yield curve that implies the discount factors to be used for discounting the various cash flows.

If T is not explicitly given, it will be set to the default bond settlement date.

Function Clean Price within
Bond returns the bond's clean price at a given settlement date T
It is defined as the bond's dirty price at T minus any interest amount (based on a notional of 100) that has been accrued until T during the coupon accrual period containing T
There exist 4 distinct methods for obtaining the clean price:
1) From a given dirty price.
2) From a given flat yield that acts as the discount zero rate for all cash flows.
3) From a given discounting yield curve that implies the discount factors to be used for discounting the various cash flows.
4) From a given discounting (treasury or swap rate) yield curve plus a Z-spread over that curve that combined imply the discount factors to be used for discounting the various cash flows.

If T is not explicitly given, it will be set to the default bond settlement date.

Bond returns true if there exist cash flows to be paid after the given settlement date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Settlement Date within
Bond returns the bond's settlement date associated with the given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Settlement Period within
Bond returns the bond's settlement period expressed as an object of type Single Period

Function Settlement Date Bump within
Bond returns the bond's settlement date bump convention expressed as an object of type DateBump

Function Settlement Day Count within
Bond returns the bond's settlement daycount convention expressed as an object of type DayCount

Function Settlement Calendar within
Bond returns the bond's settlement calendar expressed as an object of type Calendar

Function Settlement Currency within
Bond returns the bond's payment currency expressed as an object of type Currency

Function Settle Days within
Bond returns the number of the bond's settlement business days.

Function Previous Cash Flow Date within
Bond returns the payment date of the cash flow associated with the accrual period preceding the given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Next Cash Flow Date within
Bond returns the payment date of the cash flow associated with the accrual period following the given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Previous Cash Flow Amount within
Bond returns the cash flow amount associated with the accrual period preceding the given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Next Cash Flow Amount within
Bond returns the cash flow amount associated with the accrual period following the given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Previous Coupon Rate within
Bond returns the coupon rate of the accrual period preceding the given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Next Coupon Rate within
Bond returns the coupon rate of the accrual period following the given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Accrual Start Date within
Bond returns the start of the accrual period containing a given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Accrual End Date within
Bond returns the end of the accrual period containing a given reference date T

If T is not explicitly given, it will be set to the default bond settlement date.

Bond returns the start of the reference period containing a given reference date T
Note the reference period can differ from the corresponding accrual period and is needed in the calculation of the time length of the accrual period by a few day count conventions, such as ACT/ACT(ICMA)

If T is not explicitly given, it will be set to the default bond settlement date.

Function Reference Period End within
Bond returns the end of the reference period containing a given reference date T
Note the reference period can differ from the corresponding accrual period and is needed in the calculation of the time length of the accrual period by a few day count conventions, such as ACT/ACT(ICMA)

If T is not explicitly given, it will be set to the default bond settlement date.

Function Accrual Period within
Bond returns the bond's accrual period at a given reference date T
This equals the number of calendar days of the accrual period containing T expressed in annual units according to the bond's day count convention.

If T is not explicitly given, it will be set to the default bond settlement date.

Function Accrual Days within
Bond returns the bond's accrual days at a given reference date T
This equals the number of calendar days of the accrual period containing T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Accrued Period within
Bond returns the bond's accrued period at a given reference date T
This equals the number of calendar days from the start of the accrual period containing T until T expressed in annual units according to the bond's day count convention.

If T is not explicitly given, it will be set to the default bond settlement date.

Function Accrued Days within
Bond returns the bond's accrued days at a given reference date T
This equals the number of calendar days from the start of the accrual period containing T until T

If T is not explicitly given, it will be set to the default bond settlement date.

Function Accrued Amount within
Bond returns the bond's 100-scaled accrued amount at a given reference date T
This equals the interest amount that has been earned up to T during the accrual period containing T assuming a notional of 100.

If T is not explicitly given, it will be set to the default bond settlement date.

Function BPS within
Bond returns the basis-point sensitivity of the bond's cash flows as of a given reference date T
This equals the change in NPV (on a notional of 100) due to a uniform 1-basis-point change in the rate paid by the cash flows, provided all discounting is done according to the provided yield or discounting curve,

Concretely, in the case where a yield is supplied, a discounting curve is constructed with a flat zero rate equal to the supplied yield.
Otherwise, the supplied discount curve is used.
Then all coupons - regardless of being fixed or floating - are replaced with fixed payments of an amount equal to one basis point on a notional of 100, i.e. an amount of 0.01
Then the NPV of all these replaced coupons is calculated using the applicable discounting curve mentioned above.

There exist 2 distinct methods for obtaining the NPV:
1) From a given flat yield that acts as the discount zero rate for all cash flows.
2) From a given discounting yield curve that implies the discount factors to be used for discounting the various cash flows.

If T is not explicitly given, it will be set to the default bond settlement date.

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Function ATM Rate within
Bond returns the At-The-Money rate as of a given reference date T
This is the theoretical fixed flat coupon rate that the bond would need to have in order for its calculated clean price to equal a given clean price input.
A discounting yield curve must be provided.
If a clean price is not provided, it is calculated from the given discounting yield curve.

If T is not explicitly given, it will be set to the default bond settlement date.

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Function Duration within
Bond returns the duration as of a given reference date T
The exact duration definition should be specified out of the list
Bond::Duration Type

If T is not explicitly given, it will be set to the default bond settlement date.

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Function KRD within
Bond returns the Key Rate Duration (KRD) as of a specified array of future times T₁, T₂, ..., Tₙ
KRD is defined and implemented outside of QuantLib as follows:

Let P be the bond's dirty price as of the bond's settlement date T
KRD is an array of n numbers D₁, D₂, ..., Dₙ, such that Dᵢ represents the sensitivity of P upon changing the zero rate rᵢ corresponding to maturity Tᵢ

More specifically:
Dᵢ = (Pᵢ⁻ - Pᵢ⁺)/(2ΔP)
, where
Δ = User-specified amount by which each interest rate described below is shifted both downwards and upwards. For example Δ = 0.01 means an initial rate of 4% is shifted downwards to 3% and upwards to 5%
P = Dirty price that corresponds to the given clean price.
In order to explain Pᵢ⁻ and Pᵢ⁺, let:
y = Bond's continuously compounded yield implied by the given clean price.
YC = Yield curve built out of zero rates r₁, r₂, ..., rₙ, of which the corresponding equivalent continuously compounded rates are all equal to y
Due to the way the bond's continuously compounded yield y is defined, P must then equal the bond's NPV calculated off the yield curve YC
Now Pᵢ⁻ and Pᵢ⁺ are defined as follows:
Pᵢ⁻ = Dirty bond price calculated using a yield curve YCᵢ⁻, which differs from YC in that it is built out of the zero rates r₁, r₂, ..., rᵢ-Δ, ..., rₙ
Pᵢ⁺ = Dirty bond price calculated using a yield curve YCᵢ⁺, which differs from YC in that it is built out of the zero rates r₁, r₂, ..., rᵢ+Δ, ..., rₙ

Note all yield curves are built using linear interpolation and flat extrapolation with respect to the input zero rates.
This has the consequense that the curves YCᵢ⁻ and YCᵢ⁺ will differ from each other only in the time interval (Tᵢ₋₁,Tᵢ₊₁)
This in turn implies that Pᵢ⁻ - Pᵢ⁺ will receive non-zero contributions only from bond cash flows that occur in the interval (Tᵢ₋₁,Tᵢ₊₁)
The conclusion is that the iᵗʰ KRD Dᵢ = (Pᵢ⁻ - Pᵢ⁺)/(2ΔP) is determined only by the cash flows occurring in the interval (Tᵢ₋₁,Tᵢ₊₁)

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Function Convexity within
Bond returns the convexity as of a given reference date T
The convexity is defined as follows:
Let y be an assumed given yield (flat yield to maturity) for our bond.
Let P be the bond's dirty price as of T
Then the second derivative (a measure of curvature) C of P with respect to y is computed.
Note that C will depend on the conventions defining the yield y
Convexity is then defined as C/P

If T is not explicitly given, it will be set to the default bond settlement date.

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Function Basis Point Value within
Bond - also known as DV01 - returns the basis point value as of a given reference date T
It equals the change in NPV dP caused by an increase in yield by one basis point.
Obtained by setting dy = 0.0001 in the following 2nd-order Taylor series expansion.
dP = delta*dy + ½*gamma*(dy)²
The coefficients delta, gamma are computed as follows:
delta = -D*P, where D is the bond's modified duration
gamma = C*P/100, where C is the bond's convexity

If T is not explicitly given, it will be set to the default bond settlement date.

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Function Yield Value Basis Point within
Bond returns the yield value of a basis point as of a given reference date T
The yield value of a one basis point change in price is the derivative of the yield with respect to the price multiplied by 0.01

The exact calculation proceeds as follows:
By definition, for the modified duration D holds:
D = -(1/P)(dP/dy)
where y is an assumed given yield (flat yield to maturity) for our bond
and P is the bond's dirty price (NPV) as of T calculated according to the given yield y

Solving for the derivative dP/dy we get:
dP/dy = -PD
Therefore the inverse derivative is:
dy/dP = -1/(PD)
and this function's output is:
0.01dy/dP = -0.01/(PD)

If T is not explicitly given, it will be set to the default bond settlement date.

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Bond returns the Z-spread as of a given reference date T
Here the Z-spread is defined as the single rate z that when added to the time-dependent "risk-free" rate r, produces the time-dependent rate r+z, which when used to discount all future bond cash flows down to time T, the resulting present value equals the given dirty bond price at the same time T
This function requires the input of a "risk-free" curve - a treasury or swap curve - that is used to imply the time-dependent rate r
It also needs the bond clean price as of time T, so that the respective dirty price can be calculated.

If T is not explicitly given, it will be set to the default bond settlement date.

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Function Yield within
Bond returns the theoretical bond yield to maturity (same as internal rate of return) as of a given reference date T
Here the yield is defined as the single rate r that when used to discount all future bond cash flows down to time T, the resulting present value equals the given dirty bond price at the same time T
There exist 2 distinct methods for obtaining the yield:
1) From a given clean price as of time T, which is used to imply the needed respective dirty price.
2) From a given discounting yield curve that is used to calculate the needed dirty price at time T

If T is not explicitly given, it will be set to the default bond settlement date.

PRECAUTION: This function treats the referenced bond as if it were a fixed rate bond even if this is not the case! All index-linked cash flows are treated as fixed cash flows that pay the amount forecasted by the corresponding curve.

Function Settlement Value within
Bond returns the bond's settlement value as a function of the clean price.
This is based on the actual bond's notional (not scaled to 100).
The theoretical settlement value is returned if no clean price is provided.
The default bond settlement date is used for calculation.