6 mins read

Figure 20: Zero and Forward rate term structure

Deriving zero rates and forward rates using the bootstrapping process is a standard first step for many valuation, pricing and risk models. Interest rate and cross currency swaps & interest rate options pricing & VaR models, revolving credit facilities & term B loans valuation models, Black Derman Toy interest rate models, etc. all make use of the zero rates and/or forward rates derived from the bootstrapping process.

Understanding how to build the bootstrapping model in EXCEL is therefore an essential corner stone to building more complex models that depend on its results. In this post we will walk you through the process of building a zero curve bootstrapping model in EXCEL.

In general the bootstrapping calculation follows the process depicted below:

It is usually steps 3 to 6, the iterative process of the model that is a cause of confusion among students when constructing the bootstrapping model in EXCEL.

Let us consider the following par term structure:

Tenor | Rates |

7 Days | 8.48% |

15 Days | 6.27% |

30 Days | 4.68% |

60 Days | 4.18% |

90 Days | 4.03% |

120 Days | 3.97% |

180 Days | 3.88% |

270 Days | 3.93% |

365 Days | 4.04% |

2 Years | 4.23% |

3 Years | 4.35% |

4 Years | 4.46% |

*Figure 2: Sample par term structure*

Assume that we are going to price a 4-year tenor, quarterly coupon paying security.

We would need a par term structure that has rates for each quarterly tenor from 0.25 years to 4 years. So our first step is to interpolate the rates for the missing tenors [1.25 years, 1.5 years, 1.75 years, 2.25 years, 2.5 years, 2.75 years, 3.25 years, 3.5 years, 3.75 years] in the par term structure. We use the following linear interpolation formula for this purpose:

As you can see from the screen shot above, the rate at tenor 1.25 (cell E10) is derived as follows:

Once all the par term structure rates have been derived, we us the bootstrapping method for deriving the zero curve from the par term structure. This is an iterative process that allows us to derive a zero coupon yield curve from the rates/ prices of coupon bearing instruments.

The bootstrapping & zero and forward rates derivation process is as follows:

- Our first step is to prepare a grid that shows us the stripped coupon and principal cash flows of the par bonds:

Row 13 labelled Coupon shows the par term structure including the derived rates for the missing tenors.

The stripped cash flows are calculated as follows:

The formula says that if the tenor of the grid as shown in column A [cash flows] exceeds the tenor of the bond [row 14] then a zero value will be returned otherwise:

If the tenor of the grid equals the tenor of the bond the cash flow will equal the coupon and principal amount due on the maturity of the bond, i.e.

PAR VALUE + PAR VALUE * Coupon Rate/ Payment Mode

If the tenor of the grid is less than the tenor of the bond the cash flow will equal the coupon of the bond, i.e.

PAR VALUE * Coupon Rate/ Payment Mode

For the 0.25-year tenor par bond we have the following cash flows:

- Coupon
_{25}= 4.03%/4 *100 = 1.0075 - Principal
_{25}= 100

i.e., 100 + 100*4.03%/4 = 101.0075 at tenor 0.25

For the 0.5-year tenor par bond we have the following cash flows:

- Coupon
_{25}= 3.88%/4 *100 = 0.97 - Coupon
_{50}= 3.88%/4 *100 = 0.97 - Principal
_{50}= 100

i.e. 100*3.88%/4 = 0.97 at tenor 0.25 years & 100 + 100*3.88%/4 at tenor 0.5 years= 100.97

2. The second step is to prepare a grid that shows the discounted cash flows for each bond:

3. The price of the bond is equivalent to the sum of the present value of each cash flow discounted using the relevant zero rates over the respective tenors. For a quarterly payment frequency this means that:

Under the assumption of par bonds, the bond price, at time 0 is equal to it face value, which we will assume is 100.

4. As you can see from the formula above, the discounted values are functions of zero rates and we have yet to derive these rates. This issue is solved when we take into account the par bond assumption and the iterative process. The discounted cash flow for the shortest tenor bond & zero rate for the first tenor will be solved for using only the par bond assumption. The discounted cash flows & zero rates for later tenors will be solved for using the par bond assumption and the zero rates derived for the earlier tenors. This is illustrated in the steps that follow.

5. Let us start with the shortest tenor bond, the 0.25 year bond. Its cash flows are coupon and principal payable at maturity of 101.0075. The present value of the 0.25 year tenor bond at time 0 should equal 100 under our par bond assumption. Hence, according to the price formula we have:

6. If we rearrange this equation we have:

We have labelled this derivation of the discount factor as df_{0.25} in our EXCEL work sheet (cell B7), which works out to 0.99025.

__Note:__The value of df is based on the

The values in cells B34 and B15 are picked up with the help of VLOOKUP formulas that refer to the tenor & specified column index number and pick up the relevant discounted cash flow and cash flow values respectively.

For example the value in cell B34 is picked up from the discounted cash flow grid specified in table array $A$34:$Q$49 by reference to the tenor as given in cell B6 (0.25) and the cash flows in column B. *Given our table array the column reference in the VLOOKUP should be 2, i.e. the second column counting from column A. We have chosen to define this as a variable B$5+1 in the formula, but alternately it may deterministically specified as 2.*

7. We have labelled the inverse of the formula in Step 6, i.e. ,

the accumulation factor, as Acc_{0.25 }(cell B8=1/ df_{0.25}) which works out to 1.010075.

8. We have

_{25} (cell B9) by rearranging the equation as follows:

9. Finally for

10. Let us now move to the next bond, the 0.5 year tenor bond. Its cash flows are as follows:

- Coupon
_{25}= 0.97 - Coupon
_{50}+ Principal_{0.50}= 100.97

11. The present value of this bond at time zero should equal 100 under our par bond assumption. Hence, according to the price formula we have:

12. We break this equation into two parts. The first part

is df_{25, }which we have already solved for in step 6 above. The discounted value of the first coupon of the 0.5 tenor bond works out to have 0.97*0.99025 = 0.9603.

**Note:** The HLOOKUP($A34,$B$6:$Q$30,2,0) picks up the df value with reference to the tenor 0.25 (cell A34) in the table array $B$6:$Q$30 from the second row (row 7) of the table array, i.e. the value in cell B7.

13. The discounted cash flow of time 0.50 in cell 35 is then calculated using the par bond assumption,

14. Rearranging the equation in step 11 we have:

The left hand side of the equation,

equals 99.04 as calculated in Step 13 above, so we have the equation:

16. If we rearrange this equation we have:

We have labelled this derivation of the discount factor as df_{0.50} in our sheet (cell C7) which works out to 0.9808822.

VLOOKUP(C6,$A$34:$Q$49,C$5+1,0) pulls in the value from cell C35 while VLOOKUP(C6,$A$15:$Q$30,C$5+1,0) brings in the value from cell C16.

17. We have labelled the inverse of the formula in Step 16,

the accumulation factor, as Acc_{50 }(cell C8 = 1/df_{0.50}). It works out to 1.0194904.

18. We have

Acc_{5} = 1.0194904. We solve for ZC_{0.5} (cell C9) by rearranging the equation as follows:

19. Finally the forward rate for the period [0.25,0.5is:

ZC_{t} | FC_{t-.25,t} | |

0.25 | 4.030% | 4.030% |

0.5 | 3.879% | 3.729% |

0.75 | 3.930% | 4.031% |

1 | 4.042% | 4.377% |

1.25 | 4.090% | 4.283% |

1.5 | 4.138% | 4.380% |

1.75 | 4.187% | 4.478% |

2 | 4.236% | 4.577% |

2.25 | 4.266% | 4.512% |

2.5 | 4.297% | 4.575% |

2.75 | 4.328% | 4.639% |

3 | 4.360% | 4.703% |

3.25 | 4.388% | 4.733% |

3.5 | 4.417% | 4.793% |

3.75 | 4.446% | 4.853% |

4 | 4.475% | 4.913% |

Published by

October 22, 2016 6:07 am

What is the best way of evaluating portfolio performance allocation strategies? Should we just compare risk, return or risk adjusted…

February 5, 2019 12:51 pm

6 mins read Introducing Project Plain speak. Currently a work in progress Plain speak focuses on bringing intelligent financial reporting…

January 22, 2019 6:08 am

9 mins read What can we learn about oil markets from the last ten years? The next decade. The final…

January 19, 2019 3:47 pm

5 mins read What does the data say about future direction of crude oil markets. We look at OPEC spare…

January 19, 2019 12:44 pm

5 mins read What factors would impact crude oil supply side equation in 2019? Russian non compliance, lower breakeven for…

January 19, 2019 5:14 am

5 mins read How would you model demand for crude oil? What are the key components? What is the long…

January 18, 2019 4:46 pm

This website uses cookies.