# 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**and plug everything in the formula to get:

_{+}-B_{-}**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 **B _{1}+B_{2}+…B_{n}** of the present values of the bond's cash flows

**B**. Since a derivative acts linearly on a sum, it follows:

_{1}, B_{2}, …, B_{n}**dB/****dy = ****dB _{1}/**

**dy+**

**dB**

_{2}/**dy+…+**

**dB**

_{n}/**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 **i ^{th}** cash flow

**C**paid at time

_{i}**T**must equal

_{i}**e**, meaning that

^{-yTi}**Bi = e**and therefore:

^{-yTi}C_{i}**dB _{i}/**

**dy =**

**d(e**

^{-yTi}C_{i})/**dy = -C**

_{i}T_{i}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 **C _{i}** is the same flat yield

**y**, regardless of the time

**T**on which the

_{i}**C**is paid. Note here that

_{i}**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

**T**in isolation! The only value of

_{i}**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 **C _{i}** changes by the same amount

**Δy**(discrete or infinitesimal), regardless of the time

**T**.

_{i}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* **K _{i}** is defined with respect to a given maturity

**T**and an absolute one-sided rate shift

_{i}**δ**as follows:

**K _{i} = (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 **z**_{1}, z_{2},…,z** _{N}** 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

**i**input zero rate

^{th}**z**

**downwards by the given amount**

_{i}**δ**, 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

**i**input zero rate

^{th}**z**

**upwards by the given amount**

_{i}**δ**, 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 **K _{i}** has the following two consequences:

**Consequence 1:** For the sum **K =** **K _{1}+K_{2}+…+K_{N }**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 **e ^{ct}-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 = e ^{ct}-1 => r = (e^{ct}-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 **K _{i} = (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 =** **K _{1}+K_{2}+…+K_{N }**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