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% |
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:
- Coupon25 = 4.03%/4 *100 = 1.0075
- Principal25 = 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:
- Coupon25 = 3.88%/4 *100 = 0.97
- Coupon50 = 3.88%/4 *100 = 0.97
- Principal50 = 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 df0.25 in our EXCEL work sheet (cell B7), which works out to 0.99025.
Note:
The value of df is based on the discounted final cash flow and the final cash flow at maturity of a given bond. To keep the formula for df general for all tenors, instead of redefining the cells for each tenor, we alternatively have used the VLOOKUP function in EXCEL to find these final cash flows and their discounted values in the constructed grids:
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 Acc0.25 (cell B8=1/ df0.25) which works out to 1.010075.
8. We have
We solve for ZC25 (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:
- Coupon25 = 0.97
- Coupon50 + Principal0.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 (cell C34) we can solve for because we have all the variables: for the 0.5 tenor bond = 0.97 &
is df25, 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 df0.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 Acc50 (cell C8 = 1/df0.50). It works out to 1.0194904.
18. We have
Acc5 = 1.0194904. We solve for ZC0.5 (cell C9) by rearranging the equation as follows:
19. Finally the forward rate for the period [0.25,0.5is:
![Figure 19: Forward rate for the period [0.25,0.5]](https://financetrainingcourse.com/education/wp-content/uploads/2016/10/bootstrapping16v.png)
20. We repeat this process iteratively for each of the remaining 14 tenors, 0.75 to 4. The resulting zero rates and forward rates term structures are given below:
| ZCt | FCt-.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% |
