Browse By

Bootstrapping the Zero Curve and Forward Rates

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:

bootstrapping1

Figure 1: Zero curve & Forward rates derivation process

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:

TenorRates
7 Days8.48%
15 Days6.27%
30 Days4.68%
60 Days4.18%
90 Days4.03%
120 Days3.97%
180 Days3.88%
270 Days3.93%
365 Days4.04%
2 Years4.23%
3 Years4.35%
4 Years4.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:

bootstrapping2

Figure 3: Interpolated rates

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

bootstrapping3

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:

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

Figure 4: Cash flow grid

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:

bootstrapping5

Figure 5: Cash flows for a 0.25 year tenor bond

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

bootstrapping6

Figure 6: Cash flows for a 0.5 year tenor bond

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

bootstrapping7

Figure 7: Discounted Cash flow grid

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:

bootstrapping8

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:

bootstrapping9

6. If we rearrange this equation we have:

bootstrapping10

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.

bootstrapping11

Figure 8: Discount factor at time 0.25

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:

Figure 9: Discount factor at time 0.25 – alternative generic formula

Figure 9: Discount factor at time 0.25 – alternative generic formula

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.

Figure 10: VLOOKUP function

Figure 10: VLOOKUP function

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. ,bootstrapping14

the accumulation factor, as Acc0.25 (cell B8=1/ df0.25) which works out to 1.010075.

Figure 11: Accumulation factor at time 0.25

Figure 11: Accumulation factor at time 0.25

8. We have

bootstrapping16aWe solve for ZC25 (cell B9) by rearranging the equation as follows:

bootstrapping16b

Figure 12: Zero Rate at time 0.25

Figure 12: Zero Rate at time 0.25

9. Finally for bootstrapping16d

Figure 13: Forward rate at time 0.25

Figure 13: Forward rate at time 0.25

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:

bootstrapping16f

12. We break this equation into two parts. The first part bootstrapping16g(cell C34) we can solve for because we have all the variables: for the 0.5 tenor bond = 0.97 &   bootstrapping16h

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.

Figure 14: Discounted cash flow at time 0.25 of the 0.5 year tenor bond

Figure 14: Discounted cash flow at time 0.25 of the 0.5 year tenor bond

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,

Figure 15: Discounted cash flow at time 0.50 of the 0.5 year tenor bond

Figure 15: Discounted cash flow at time 0.50 of the 0.5 year tenor bond

14. Rearranging the equation in step 11 we have:

bootstrapping16k

The left hand side of the equation,

bootstrapping16l

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

bootstrapping16m

16. If we rearrange this equation we have:

bootstrapping16n

We have labelled this derivation of the discount factor as df0.50 in our sheet (cell C7) which works out to 0.9808822.

Figure 16: Discount factor at time 0.5

Figure 16: Discount factor at time 0.5

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, bootstrapping16p

the accumulation factor, as Acc50 (cell C8 = 1/df0.50). It works out to 1.0194904.

Figure 17: Accumulation factor at time 0.5

Figure 17: Accumulation factor at time 0.5

18. We have bootstrapping16r

Acc5 = 1.0194904. We solve for ZC0.5 (cell C9) by rearranging the equation as follows:

bootstrapping16s

Figure 18: Zero rate at time 0.5

Figure 18: Zero rate at time 0.5

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

bootstrapping16u

Figure 19: Forward rate for the period [0.25,0.5]

Figure 19: Forward rate for the period [0.25,0.5]

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:
ZCtFCt-.25,t
0.254.030%4.030%
0.53.879%3.729%
0.753.930%4.031%
14.042%4.377%
1.254.090%4.283%
1.54.138%4.380%
1.754.187%4.478%
24.236%4.577%
2.254.266%4.512%
2.54.297%4.575%
2.754.328%4.639%
34.360%4.703%
3.254.388%4.733%
3.54.417%4.793%
3.754.446%4.853%
44.475%4.913%
Figure 20: Zero and Forward rate term structure

Figure 21: Zero and Forward rate term structure – graphical representation

Figure 21: Zero and Forward rate term structure – graphical representation

Page-Break-Image-medium

Comodo SSL