# Floating Rate Notes (FRN) in Excel: Understanding Duration, Discount Margin and KRD

Deriscope exports in Excel over 30 functions that deal with *bonds*, as I have described in an earlier document about *bond* risk management functions.

In spite of the fact that the referred functions apply to all types of *bonds*, including *interest rate* and *inflation linked bonds*, one should be cautious when the referenced *bond* does not pay a *fixed rate coupon*.

The reason is that all bond functions that process future coupon amounts, estimate these amounts from the given input and then treat these amounts as fixed!

For example, the **DirtyPrice** function would **a)** project the future coupons by using the given *fixed rate* in the case of *fixed rate bonds* or the given *forecasting curve* in the case of *floating rate bonds* and **b)** discount these coupons to today by using the given *discounting curve*.

In effect, the **DirtyPrice** function – and all other bond functions – do not care about the fixed or floating nature of the coupons. They only care about their projected amount.

It turns out that this is perfectly ok for most of the functions, such as the **AccrualDays**, **AccruedAmount** or **DirtyPrice**.

The big problem arises with the **Yield** function and all other functions that rely on the calculated *yield to maturity*.

The functions below fall in that category:

**BasisPointValue**

**Convexity**

**Duration**

**KRD**

**Yield**

**YieldValueBasisPoint**

Also affected are functions that may be invoked using different parameter input sets and one of them happens to include the *yield to maturity*.

An example is the **DirtyPrice** function in the case when it is called with the key **Yield=** as input.

Another example is the **BPS** (*Basis Point Sensitivity*) function, which works fine when is called with the key **Discount Curve=**, but becomes problematic, when is called with the key **Yield=**.

The problematic nature of the affected functions is best exemplified by the function **Duration** when it is specified to return the *modified duration* of a bond as a measure of the bond sensitivity on interest rates. As a reminder, the *modified duration* is defined as **–(dP/dy)/P**, where **P** is the present value and **y** the *yield* of the bond.

It is well known that the duration of a *floating rate note* is very small and close in value to the time interval – in annual units – from today until the first coupon payment. In fact, the *duration* of a *par floater* linked to an ibor index must equal exactly zero, when the index applicable to the first coupon is not considered yet fixed.

Contrary to this theoretical expectation, applying the **Duration** function on a *floating rate note* returns a big number that is normally associated with *fixed rate bonds*.

The reason - as explained above – is the treatment by QuantLib of the bond's cash flows as if they were fixed and thus independent of the yield **y**.

It is perhaps worth to note that the resulting duration may still be ascribed a meaning by considering it to represent the bond's partial sensitivity on its own credit spread. In that case, one is interested in the relation between the bond price and its yield in an assumed environment where the bond's credit quality is allowed to vary, but the interest rates not.

### Why is Yield to Maturity problematic with Floating Rate Notes?

The *yield to maturity* (or *internal rate of return*) as of a given reference date **T** is defined as the single rate **y**, which when used to discount all future bond cash flows down to time **T**, the resulting present value (NPV) matches the observed market bond price at the same time **T**.

In the case of *fixed rate bonds*, the above definition is clear and always leads to a unique result for the sought-after quantity **y**.

The reason is that – no matter how one understands the discounting process through some sort of discounting rate **r** – the relation between the **NPV** and **r** must be that of a downward sloping curve, as shown below in the case of a **10-year** *zero coupon bond*:

It is quite clear that no matter what the observed market price of the bond is, there will always exist a unique solution for the discounting rate **r**, which will be regarded as the yield to maturity **y** corresponding to that market price.

For a visual understanding, assume the observed market price at some particular time **T** equals **90**. Then the corresponding orange horizontal line intersects the blue curve at exactly one point that leads to a discounting rate of **r = 1.05%**, which is, by definition, the corresponding yield to maturity **y**, as shown below:

Consider now the case where the bond's coupons are linked to some benchmark index, such as the US prime rate or an inflation index.

Then one may still assume that the discounting rate represented by the horizontal axis above is fully decoupled from the benchmark index, repeat the above analysis and derive a *yield to maturity*.

But in general, the discounting rate will be correlated with the benchmark index and often in such a fashion that the blue curve may have no common point – or several common points - with the orange line!

For example, it is well known that the present value of a *par riskless floater *equals its notional regardless of the level of interest rates.

It follows that the blue line for such a floater would be a horizontal line which would therefore coincide with the orange line, as shown below:

The conclusion is that in the case of index-linked bonds the yield to maturity may still be defined, but perhaps only in a technical manner where discounting is assumed decoupled from forecasting. A similar technical interpretation was devised above for the *modified duration* of a *floating rate note*.

All is not lost though. Deriscope provides powerful tools that allow the calculation of various risk metrics perfectly suited for *floating rate notes*. Let's now see these tools in practice.

### Creating a Floating Rate Note in Excel

The Deriscope wizard can generate the spreadsheet formula that creates an object of type *Ibor Rate Bond* in Excel as the following video demonstrates:

and this is how the pasted formula looks like, after I have inserted two keys named **Handle=** for easy reference:

The bond is created in cell **A1** through the formula *=ds(A2:B11)* that returns the text ** &ActualFloater.1**.

It is of type *Ibor Rate Bond*, which indicates that the coupons are linked to an index of type *Ibor Rate*.

This is not the only type of floating rate bond supported by Deriscope. I could have also created an object of type *CMS Rate Bond* or *Inflation Bond*, but this one is the simplest type that serves the purpose of this demonstration.

As the above video testifies, I let the wizard create these formulas using the default mandatory arguments shown in the two input ranges **A2:B11** and **A14:B23**.

Due to the fact that the cell **A1** is selected, the wizard displays the contents of the object *&ActualFloater.1**.*

*As usually, the **keys* shown somewhat dimmed are so called *optional* *keys*, which acquire a default value – determined by Deriscope – if they are not explicitly supplied by the user in the input spreadsheet ranges.

*Of particular interest here are the **keys* **Gearings=, Spreads=, Floors=, Caps=, In Arrears=, Payment Delay=**, the meaning of which is mostly obvious. The plural naming in some of them indicates that an array of values may be associated with these *keys* that can provide for a possible step-up or step-down character of the respective properties.

It turns out, it is well possible to create more complex structures that represent inverse floaters, step up, capped, floored or collared notes by editing the values of these *keys*.

In particular, the keys **In Arrears=, Payment Delay=** allow for dislocating the cash flow away from the regular end of each accrual period.

Perhaps the single most important object element for diagnostic purposes is the *key-value* pair **_CashFlows= &Set#6** shown at the very bottom.

Note this last key is prefixed by **_**, which serves as a visual reminder that it is regarded as a read-only part of the object's data and therefore its associated value cannot be edited. The only purpose of this type of keys is to provide the user with information regarding the current state of the containing object.

If I click – for fun, I suppose – on the respective little lens sign, I see the following:

This is a bird's eye view of what this bond is all about.

Note that certain important columns – such as the one containing the forward index fixings – are missing, while others – specifically the **#Amount, #AccrualTime, #Gearing, #Spread**, and **#Notional** - may contain a few **N/A**s. This is due to the respective information being either not retrievable or not applicable in the given context.

For example, no forward index fixings and floating coupon amounts can ever be estimated without the provision of a yield curve. Also, cash flows of a fixed character – like the one shown at the last row above – can ascribe no meaning to properties like **#Gearing** or **#Spread** that are associated only with index-linked coupons.

Later on, you will see that the part of missing information caused by the lack of a forecasting yield curve will appear in this table as a by-product of the pricing routine because all yield curves will be provided then.

Going back to the spreadsheet, you may notice that the wizard chose to paste the index **%GBPLibor|6M** in cell **B7** because my locale is set to the **UK**. This is the *6-month GBP Libor rate* to which the bond coupons are linked.

I can easily see the conventions associated with this index inside the wizard by selecting the cell **B7**:

The last two input keys **Payment Delay=** and **Redemption=** in **rows** **10** and **11** specify that all coupons are paid at the end of each accrual period without delay and the bond's notional is paid at full at maturity.

The accrual schedule is specified through a separate object of type *Schedule* that has been created in cell **A13** and bears the handle name **&ActualSchedule.1**.

In the image below I have selected the cell **A13** so that you may see the contents of that *Schedule* object in the wizard:

You can see that the schedule starts today (**28/11/2019**), extends for **5 years (%5Y)** and consists of **10** **periods**, each having a length of **6 months (%6M)** as shown in the simple diagram below.

The object **&ActualSchedule.1 **contains a few subtleties stemming from the various shown conventions that are not picked up in the simple diagram above, but nonetheless still have a measurable effect on the bond price.

When in doubt, you consult at best the two arrays of cash flow dates appearing at the bottom of the object's contents in the wizard, pointed by the keys **_Unadjusted Dates=** and **_Adjusted Dates=**.

A final remark concerns the date **28/11/2019 **appearing in green color ( visual sign that the cell contains a simple spreadsheet link) in cell **B17**.

In fact, the wizard had originally generated the date **28/11/2019** and pasted it as a constant value with a blue color ( visual sign that the cell contains a constant value) in cell **B17**.

But I have decided to replace the original constant date value with a link to the cell **B25** further below, where today's date is generated dynamically with the simple Deriscope formula shown below:

The advantage of this method is that my spreadsheet is going to function properly in the distant future because the date regarded as "today" is not hard-coded but rather dynamically generated by the function **Get Default Trade Date** in cell **B25**.

###
Calculating the Price of the Floating Rate Note

It is well known that the price of a *par floater* should equal its notional, regardless of the assumed evolution of the future interest rates.

It is therefore an important sanity test for Deriscope to check if this result is indeed obtained.

The pricing formula is very simple and easily generated by the wizard, as this video shows:

The result is shown below:

The bond price of **100.0004881** is calculated and returned by the formula =ds(D2:E4) in cell **D1**.

The wizard has come up with this result after it produced and used a simple yield curve labeled **&YieldCrv.1** in cell **D6**, built with a flat zero rate of **4%** in cell **E11**.

While this result is close to the theoretically expected **100**, is not exactly equal to it!

A first suspect for this slight discrepancy is the usage of different daycount conventions.

Indeed, the earlier image with the index contents indicates an index daycount convention of **ACT/365**, whereas that associated with the yield curve is **ACT/ACT**.

In order to have the same daycount convention used everywhere, I add the extra key/value pair **TS Daycount=** **%ACT/365F** in the input data of the *Yield Curve* creation and I get the following:

Unfortunately, this correction has a very small effect on the calculated bond price.

The exact impact of the **TS Daycount** is described at the bottom of the wizard as soon as I select the cell that contains that key:

The highlighted bottom text indicates that the **TS Daycount** must have a big effect on the discount factors implied by the produced yield curve **&YieldCrv.2**, since the latter is built out of a flat rate.

Nevertheless, the very nature of the floating rate payments makes the calculated present value quite insensitive to the shape of the discount factor curve. This is theoretically expected and the result above fits this expectation.

Before we search for the next suspect, you might have noticed that the handle for the produced yield curve object appears to have changed from **&YieldCrv.1** to **&YieldCrv.2**.

In reality, in a technical sense the handle is correctly defined as the text part appearing to the left of the dot symbol. In that technical sense, the handle is just **&YieldCrv** and therefore remains the same, before and after the introduction of the **TS Daycount** input. The only thing that has changed is the numerical suffix, which has increased from **1** to **2**. This happened because the memory object pointed by the handle has been modified. The suffix increase acts as a visual aid for this fact and also helps Excel in its task of calculating the various dependencies.

Returning now to the deviation from the par price, a next natural suspicion is that our bond is not a perfect standard *floater*, in the sense that its cash flows do not precisely follow the pattern expected by a *par* *floater*.

Deriscope makes it very easy to investigate the validity of this suspicion by allowing me to browse through the cash flows.

I can go back to the earlier screen with the bond cash flows, but since I have already calculated the price of the bond, I can simply select the cell **D1**, whereby the wizard reacts by showing me the following:

At this stage, the easiest method is probably to click on the word "**here**" of the sentence "*To display extra pricing data click here*", which would lead to the following *extra pricing data*:

The numbers appear to be exact **100**, but this is only due to the limited precision of displayed decimals. Hovering the mouse over any number, I see the following:

Finally, I click on the **CashFlows** element and I get the following:

This table reveals not only the detailed structure of the cash flows during the life of the bond, but also information about all quantities that affect these cash flows.

It also includes the earlier mentioned data that depend on the yield curve and were not available when I browsed the contents of the bond object.

Since I investigate the par nature of the bond, I jump straight to the **4 ^{th}** and

**5**

^{th}**columns**, labeled

**#Fixing**and

**#FixingEnd**respectively, which describe the exact time interval over which each Libor rate is defined.

In a standard *floater*, this time interval must be identical with the respective *coupon accrual period*.

But the above table indicates that at least for a few coupons this is not the case.

For example, the **4 ^{th}**

**coupon**– shown in the

**5**

^{th}**row**above – has its accrual interval ending at

**29/11/2021**, while the respective Libor rate spans a

**6-month**period ending at the different date of

**30/11/2021**.

By modifying the bond slightly so that the index dates match those in the accrual schedule, I can prove that the date mismatches are indeed behind the failure to reach an exact par price for the given bond.

### Creating a Par Floating Rate Note

A simple way to achieve perfect date matching is by adopting a calendar where weekends and holidays are ignored.

To do that, I must replace the **6-month GBP Libor **index with a **custom** **6-month** index that follows the **no-weekend** **calendar**.

Here is how I use the wizard to create such a custom index:

and this is how the pasted formula looks like, after I have inserted the key/value pair **Handle=** **Libor6M** for easier reference:

It might not be apparent at first, but the only difference between the previous index **%GBPLibor|6M** and this new custom index **&Libor6M.1** is their **Settlement**.

The former has **Settlement= %0B{GB_LSX|F}**, while the latter has **Settlement**= **%0D**.

The last **Settlement** value of **%0D** implies the trivial no-weekend calendar and allows the index **&Libor6M.1** to span time intervals that start and end on weekends.

Now that the required custom index has been created, the creation of the standard floater is easily achieved without the wizard by copying and pasting the two formulas in **columns A** and **B** and substituting the various calendars with the **%No** calendar.

The result is shown below, with the new object named **&ParFloater.1** created in cell **G1**:

### Calculating the Price of the Par Floating Rate Note

In order to calculate the price of the **&ParFloater.1**, I only need to copy and paste the existing pricing formula from cell **D1** and edit its input so that it references the new bond object **&ParFloater.1**.

The result below in cell **J1** shows a resounding perfectly exact result of **100**.

Below are the respective cash flows displayed.

Now – as expected – the index period dates perfectly match the coupon accrual dates.

### Calculating the Duration

From now on I will only use the *par floater* **&ParFloater.1** because of the regularity of its cash flows.

The video below shows how I use the wizard to generate the formula that calculates the *modified duration* of that bond:

The result is the value **4.581779608** in cell **J6** shown below:

As explained earlier, this result actually equals the modified duration of a *fixed rate bond* that is specially designed to pay the exact same coupons as those forecasted for the current floater through the yield curve **&YieldCrv.2** supplied in cell **K10**.

It is indeed close to what one would expect for the weighted average time – in annual units - of the cash flow payments occurring through the life of a **5-year** *fixed rate bond*.

__The question is:__

**How can I compute a notion of modified duration that takes into account the impact of the yield (interest rate) on the bond price?**

__Answer:__

**By using the usual Deriscope mechanism that outputs the risk (sensitivity) of the price of any given tradable against changes of the level of a chosen market input.**

In the current case, I am interested in the sensitivity of the bond price against changes of the flat zero rate of **4%** of the yield curve that is used for both forecasting and discounting purposes.

The following video shows how I use the wizard to produce this type of risk:

And here is the output on my spreadsheet:

The wizard pasted the formula *=ds(J15:K22)* in cell **J14** that produces the object **&BondRisk.1** of type *Valuation*.

Due to the key/value pair **Add Risk= TRUE** in cell **K20**, the output object contains – apart from the bond price – also the risk with respect to the market reference **&YieldCrv.2** specified in cell **K21**.

The latter risk is the ratio **dP/dy**, where **dP** is the change in the calculated bond price caused by a shift of **dy** in the flat zero rate of the reference curve **&YieldCrv.2**.

It is reported as the mentioned ratio because I have specified **Delta Def=** **Ratio** in cell **K28**.

In general, I can specify more complex types of sensitivities that relate to a non-flat shift of the underlying interest rates, but the current choice of a flat shift **dy** is compatible with the simple flat yield curve being used.

The exact value of **dy** and other relevant technical assumptions are part of the object **&RiskModel.1** created in cell **J24**.

The contents of that object are displayed in the wizard as soon as I select the cell **J24** and shown below. Most of them are not shown on the spreadsheet because they are optional, a fact that allows them to be absent from the spreadsheet and still assume a certain default value determined by Deriscope.

At any case, the risk ratio **dP/dy** lives inside the output object **&BondRisk.1** in cell **J14** and is brought to the spreadsheet through the usual Deriscope formula =ds("ObjectTools::Show","Reference=",J14,"Key=","Risk","Yield Curve Risk") in cell **J13**, created as demonstrated at the end of the above video.

In "English translation", this formula reads: "*Apply the static function Show of the type ObjectTools on the object referenced by the handle name in cell J14 using as a navigation path of keys defined by the sequence Risk -> Yield Curve Risk*."

The key sequence **Risk*** -> ***Yield Curve Risk** makes sense because the object **&BondRisk.1** in cell **J14** contains a key named **Risk**, which is associated with a sub-object containing a key named **Yield Curve Risk**, of which the associated value I want to extract.

This associated value is thus extracted and displayed in cell **J13** as **-1.42109 10 ^{-10}**.

It results to the final duration-like value of **1.42109 10 ^{-12}** shown in cell

**J12**, after being divided by

**-P**, i.e. minus the spot bond price of

**100**shown in cell

**J1**.

This is a value indeed very close to **0**, as is theoretically expected for the *modified duration* of a *par floater*.

### Calculating the Duration when the First Coupon is Known

The *modified duration* is so small because the index fixing of the first coupon is not independent of the current level of interest rates contained in the market element **&YieldCrv.2** entered as input in cell **K21**.

But if I were to change the valuation date to tomorrow, while keeping everything else as it is, then that index fixing would occur before the valuation date. It would be part of the historical data and would be independent of the current level of interest rates.

I would expect then that the modified duration would equal the annualized time until the payment of the first coupon, since the collection of all future payments could be regarded as a portfolio consisting of **a)** the single first fixed coupon and **b)** a *forward starting par floater* composed by all remaining payments, with a vanishing duration.

It is easy to check the veracity of this claim by running once more the **Price** function, but this time with an additional key called **As Of=**, which fixes the valuation date as of tomorrow (**29 Nov 2019**).

I will also need to supply one more object as input, next to the **Markets=** key, which will hold the historical – from the point of view of tomorrow - index fixing as of **28 Nov 2019**.

I have pre-calculated that fixing as **0.040401569** in cell **J30** as the zero rate with respect to today's yield curve and maturity **6 months** from today, i.e. the **28/05/2020** shown in cell **K33**.

Below is this new formula in cell **M14**, set to the right of the existing one for easy comparison:

I have used **BondRisk t+1** for the handle name in cell **N17** as a reminder that the output object in cell **M14** contains the **t+1** risk, i.e. the risk as evaluated on the next calendar day.

Using the exact same formulas as before, the modified duration is reported in cell **M12** as **0.495878116**, which is indeed equal to the time (**6 months less one day**) in annual units until the first cash flow.

It could be easy for Deriscope to introduce a flag to the existing **Duration** function that would instruct the routine to apply this approach when the underlying bond is of floating type. This would make the duration calculation a snap and also allow the uniform usage of the **Duration** function on all types of bonds, fixed and floating. Please let me know if you are a Deriscope user and would like to see such a functionality.

### Calculating the Discount Margin by Solving for the Fair Spread (The Long Way!)

I have already explained why the *yield to maturity*, while very important for *fixed rate bonds*, carries little informational value in the case of *floating rate bonds*.

A better measure of yield for FRNs is the *discount margin*, which is defined as the spread **s** that must be added to each forward Libor rate **L _{i}** associated with the

**i**coupon

^{th}**C**in order to produce a rate

_{i}**R**=

_{i}**L**so that - if all cash flows are discounted in stages with the rate

_{i}+ s**R**used as the discounting rate over the respective

_{i}**i**accrual period - the total NPV equals the observed market price

^{th}**P**of the bond.

Formally - and denoting by **τ**_{i}** **the length of the **i ^{th}** accrual period - the

*discount margin*is defined as the unique solution for the unknown quantity

**s**to the following equation:

where **n** is the number of coupons and **N** is the bond's notional.

While Deriscope does not provide a direct solution to this equation, it supplies perhaps something more interesting:

Rather than a single number **s**, Deriscope can produce a complete yield curve object, which may then be used to discount any future cash flows using approximately the same discount factors as those embedded in the above discount margin definition formula.

More specifically, the Deriscope function **Add Fwd Spread** can act on any given object of type *Yield Curve* and produce a new curve, of which the instantaneous forward rates are higher than the respective rates of the original curve by a given fixed spread.

The image below shows this construction:

By default, the wizard has chosen a spread of **0.001** or **10 bps** in cell **R5**.

In order to better understand the role of this type of curve construction, I consider a more realistic **non-flat** demo curve that I have produced using arbitrary *deposit rates* so that its zero rate graph presents a pronounced double inverted hump.

Then I apply the **Add Fwd Spread** function on this demo curve to produce its implied spreaded curve.

Finally, I calculate the *zero rates* of both curves for a big set of sample maturities extending from to today to **5 years**.

The resulting chart is shown below:

I may now use this new curve in the role of a *discounting curve* to price the bond. Since the forward rates of this new curve **&SpreadedCrv.1** are higher than the respective rates of **&YieldCrv.2** by **10 bps**, I would expect that the resulting **NPV** will be less than **100**.

So, my plan is to set up a formula that calculates the price of the bond **&ParFloater.1** by using simultaneously two different yield curves, the **&YieldCrv.2** for forecasting and the **&SpreadedCrv.1** for discounting.

If I achieve this, I could subsequently use a solver – even the built-in Excel solver – to find out the value of the spread **s** – which is an input in the pricing formula – that makes the output of the formula equal to some pre-given fixed bond market price. The so found solution **s** could be then regarded as being very close to the sought d*iscount margin*.

The simultaneous involvement of two separate yield curves poses the following conceptual dilemma:

How can the pricing routine know which curve to use for what purpose?

The answer is that it is impossible to know, except if I provide the coupling **yield** **curve <-> usage** as an additional supplied input.

This additional input is effectively an assumption, which means it has to be part of some object, the type of which is part of the *Model* category. Reminder: Deriscope's objects fall primarily in three categories: *Tradable*, *Quotable* and *Model*.

The pricing of any *Tradable* object of specific type *X* always involves a *Model* object of specific type *Model[X]*, even if this is not explicitly shown on the spreadsheet.

For example, our pricing so far of the bond **&ParFloater.1** – which is of type *Ibor Rate Bond* - has actually involved an object of type *Model[Ibor Rate Bond]* created behind the scenes by Deriscope.

But now, I will have to explicitly construct this object so that it contains my chosen **yield** **curve <-> usage** coupling and supply it as input to the pricing formula.

Below you see this – partially completed - construction:

As you see, I have attempted to build an object of type *Model[Ibor Rate Bond]*, which strives to include a concrete prescription of which curves must be used for forecasting and discounting purposes.

The prescription is not direct. This object does not prescribe directly which curve must be used for each role. It does that only indirectly, by specifying the *Issuer* object that a candidate curve must contain in order to be authorized to carry out the indicated job.

Concretely, this object tells the pricing routine that only a curve linked to the specified **Forecasting Curve Issuer** is authorized to be used for forecasting the future index fixings and only a curve linked to the specified **Discounting Curve Issuer** is authorized to be used for discounting all cash flows.

The question is what should I replace the **?** with.

It's important to know that when a *Yield Curve* object is created without an explicit *Issuer* input, it acquires by default the specific **%Riskless Issuer **object, which is a * trivial *object of type

*Issuer . In Deriscope terminology, an object is*

*trivial*if all its contents are implied by its handle name and therefore can be created on-the-fly when its handle name is encountered.You may verify this fact by inspecting the contents of the two *Yield Curve* objects, **&YieldCrv.2** and **&SpreadedCrv.1**, created so far.

Both have the same default issuer **%Riskless Issuer**.

Since both curves share the same issuer, no specification here can lead to an unambiguous curve selection for each job.

It is therefore imperative that I change the issuer of one of the curves before I am able to assign them different roles by specifying their respective issuers in my *Model* object above.

In the image below, you see that I add the extra key/value pair **New Issuer= %Disc|UK|I** in my original formula that created the *Yield Curve* object **&SpreadedCrv.1**. The handle name **%Disc|UK|I** represents a * trivial *object of type

*Issuer*and has been created by the wizard after I chose the custom issuer name "

**Disc**" as a reminder that I have decided to link this issuer with a discounting curve.

Now that my two curves are distinguished from each other with regard to their issuers, I can replace the **?** signs with the appropriate *Issuer* objects, as shown below:

Putting everything together, the pricing formula involving the two curves is shown below:

You see the effect of the "stronger" discounting on the calculated bond price in cell **Q7**, which now drops below **100** to the new value of **99.54292907**.

Note the inclusion of the new key **Models=** and its associated object **&BondModel.1** in cell **R12** as input to the **Price** function, as explained in detail above.

Also, both curves - the **&SpreadedCrv.1** and **&YieldCrvRisky.1** – are supplied as input next to the **Markets=** key in cells **R13** and **S13**.

As I explained above, my ultimate task is to solve for the spread value entered in cell **R5** that leads to a theoretical bond price that matches the observed market price. Then the found spread value should equal – at least approximately – what we call *discount rate*.

To that end I could use the built-in Excel solver, but this would hardly be called "professional", for reasons that must be obvious.

Thankfully, Deriscope provides several options for computing "fair values", i.e. values that a designated independent variable must possess in order for some other dependent variable to reach a given target, at least within a specified accuracy.

In the current context, the easiest approach is to make use of the good, old **Price** function together with the additional input of the optional key named **Fair Value=**.

As I have always done until now, when faced with uncharted territories, the advisable course of action is to "play" with the wizard until some decent working formula gets pasted in the spreadsheet, which formula is deemed to be close enough to the task at hand and therefore require only minimal manual adjustments to have it being brought to its final state.

So, I start by selecting the *Bond* object **&ParFloater.1**, relative to the price of which, I want to calculate the fair value of the involved spread **s**.

Then I select the **Price** function inside the wizard's *Function Selector* and expand the wizard's *Browser Area* so that all input parameters – both mandatory and optional – are displayed. This is what I see:

Notice that there are several optional input parameters that may be employed to calculate much more than just the price of the selected tradable.

My attention is caught by the unchecked box to the right of the key **Fair Value=**.

When I check this box, I see the following:

What happened is that the **Edit Input=** key of the previous screen disappeared and five new keys appeared below the **Fair Value=** key.

The job of these five keys is to define both the independent and dependent variable involved in the fair value computation.

The wizard, lacking psychic skills to read my mind, it fills the corresponding values with certain default choices that – in all likelihood – are not what I want!

While I can easily edit directly in the wizard numerical and text values, it is a bit less straightforward to do the same with values, such as the shown **&ParFloater.1**, that reference sub-objects.

I mention this because I am interested in the fair value of the spread **s**, which does not belong to the proposed object **&ParFloater.1**., but to the object **&SpreadedCrv.2**, which is nowhere to be seen here!

I have two options:

One is to paste the shown key-value collection to the spreadsheet with the **Go** button and then edit the spreadsheet cells accordingly.

The other is to … do it! Replace the shown value **&ParFloater.1** with the handle name **&SpreadedCrv.2**.

I choose the second option, for … fun!

Since I am lazy to type **&SpreadedCrv.2 **in the wizard cell, I would rather copy the text from the spreadsheet cell **Q1**.

But, if I select the cell **Q1**, the wizard will immediately react by displaying the contents of the **&SpreadedCrv.2 **object!

The solution is to temporarily freeze the wizard by selecting the **Lock Browser **menu item of the **Tools** button, as shown below:

Then the contents inside the wizard are shown in green color and stay fixed no matter what I do with the spreadsheet.

So can select the spreadsheet cell **Q1** in peace - without worrying about the wizard's mood - and copy it in my clipboard.

Then I return to the wizard, but before I paste the clipboard content in my target cell, I click on the lock button in order to unfreeze the wizard, as shown below:

Now, I can finally double-click on the cell showing the text **&ParFloater.1** and paste my clipboard text inside.

The wizard reacts and presents the following screen:

It is fascinating that the wizard not only accepted the new handle name **&SpreadedCrv.2**, but also figured out it represents an object of type *Yield Curve* that happens to contain a key named **FLATRATE**, which then decides to display directly underneath as a default candidate value for the **Ref Key=**.

Unfortunately, I am not interested in the fair value of the flat rate of the object **&SpreadedCrv.2**, but rather the fair value of the spread of the sub-object associated with the key **Market Data=** of the object **&SpreadedCrv.2**.

Putting it differently, I am happy with the current selection **Ref Object=** **&SpreadedCrv.2**, but not quite so happy with the selection **Ref Key=** **FLATRATE**.

What I want is that the value of the key **Ref Key= **is an array consisting of the two elements **Market Data, Spread**.

This array is desired because it specifies exactly the chain of keys that lead to my final spread variable in the following sense:

The first key **Market Data** leads to the associated sub-object that is part of the original object **&SpreadedCrv.2**.

Then the second key **Spread** tells where exactly inside the previous sub-object the final variable resides.

Since the current wizard technology does not support the insertion of two elements to the right of **Ref Key=**, I paste the existing setup in the spreadsheet after I have hidden all optional parameters so that the pasted formula does not take too much space.

I also use the second menu item of the **Go** button to avoid the generation of formulas related to the *Market* object **$GBPCrv#2**, since I have the required *Market* object already in the spreadsheet.

Here is the result:

Now, I carry out the manual adjustments, whereby I set a target value of **99** for the market bond price and end up with the unexpected error result shown below:

The wizard-displayed diagnostic message indicates a failure of the root finding method employed by the Deriscope – actually QuantLib – solver to reach an acceptable solution.

The details of this message can guide me to the solution.

It tells me that the failure occurred right at the start of the root finding algorithm, when the two extreme values **10 ^{-5}** and

**0.002**were applied on the spread variable

**s**.

From my previous pricing result of **99.54292907** when a spread s of **0.001** was assumed, I realize that in order to reach a bond price that equals the given target of **99**, it is quite likely that a spread higher than the reported extreme value of **0.002** is required.

If this is true, then the root – i.e. the fair value of the spread **s** – would be greater than **0.002** and thus indeed not bracketed by the values **10 ^{-5}** and

**0.002**.

Therefore, the solution would be to instruct Deriscope to use a different upper value that would be somewhat higher than **0.002**.

This can indeed be done by adding to the **Price** function above an extra optional key-value input, that supplies the required fine tuning through an object of type *Solver*, as shown below:

As you see, it only took an increase of the upper bound from the default **0.002** to the slightly higher value of **0.003** in cell **V21** to enable the solver to find the solution of **0.002194168** in cell **U1**.

This is then the discount margin for my floating rate bond, a value of about **22 bps**.

This number makes sense, since if we multiply it with **5** – from the bond's tenor of **5 years** – we end up with about **100 bps**, which corresponds to the **1%** difference in the bond price from **100** to **99**.

### Calculating the Discount Margin by using the Deriscope Shortcut (The Short Way!)

While the above derivation of the discount margin has a definitive instructional value, it is not ideal for a situation when one needs to do this calculation for several different bonds or different contexts.

Deriscope provides a useful shortcut in the form of a local function named – guess what! – **Discount Margin**, which acts only on objects of type *Ibor Rate Bond*, runs the above described calculations in its efficient C++ implementation and spits out the fair value of the spread **s**.

It is actually a bit smarter than the generic **Fair Value** output of the **Price** function because it is specialized to dealing with a spread and therefore is capable of choosing better initial conditions for the root finding algorithm.

This means it does not generally need the specification of a *Solver* object input, although it can happily accept it, if one is offered.

Below you see the very simple setup of this function and its output of **0.002194168** in cell **Y1**, which exactly equals the previous result.

The sheer simplicity of this function is that it needs the input of only three parameters:

- The caller
*Bond*object of type*Ibor Rate Bond*, in this case the object**&ParFloater.1** - The target price that represents the NPV of the caller bond observed in the market, here the number
**99**. - The forecasting yield curve, i.e. the curve used to calculate the forward fixings of the ibor index, here the object
**&YldCrv.2**.

### Calculating the Key Rate Duration

The *key rate duration* – also known as *KRD* – of a bond is defined as the array of sensitivities **D _{i}**,

**i = 1, …, n**, where each

**D**is loosely defined as the sensitivity of the bond price

_{i}**P**on changes of the interest rate

**r**, where

_{i}**r**is the zero rate associated with a specific maturity

_{i}**T**, under the restriction that the zero rates for all other maturities stay fixed.

_{i}On the absence of an externally provided discounting yield curve, all zero rates **r _{i}** are set equal to the

*yield to maturity*

**y**, with the latter being inferred from the given market bond price

**P**.

By definition then, the *KRD* becomes meaningful only after the provision by the user of **n** maturities **T _{i}**, which can otherwise be arbitrary.

Its computation is outlined in utmost detail in my previous post about Key Rate Duration in Excel, where the challenge was the construction of a discounting yield curve out of a given market bond price, which could afterwards have its zero rates perturbed by a small amount **δ** on the given maturities **T _{i}** and lead thus to new "perturbed" yield curves

**YC**, which could be finally employed to discount the bond cash flows for the purpose of calculating the "perturbed" bond prices

_{i}**P**.

_{i}Having calculated the "perturbed" bond prices **P _{i}** this way, the

*key rate durations*

**D**are immediately produced as the ratios

_{i}**(P-P**

_{i})/(**δ**

**P)**, which converge to the partial derivatives

**∂ln(P**

**)/∂r**of the logarithm of the bond price

_{i}**P**with respect to the zero rates

**r**, assuming

_{i}**P**is seen as a function of the

**n**variables r

**.**

_{i}The Deriscope **KRD** function is essentially implemented along the above described lines outside of QuantLib and works on all kinds of bonds, including floating rate bonds, by treating the projected cash flows as fixed, even if they are linked to some index and therefore not known today.

This treatment is clearly problematic when the zero rates **r _{i}** are perturbed for the very same reason that the QuantLib implementation of the modified duration – which is based on the same cash flow treatment – fails to produce the expected duration of a floater, as explained in detail earlier.

There are two possible solutions:

** Solution A:** Calculate the KRD array by solving for implied zero rates

**r**for the given set of maturities

_{i}**T**so that the curve

_{i}**YC**implied by these rates achieves two things:

^{A}**a)**Implies forward rates that match the forecasted ibor fixings and

**b)**when used to discount the bond's cash flows, the resulting theoretical bond price

**P**matches the given bond market price

^{A}**P**.

This construction can be done in a forward bootstrapping fashion, whereby additional zero rates **r _{j}**

_{ }may to be needed as extra degrees of freedom in order to fulfil the task of generating forward rates spanned by those ibor dates

**T**,

_{j}**j = 1, …, m**that do not match any of the KRD maturities

**T**.

_{i}This so constructed curve **YC ^{A}** would then reproduce the bond price

**P**when used for both forecasting and discounting.

It follows that one could then produce **n** new "perturbed" curves **YC ^{A}**

_{i}based on perturbed zero rates

**r**

_{j}+**δ**, which would imply corresponding bond prices

**P**and therefore associated

^{A}_{i}*key rate durations*

**D**.

^{A}_{i}** Solution B:** Calculate the KRD array by extending the concept of the implied spreaded curve introduced in the calculation of the

*discount margin*, as follows:

Extend the **Add Fwd Spread** function so that it takes as input, instead of a single spread number, one array of maturities that correspond to the KRD maturities **T _{i}** and an array of corresponding spreads

**s**.

_{i}To the extent that all such input spreads **s _{i}** are equal to the same number

**s**, the produced implied yield curve would be the same as the existing one and would generate zero rates that are shifted upwards in relation to the zero rates of the source curve by the amount

**s**, as shown by the chart above.

If one now sets all **s _{i}** to be equal to the special value

**s**that has been pre-calculated as the discount margin of our bond, then the produced implied yield curve

**YC**would both forecast correctly the forward fixings and imply a theoretical bond price

^{B}**P**that would match the given bond market price

^{B}**P**, just like with the curve

**YC**above.

^{A}This doesn't mean of course that **YC ^{B}** is the same curve as

**YC**!

^{A}While they both reproduce the same forward fixings for the particular ibor rates involved in the coupons of the given bond and do the same with the calculated bond NPV, they – most likely - imply diverging forward rates for any other arbitrary set of dates.

As a matter of fact, they imply discount factors that generally do not match each other, not even on the restricted set of maturities defined as the union of **{T _{i}, i = 1, …, n}** and {

**T**,

_{j}**j = 1, …, m}**.

The important consequence of this fact is that when the zero rate **r _{i}** in Solution A, or the spread

**s**in Solution B, is perturbed by the amount

_{i}**δ**, the previously matched forward rates would generally cease to equal each other.

In other words, for each **i** the two perturbed curves **YC ^{A}**

_{i}and

**YC**

^{B}_{i}will not imply the same forward rates anymore and therefore would lead to divergent floating cash flows and discount factors.

The conclusion follows that the corresponding "perturbed" implied bond prices **P ^{A}_{i}** and

**P**would now differ, even if the unperturbed base prices

^{B}_{i}**P**and

^{A}**P**equal each other.

^{B} This finally means that the two solutions lead to different estimated key rate durations **D ^{A}_{i}**. and

**D**, for each maturity

^{B}_{i}**T**.

_{i}The preferred curve is clearly **YC ^{B}** because its graph of zero rates would be a smooth shifted replication of the original ibor curve. The same would not hold for the curve

**YC**.

^{A}The remaining steps towards calculating the key rate durations **D ^{B}_{i}** are similar to those in solution

**A**and lead to

**n**new "perturbed" curves

**YC**

^{B}_{i}based on perturbed spreads

**s**

_{j}+**δ**with corresponding implied bond prices

**P**and therefore associated

^{B}_{i}*key rate durations*

**D**.

^{B}_{i}Note that now the spreads **s _{i}** - and not the zero rates

**r**

_{j}_{ - }are the quantities that are perturbed in order to produce the curves

**YC**

^{B}_{i}.

Please let me know if you are using Deriscope and would like to see the extension of the **Add Fwd Spread** function that would allow you to replicate on the spreadsheet the strategy described here.

In that case, it would be straightforward for me to also enhance the existing **KRD** function with a flag, so that it would produce a KRD array consistent with this calculation as soon as the bond is of floating type. This would allow you to get the KRD result immediately, without the need to generate the implied spreaded curve and also apply the same **KRD** function in a uniform fashion across all types of bonds regardless of their floating nature.

Click on **FRN.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