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.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
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/As. 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 4th and 5th 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 4th coupon – shown in the 5th 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 Li associated with the ith coupon Ci in order to produce a rate Ri = Li + s so that - if all cash flows are discounted in stages with the rate Ri used as the discounting rate over the respective ith accrual period - the total NPV equals the observed market price P of the bond.
Formally - and denoting by τi the length of the ith 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 discount 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 Di, i = 1, …, n, where each Di is loosely defined as the sensitivity of the bond price P on changes of the interest rate ri, where ri is the zero rate associated with a specific maturity Ti, under the restriction that the zero rates for all other maturities stay fixed.
On the absence of an externally provided discounting yield curve, all zero rates ri 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 Ti, 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 Ti and lead thus to new "perturbed" yield curves YCi, which could be finally employed to discount the bond cash flows for the purpose of calculating the "perturbed" bond prices Pi.
Having calculated the "perturbed" bond prices Pi this way, the key rate durations Di are immediately produced as the ratios (P-Pi)/(δP), which converge to the partial derivatives ∂ln(P)/∂ri of the logarithm of the bond price P with respect to the zero rates ri, assuming P is seen as a function of the n variables ri.
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 ri 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 ri for the given set of maturities Ti so that the curve YCA implied by these rates achieves two things: 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 PA matches the given bond market price P.
This construction can be done in a forward bootstrapping fashion, whereby additional zero rates rj may to be needed as extra degrees of freedom in order to fulfil the task of generating forward rates spanned by those ibor dates Tj, j = 1, …, m that do not match any of the KRD maturities Ti.
This so constructed curve YCA 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 YCAi based on perturbed zero rates rj+δ, which would imply corresponding bond prices PAi and therefore associated key rate durations DAi.
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 Ti and an array of corresponding spreads si.
To the extent that all such input spreads si 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 si 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 YCB would both forecast correctly the forward fixings and imply a theoretical bond price PB that would match the given bond market price P, just like with the curve YCA above.
This doesn't mean of course that YCB is the same curve as YCA!
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 {Ti, i = 1, …, n} and {Tj, j = 1, …, m}.
The important consequence of this fact is that when the zero rate ri in Solution A, or the spread si in Solution B, is perturbed by the amount δ, the previously matched forward rates would generally cease to equal each other.
In other words, for each i the two perturbed curves YCAi and YCBi 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 PAi and PBi would now differ, even if the unperturbed base prices PA and PB equal each other.
This finally means that the two solutions lead to different estimated key rate durations DAi. and DBi, for each maturity Ti.
The preferred curve is clearly YCB 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 YCA.
The remaining steps towards calculating the key rate durations DBi are similar to those in solution A and lead to n new "perturbed" curves YCBi based on perturbed spreads sj+δ with corresponding implied bond prices PBi and therefore associated key rate durations DBi.
Note that now the spreads si - and not the zero rates rj - are the quantities that are perturbed in order to produce the curves YCBi.
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
