Excel: Method to Calculate the Duration of a Bond

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
• Microsoft Excel for OS/2, versions, 2.2 and 3.0
• Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0

SUMMARY

Duration is a measure of the sensitivity of a bond's price to changes in interest rates. A bond with a high duration will tend to be highly sensitive to a change in interest rates. It is the preferred measure used by portfolio investment managers to evaluate what types of bonds they should include in a portfolio for a given investment objective.

MORE INFORMATION

Example

The example below outlines a method to calculate duration in Microsoft Excel. Consider the following bond for which dividends are paid annually:

   Par value               = $1,000
   Annual coupon rate      = 8 percent
   Term to maturity        = 3 years
   Yield to Maturity       = 10 percent

A1:Per. B1:CFlow C1:PV(CFlow)        D1:PV % of Price E1:A*D
A2:1    B2:80    C2:=-PV(10%,A2,,B2) D2:=C2/$C$5      E2:=A2*D2
A3:2    B3:80    C3:=-PV(10%,A3,,B3) D3:=C3/$C$5      E3:=A3*D3
A4:3    B4:1080  C4:=-PV(10%,A4,,B4) D4:=C4/$C$5      E4:=A4*D4
A5:     B5:PRICE C5:=SUM(C2:C4)      D5:DURATION      E5:=SUM(E2:E4)

Duration is a weighted average time to full recovery of principal and interest payments from a bond. It is calculated as follows by dividing the summation of the present value of the cash flows, multiplied by the time period over which the cash flow occurs, multiplied by the price of the bond.

The price of the bond is in cell C5. The present value of the cash flows divided by the price of the bond is in cells D2:D4. This value multiplied by the time period over which the cash flow occurs is in cells E2:E4. The duration, which is the sum of the values in cells E2:E4, is in cell E5.

REFERENCES

"Function Reference," version 4.0, page 341-342

"Microsoft Excel Function Reference," version 3.0, page 189-190