Expert: Doug Ingram - 4/21/2004

Question
Hi Doug

Have you got an example of a bootstrapped zero coupon calculation in Excel?

What I have in mind is that given some coupon bonds close to par for different maturities being able to derive a bootstrapped zero implied forward rates.

I'm just starting to think about different yield curves in Excel.

Thanks

Alan

Answer
Since no accrued interest is involved, zero coupons are really easy.
Actually, all bond calculations simply discount future cash flows to gauge a return.  A coupon paying bond is treated as a series of mini-zero coupon bonds.

Each cash flow is divided by:
(1 + yield) ^ years (or periods held)

A thirty year zero to yield 5% would be worth 23.13774
PV = Par / (1 + yield) ^ years
PV = 1000 /(1+.05)^30   (x100/1000)
(divide by par and multiply by 100 to get price)
Just put the variables in Excel cells

To get the return, reverse the equation:
Yield = ((Par / PV) ^ (1 / Years)) - 1

To get semi-annual yields, double the time and halve the yield:
Yield = (((Par / PV) ^ (1 / Periods) -1 ) * 2
or
PV = Par/(1+yield/2)^ (periods)

For uneven periods, simply use the portion of the year:
You can use the days360 function in excel (divide by 360) to
get the years.

years = days360(cell with start date,cell with end date)/360
For 2/25/2001 to 5/10/2006 you should get 5.2083

PV= (1000/ (1+ yield) ^ 5.2083)

Hope that helps.