Mac Works: Method to Calculate the Duration of a Bond
  
PSS ID Number: Q103768
Article last modified on 10-22-1998
 
3.0 4.0
 
MACINTOSH
 

======================================================================
-------------------------------------------------------------------
The information in this article applies to:
 
 - Microsoft Works for the Macintosh, version 3.0 and 4.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 a
Microsoft Works spreadsheet. 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 spreadsheet 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 "duration" 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.
 
KBCategory: kbother
KBSubcategory: macworkskb
 
Additional reference words: 3.00  mac mwksss m_eXcel
======================================================================
Version           : 3.0 4.0
Platform          : MACINTOSH
Issue type        : kbhowto
=============================================================================
Copyright Microsoft Corporation 1998.