Microsoft KB Archive/103768
Mac Works: Method to Calculate the Duration of a Bond
PSS ID Number: Q103768 Article last modified on 10-22-1998
The information in this article applies to:
- == Microsoft Works for the Macintosh, version 3.0 and 4.0 ==
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.
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:AD A2:1 B2:80 C2:=-PV(10%,A2,,B2) D2:=C2/$C<math display="inline">5 E2:=A2*D2 A3:2 B3:80 C3:=-PV(10%,A3,,B3) D3:=C3/</math>C<math display="inline">5 E3:=A3*D3 A4:3 B4:1080 C4:=-PV(10%,A4,,B4) D4:=C4/</math>C$5 E4:=A4D4 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.