Bond Key Rate Duration (KRD) in Excel: Calculating and Understanding
You are all familiar with the concept of bond Duration, which tells us how much a bond's price changes if its yield goes up or down by a small amount.
While there exist several different Duration definitions that produce slightly different results, the one that is most closely related to the Key Rate Duration is the Modified Duration D, which may be defined in a very general fashion with respect to any stream of future cash flows as the negative of the relative change -ΔB/B of a bond's initial dirty price B caused by a change Δy in its initial yield y, divided by that change Δy. In mathematical terms:
D = (-ΔB/B)/Δy = -(1/B)ΔB/Δy
There is still some residual uncertainty in this definition due to the yet unspecified size of the assumed yield change Δy. Many industry practitioners set Δy = 1%. For example, they would shift an initial yield of y = 5% to y΄ = y+Δy = 5% + 1% = 6% and then they would calculate the theoretical bond price B΄ implied by the shifted yield y΄. Finally they would calculate the difference ΔB = B΄-B and then the Duration D from the formula above.
The actual practice follows a slightly different interpretation of the above formula so that both the upside and downside yield movements are considered in a symmetrical fashion. More specifically, most practitioners take Δy in the above formula to mean the difference y+-y- = 2δ, where y+ = y+δ and y- = y-δ and δ is the absolute one-sided yield shift, typically 1%. Then they form the dirty bond price difference ΔB = B+-B- and plug everything in the formula to get:
D = -(1/2B)ΔB/δ
Free and open source QuantLib calculates the Modified Duration of fixed rate bonds analytically by setting Δy -> 0, which transforms the definition formula to one involving the derivative dB/dy of B with respect to y:
D = -(1/B)dB/dy
Then rather than calculating the theoretical bond prices corresponding to shifted yields, QuantLib regards B as the sum B1+B2+…Bn of the present values of the bond's cash flows B1, B2, …, Bn. Since a derivative acts linearly on a sum, it follows:
dB/dy = dB1/dy+dB2/dy+…+ dBn/dy
Now each derivative can be easily calculated provided we know how the yield y is defined. For example, if y is defined with the continuous compounding convention, then its associated discount factor that applies to the ith cash flow Ci paid at time Ti must equal e-yTi, meaning that Bi = e-yTiCi and therefore:
dBi/dy = d(e-yTiCi)/dy = -CiTi e-yTi
The advantage of this approach is that one discounting operation - rather than two - suffices for calculating the symmetric two-sided derivative that is needed by the definition of the Modified Duration.
Recommended for Deriscope starters: The Overview and Quick Guide pages.
Definition and Rational of Key Rate Duration
Now that we have refreshed our memory with the details of the Modified Duration, we notice that the present values of all cash flows are recomputed based on two highly unrealistic assumptions:
Assumption 1: Applicable for the discounting of each cash flow Ci is the same flat yield y, regardless of the time Ti on which the Ci is paid. Note here that y is a purely theoretical interest rate linked to our particular bond (stream of cash flows), defined so that the cumulative present value of all cash flows equals the observed bond's dirty price. Take the bond away and you discover that y has no real world connection to each maturity Ti in isolation! The only value of y is that it does its job well with respect to its linked bond, namely calculating the bond's present value. Beyond that, y cannot be applied to any other bond, not even to compute the present value of a single isolated cash flow of the same bond.
Assumption 2: The new bond price is calculated under the assumption that the theoretical flat yield y applicable to each cash flow Ci changes by the same amount Δy (discrete or infinitesimal), regardless of the time Ti.
While – as explained – the first assumption is perhaps unrealistic but not bad since it still produces the correct current bond price, the second assumption is practically useful only if we are interested in calculating the sensitivity of the bond price with regard to a change of its own yield. Quite often, we are interested in how a bond price reacts on price changes of certain benchmark highly liquid bonds - such as treasury bonds - of various maturities.
It is a fact that bond price changes are not uniform over the maturity spectrum. Certain market events may lead to a spike of the short term interest rates or credit spreads (both are relevant to the bond's yield) but cause no change on the long term rates and spreads. What we need is not a single measure of how sensitive our bond is on generic yield changes, but rather several different measures that quantify our bond's sensitivity on various maturity regimes. For example, we would perhaps like to have three different sensitivities: a) on changes of short term rates, b) on changes of medium term rates and c) on changes of long term rates.
Key Rate Durations (KRD) have been invented exactly for that purpose.
More specifically a Key Rate Duration Ki is defined with respect to a given maturity Ti and an absolute one-sided rate shift δ as follows:
Ki = (B--B+)/(2Bδ)
Here B- is the bond's present value (dirty price) as calculated by a downwards bumped yield curve YC- described below.
B+ is the bond's present value (dirty price) as calculated by an upwards bumped yield curve YC+ also described below.
The above definition relies on a base yield curve YC that exactly reproduces the currently observed dirty bond price B.
This base yield curve YC is constructed by using as input market rates, N continuously compounded zero rates z1, z2,…,zN all equal to the known current – also continuously compounded – bond yield y. Both the integer number N and the maturities of the input zero rates can be arbitrarily chosen. For example, we may choose the N = 11 maturities along the US Treasury spot rate curve.
Due to the facts a) the yield curve YC has been constructed out of the bond's yield y and b) the bond's yield y by its own definition produces the currently observed dirty bond price B, it follows that pricing the bond using the YC-implied discount factors must lead to a result that is exactly equal to B.
After we have constructed YC, we build the bumped yield curve YC- by simply bumping the initial ith input zero rate zi downwards by the given amount δ, while keeping all other input rates fixed. Then we use the discount factors implied by the curve YC- to calculate the corresponding theoretical dirty bond price B-.
Similarly, the bumped yield curve YC+ is built by bumping the initial ith input zero rate zi upwards by the given amount δ, while keeping all other input rates fixed. Then we use the discount factors implied by the curve YC+ to calculate the corresponding theoretical dirty bond price B+.
This definition/construction of Ki has the following two consequences:
Consequence 1: For the sum K = K1+K2+…+KN must hold:
K = (B΄--B΄+)/(2Bδ)
where B΄- and B΄+ are the bond prices resulting from a downward and respectively upward parallel shift of the base yield curve YC, whereby all input zero rates are shifted simultaneously by the given amount δ.
Consequence 2: When the rate shift δ tends to 0, the sum of the Key Rate Durations K must tend to the bond's Modified Duration D.
Creating a fixed rate bond object in Excel
The Deriscope wizard can generate the spreadsheet formula that creates a Fixed Rate Bond object in Excel as the following video demonstrates:
Below is how the pasted formula looks like, after I have shifted two blocks upwards so that they are all visible. I have also changed on the spreadsheet the original wizard-created Settlement Days default of 0 into 2 in order to have a more realistic bond that settles 2 business days after the trade date of 06 Dec 2018 (today). Out of the same motivation for realism, I have also changed the original wizard-created Start and End Dates so that the bond starts accruing in the past and thus a non-zero accrual amount exists as of today, which makes the spot dirty and clean prices unequal. Finally I have set the DayCount to 30/360 in order to have non-trivial year fraction calculations of all involved time intervals.
Understanding the formula
As you see, cell A1 contains the formula =ds(A2:B15), which takes one input argument and returns the text &FxdBnd_A1:1.3
The prefix & indicates that &FxdBnd_A1:1.3 is the handle name of some object. In fact it points to an object of type Fixed Rate Bond and can be used in any context where an object of type Bond is expected because the type Fixed Rate Bond inherits from Bond.
The input range A2:B15 contains 12 key-value pairs with obvious meanings.
Nevertheless I can read the information applying to each key-value pair off the wizard by simply selecting the respective cell.
Regarding the values – indicated with blue color – I can edit them by manually typing in the cells or by using the wizard as explained in previous articles, for example here.
The accrual schedule of the fixed leg of the bond is represented as an object of type Schedule in cell B9. It is separately generated by the formula =ds(D2:E17) in cell D1, to which B9 links.
Finally the conventions characterizing the fixed rate 4% are supplied by the object of type Simple Rate generated in cell D18.As you see, the bond's coupon accrues in accordance with the simple compounding convention.
Calculating the Bond's Yield and Dirty Price
As I explained above, in order to calculate the KRD I need to find out the current yield. Let us assume the bond's clean price right now is 95. I may use the wizard to generate the two formulas that compute the yield and dirty price out of the given clean price.
The wizard constructs these formulas using a default input for the clean price of 100. After the formulas have been inserted in the spreadsheet, I change these to 95 (actually linking the respective cells to another single cell designed to hold my clean price).
The wizard also builds the yield rate's conventions in such a way so that it is continuously compounded and has the same DayCount as that of the calling Bond object. Since the latter had a DayCount of 30/360, the same is picked up by the wizard, as you see in cell E17 below.
Since no reference date is provided in these two formulas, the displayed results of 5.1441% and 97.222222 are the yield and dirty price respectively as of the bond's settlement date, i.e. 2 business days in the future.
Constructing the Base Yield Curve
My next task is to build a yield curve out of continuously compounded zero rates, all of which are equal to my just calculated yield of 5.1441%. My zero rates will all be spot starting without any settlement delay. Due to them being flat (all equal to each other) and continuously compounded, their implied forward started continuously compounded zero rates will also be flat and equal to 5.1441%. Therefore there is no discrepancy due to the fact that the yield of 5.1441% has been calculated with a start date two business days in the future.
It is easier to start by asking the wizard to generate the spreadsheet formula that creates a yield curve out of zero rates, as shown in the next video:
The wizard pastes too many optional input data in the spreadsheet, so I go ahead and I remove what I don't need for enhanced clarity.
Also I change the Modelled Quantity from Forward Rate to Zero Yield, since I want to have a flat (maturity-independent) zero rate and this can be best achieved when my Modelled Quantity – relative to which all interpolation takes place - is represented by the zero rate.
Also I link both DayCount entries to the bond DayCount specified in cell B29 and finally add 3 more maturities to reach a 5-year horizon spanned by one year intervals.
After I have linked all input zero rates to the cell D3 containing my calculated bond yield, my spreadsheet looks like:
Now I must verify that my yield curve is properly constructed in the sense that it ought to reproduce the dirty bond price of 97.222222 calculated in cell D18.
Deriscope offers two ways to calculate the present value of a bond out of a given yield curve. The usual way is by letting the wizard to generate the spreadsheet formula that calls the Price function, which is available for all objects of type Tradable and therefore for my object of type Bond – which inherits from Tradable – created in cell A1.
A simpler approach is to use the special Dirty Price function that is available only for objects of type Bond. So I first select cell A1 to signal the wizard that I want to make use of the functions applying to the selected Bond object and then I go to the Function Selector of the wizard, where I choose the Dirty Price function. Finally I select the empty cell D23 and hit the Go button to paste the generated formula in that cell.
You may have noticed in this video that the wizard also creates a new yield curve with a flat rate of 4% to use as the single required input to the Dirty Price function. It does not link to the existing yield curve already created in cell G1 because it realizes that the latter curve is different (not flat 4%) from the one generated internally. The wizard avoids unnecessary creation of spreadsheet formulas only if an existing formula is found that produces an object that exactly matches the one under construction. This is the reason why the wizard decides in this case to go ahead with the generation of one more yield-curve-creating formula.
What I do afterwards, is deleting this new generated curve and simply linking the cell E27 to my existing curve in cell G1.
This is how my spreadsheet looks like now. It carries an additional formula that produces the dirty bond price output in cell D23:
Dirty Price Discrepancy Resolution
So we have a problem!
The dirty price off the yield curve comes as 99.219255 in cell D23, which differs from the true bond price of 97.222222 calculated in cell D18!
You may think there is some different settlement time interval involved.
A settlement time issue of a few days would never produce such a huge difference in price. As a matter of fact, both prices are calculated with respect to 2 business days from today because the Dirty Price function always computes the dirty price wrt the bond's settlement date, if no reference date is explicitly provided as input. This holds regardless if the function is used with a Clean Price input – as in cell D18 – or with a Discount Curve input – as in cell D23.
The culprit lies elsewhere.
Even though the yield curve is built out of flat zero rates, is anything but flat! By default, all five zero rates supplied as input in the column range H20:H24 are assumed to follow the simple compounding convention. This means that the corresponding continuously compounded rates become smaller and smaller as the maturity increases. These decreasing rates are then used as pegs for calculating the continuously compounded rate for any other maturity through linear interpolation. The end effect is that the bootstrapped yield curve represents decreasing continuously compounded zero rates and therefore discounts less intensely the bond's future cash flows. The result is a higher implied bond's present value reported in cell D23.
The solution is actually straightforward: Instead of entering the same number 5.1441% in all cells of range H20:H24, calculate and enter the equivalent continuously compounded rate for each maturity.
By definition, during a time interval t, a simply compounded rate r acting on one currency unit accrues an interest amount of rt, whereas a continuously compounded rate c accrues an interest amount of ect-1. The two rates r and c are considered equivalent wrt the given time interval t if they generate the exact same interest amount during that interval. Note that two equivalent rates with respect to a certain time interval t will generally not be equivalent with respect to another time interval t΄.
So for the rates to be equivalent, we must have rt = ect-1 => r = (ect-1)/t.
We know c = 5.1441% for all maturities, but how do we compute the time interval t for each zero rate maturity?
The time interval t refers to the time between the date when the respective deposit contract starts and the date it ends. So we need to find these two dates first.
Below you see the section of my spreadsheet with the two extra columns where the end date and time interval for each input zero rate entry are calculated. As usually, I am using simple Deriscope formulas to get the results:
The new yield curve takes as input increasing simply compounded rates, pre-calculated to correspond to the same flat continuously compounded rate of 5.1441%. It is therefore a flat continuously compounded zero rate curve. Indeed the curve-implied dirty bond price turns out now almost equal to the actual bond price, as the next image betrays:
Construction of the Bumped Yield Curves
My next task is the construction of 10 additional yield curves.
For each zero rate maturity, two new yield curves must be constructed. One corresponding to the downward bumped continuously compounded zero rate and one corresponding to the upward bumped continuously compounded zero rate.
So first I introduce two columns with the titles r- and r+ that contain the bumped down and bumped up simply compounded zero rates respectively, as shown below:
Next I introduce two more columns titled yc- and yc+ that hold the corresponding Yield Curve objects. These are created by a bit complex formulas that rely on a row index supplied at an extra column inserted on their left, as shown below:
I have entered with my mouse inside the formula in cell P20 so that you can easily see the dependent ranges. The production of the bumped Yield Curve object is a simple application of the Deriscope Clone function. As you see above, the Clone function applies to the base Yield Curve object of cell G1 and attempts to modify its value associated with the key Deposit Set= in cell G18. All this is conveyed by the first three input arguments of the ds formula.
The hard part is the last input argument that supplies the new Deposit Set value that the produced cloned object must contain.
Note that the original value is the table defined through the range G19:H24. I want my new value to be the same table except of the element 5.2788% on the second row and second column. I also want my cloned Yield Curve object to reference the downward bumped rate of 4.2312% given in cell M20.
The question really is: Given a table, how can I produce another table that is a replica of the original one except of a single cell?
The answer is given by the special Deriscope spreadsheet formula Replace that has the following syntax:
=Replace( OldTable, FromRow, ToRow, FromColumn, ToColumn, NewSubTable )
The OldTable and NewSubTable arguments are rectangular sets of data, while the remaining arguments are all integers.
In order to replace the rate 5.2788% with 4.2312% the required formula is:
=Replace( $G$19:$H$24; 2; 2; 2; 2; 4.2312% )
Adopting a more general format that can be copied across, the last ds argument finally becomes:
dsReplace($G$19:$H$24;$O20;$O20;2;2;M20)
Construction of the Bumped Dirty Bond Prices and respective KRD Values
Now that I have managed to produce my bumped curves, I can introduce two more columns that contain the corresponding implied dirty bond prices as of the bond's settlement date, as shown below:
The final step is to add the last column with the KRD values calculated according to their definition Ki = (B--B+)/(2Bδ), as shown below:
You may notice the 5Y KRD value of 1.750373 is less than the 4Y KRD value of 2.099922. This is because the 5Y bond has started about 6 months ago and therefore matures about 6 months prior to the 5Y zero rate maturity peg, relative to which the 5Y KRD is calculated. The substantial bond principal payment in 4 ½ years gets affected by only half of the bump in the 5Y zero rate and therefore its contribution becomes only half of what it could have been if the two date pegs had coincided. Also all coupons paid prior to maturity add zero contribution to this 5Y KRD figure.
Convergence of the KRD Sum to the Modified Duration
As mentioned earlier, the sum of the KRD values K = K1+K2+…+KN must equal (B΄--B΄+)/(2δ), where B΄- and B΄+ are the bond prices resulting from a downward and respectively upward parallel shift of the base yield curve, whereby all input zero rates are shifted simultaneously by the given one sided rate shift δ.
Therefore if my computations so far have been correct, the sum K must converge to the Modified Duration as the rate shift δ approaches 0.
The sum turns out to be 4.067035.
The Modified Duration is calculated with the respective Deriscope function to the value of 4.066705 as shown below:
Still under a one sided rate shift of 1% (as shown in cell N18) the two results are very close to each other. It turns out the convergence is – almost suspiciously – perfect and very fast. For example, we can achieve agreement up to the first 10 decimal digits by setting the Rate Shift to as low as 0.0001%, as shown below:
Efficiency: Getting the Same Results in One Step with the Deriscope KRD Function
Imagine you want to calculate the KRD values for hundreds of bonds or you want to vary the number of benchmark KRD maturities. The spreadsheet solution presented earlier is clearly not practical.
Deriscope provides a function called KRD that carries out all these steps much more efficiently through its C++ implementation and delivers the final results.
Simply select the cell A1 that contains the Bond object to alert the wizard about your desire to work with an object of type Bond. Then go over to the Function Selector of the wizard area, select the KRD function and hit the Go button to paste the generated formula in the spreadsheet. The result is shown below:
As you see, the wizard has assumed a Clean Price of 100, a Rate Shift of 1% and 7 maturity pegs. Then the output is delivered through the array formula =ds(P27:Q33;S27:S34) in range T28:T34. I change next the Clean Price to 95 and reset the input maturities to the five maturities I used earlier. I also change the rate shift cell N18 back to 1% so that both my spreadsheet formulas and this new direct Deriscope output rely on the exact same inputs. Below is the result that confirms that the Deriscope KRD function delivers the exact same KRD values as the spreadsheet solution:
Full KRD Report
If I change the FALSE value in cell Q33 to TRUE, the ds formula will return an object that contains several intermediate results, which I can then either paste in the spreadsheet or view them through the wizard. Below I have duplicated the Deriscope KRD formula, this time with Full Report= TRUE and I select the output cell in order to force the wizard to display its contents:
You notice that apart from the 5 KRD values shown at the third row from the top, several other quantities of interest are also reported.
For example, the bond's settlement of 10.12.2018, the yield of 0.05144, the base zero curve that was used for the calculations, the dirty price implied by the base zero curve of 97.22, the actual dirty price of also 97.22 that results from adding the current accrual to the given clean price, the All Rates Duration that corresponds to the (B΄--B΄+)/(2Bδ) mentioned above for the given rate shift δ of 1%, the sum of the KRD values and the Modified Duration.
Even more details may be presented by clicking on the little lens icon to the right of Details= as follows:
Here you may see and optionally bring over to the spreadsheet all downward and upward bumped yield curves that were used in the KRD calculation, the respective implied dirty bond prices and the resulting KRD values. The bottom row with the %0D maturity contains the bumped yield curves obtained through a simultaneous parallel shift of all zero rates, the respective implied dirty bond prices and the pseudo-KRD figure that now reflects all rates together.
You may download the spreadsheet produced with the above steps here.
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
