Microsoft KB Archive/77121

{|
 * width="100%"|

Excel: Method to Calculate the Duration of a Bond

 * }

-

The information in this article applies to:


 * 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.

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

Create a worksheet as follows:

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)

The value for &quot;duration&quot; will be in cell E5.

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.