Calculating DV01 using Excel

Exercise from book 23.1:

A 3-year coupon bond has payments as follows:

Bond Cash Flow at Year

Year 1: $8

Year 2: $8

Year 3: $108

This 8% coupon bond is currently traded at par ($100).

Compute the ordinary DV01 (calculated with respect to the annually compounded bond yield)

Solution:

The first formula for PV is entered to confirm the information provided in the question.

The Excel formula for the Present Value with an Uneven Cash Flow (Cell C5): =NPV(C6,D6,E6,F6)

(Additional years can be added if needed, enter the next value in cell G6 and add G6 to the PV formula.)

DV01 is the dollar value of one basis point yield decrease. In this example, the Interest Rate started at .08 so find the PV at the new interest rate of .079.

The second formula for PV calculates the PV at the .079 interest rate.

Solve for DV01 = (dP / dr) * -.0001

The Excel formula for DV01 (Cell G14): =((B2-C10)/(B3-C11))*-0.0001 = **0.02582**