Question

What is meant by duration of a bond? Describe the steps followed in finding the duration of a bond in Excel when built-in Excel functions are not used.

Answer 1

Duration of the bond measures its price sensitivity if the interest rates are changed. For example if interest rate will rise by 1% then how much approximate decrease is there in the price of bond can be measured by the duration of the bond.

The steps followed in finding the duration of a bond in Excel

Let’s take the following example to calculate duration in excel:

A two-year bond that pays an annual coupon of 5%, returns the face value, and has a current yield to maturity of 4.5%. Use $1000 as the face value.

So we have the following information

Face value or maturity value M = $1000

Coupon rate = 5% per annum, therefore C = 5% of $1000 = $50

Maturity time = 2 years, therefore n = 2

Yield to maturity i = 4.5% per annum

Duration calculation:

Year (t)	Cash Flow from coupon payments and maturity amount (CF)	Present value (PV) of CF discounted at 4.5% [PV=CF/(1+4.5%)^t]	PV *t
1	$50.0	$47.85	$47.85
2	$50.0	$45.79	$91.57
2	$1,000.0	$915.73	$1,831.46
Sum		$1,009.36	$1,970.88
		Bond's Price↑
	Duration = Sum of (PV*t)/Bond's Price =	$1,970.88/$1,009.36	1.95	Years

Therefore Duration is 1.95 years (for a coupon paying bond, duration is always less than its maturity period)