Convexity
Author Message
Convexity

Looking for an Excel add-in to calculate bond convexity.

Tue, 26 Jul 2005 07:20:10 GMT
Convexity
Hi Steve,

If memory serves, Convexity is generally defined as the change in duration
for a 100bp increase in yield.  Therefore you should be able to calculate it
by two calls to the Duration function.

HTH

Peter Beach

Quote:
> Looking for an Excel add-in to calculate bond convexity.

Tue, 26 Jul 2005 07:46:55 GMT
Convexity
Steve,
I just wrote this and only tested it on one bond, so no guarantees . I have
to caution you that the standard accrued interest function that comes with
Excel is not very good. You may want to write your own accrued interest
function. To make this function work you need to reference an add-in (in
VBA) that is in C:\Program Files\Microsoft Office\Office10\Library\Analysis
on my machine, but you may have to do a search. The addin  is ATPVBAEN.XLA .

I changed the rate up and down 10 basis points rather than 100 as Peter
Beach recommended, because I found it matched the convexity on Bloomberg
better.

Function convexity(Settlement, Maturity, Rate, Yield, Redemption, Frequency,
Basis, Issue_Date, First_Coupon)
pricenow = PRICE(Settlement, Maturity, Rate, Yield, Redemption, Frequency,
Basis)
YieldPlus10 = Yield + 0.001
pricePlus10 = PRICE(Settlement, Maturity, Rate, YieldPlus10, Redemption,
Frequency, Basis)
YieldLess10 = Yield - 0.001
priceLess10 = PRICE(Settlement, Maturity, Rate, YieldLess10, Redemption,
Frequency, Basis)
accrued = accrint(Issue_Date, First_Coupon, Settlement, Rate, 100,
Frequency, Basis)
convexity = 10000 / (accrued + pricenow) * ((priceLess10 - pricenow) -
(pricenow - pricePlus10))
End Function

Regards,
Don Pistulka

Quote:
> Looking for an Excel add-in to calculate bond convexity.

Tue, 26 Jul 2005 10:02:32 GMT
Convexity
Hi Don,

Quote:

> I changed the rate up and down 10 basis points rather than 100 as Peter
> Beach recommended, because I found it matched the convexity on Bloomberg
> better.

That's interesting.  Perhaps my memory is playing me false.  I thought it
was a pure upwards shift (rather than averaging up and down shifts).  It
*should* be an average of up and down shifts of course, but my memory was
that that was not the market convention.  Do you get closer to the Bloomberg
number if you take up and down shifts of 1 bp and multiply the result by 10?

Regards,

Peter Beach

Tue, 26 Jul 2005 10:38:29 GMT
Convexity

Hi Peter,

No, I found that 10 basis points matched Bloomberg best.  The last time I
checked my formulas against Bloomberg's was over 5 years ago, so they may be
using something else now. When I used 100 it was usually slightly different
out around the third decimal place.

You are correct about using duration changes and it may be easier to use
than price change in Excel, sense duration is already a built in function,
however I knew that this formula was accurate for me in the past, so I
thought I would be helpful to Steve.

Don

Quote:
> Hi Don,

> > I changed the rate up and down 10 basis points rather than 100 as Peter
> > Beach recommended, because I found it matched the convexity on Bloomberg
> > better.

> That's interesting.  Perhaps my memory is playing me false.  I thought it
> was a pure upwards shift (rather than averaging up and down shifts).  It
> *should* be an average of up and down shifts of course, but my memory was
> that that was not the market convention.  Do you get closer to the
Bloomberg
> number if you take up and down shifts of 1 bp and multiply the result by
10?

> Just interested (and not currently having access to a Bloomberg terminal).

> Regards,

> Peter Beach

Tue, 26 Jul 2005 11:04:26 GMT
Convexity
Hi Don,

Thanks for that.  Another one to add to the knowledge bank.

Peter Beach

Quote:

> Hi Peter,

> No, I found that 10 basis points matched Bloomberg best.  The last time I
> checked my formulas against Bloomberg's was over 5 years ago, so they may
be
> using something else now. When I used 100 it was usually slightly
different
> out around the third decimal place.

> You are correct about using duration changes and it may be easier to use
> than price change in Excel, sense duration is already a built in function,
> however I knew that this formula was accurate for me in the past, so I
> thought I would be helpful to Steve.

> Don

> > Hi Don,

> > > I changed the rate up and down 10 basis points rather than 100 as
Peter
> > > Beach recommended, because I found it matched the convexity on
Bloomberg
> > > better.

> > That's interesting.  Perhaps my memory is playing me false.  I thought
it
> > was a pure upwards shift (rather than averaging up and down shifts).  It
> > *should* be an average of up and down shifts of course, but my memory
was
> > that that was not the market convention.  Do you get closer to the
> Bloomberg
> > number if you take up and down shifts of 1 bp and multiply the result by
> 10?

> > Just interested (and not currently having access to a Bloomberg
terminal).

> > Regards,

> > Peter Beach

Tue, 26 Jul 2005 11:16:24 GMT
Convexity
Steve & Peter

I made a slight adjustment to the convexity last function I posted to make
it more user-friendly in day-today use. I substituted the coupncd() and
couppcd() built-in functions for the need to enter issue date and first
coupon date. This makes the convexity calculation correct as long as you are
not in a long or short coupon payment period.

Regards,

Don Pistulka

Function convexity(Settlement, Maturity, Rate, Yield, Redemption, Frequency,
Basis)
pricenow = Price(Settlement, Maturity, Rate, Yield, Redemption, Frequency,
Basis)
YieldPlus10 = Yield + 0.001
pricePlus10 = Price(Settlement, Maturity, Rate, YieldPlus10, Redemption,
Frequency, Basis)
YieldLess10 = Yield - 0.001
priceLess10 = Price(Settlement, Maturity, Rate, YieldLess10, Redemption,
Frequency, Basis)
next_Coupon = coupncd(Settlement, Maturity, 2, 1)
last_Coupon = couppcd(Settlement, Maturity, 2, 1)
accrued = accrint(last_Coupon, next_Coupon, Settlement, Rate, 100,
Frequency, Basis)
convexity = 10000 / (accrued + pricenow) * ((priceLess10 - pricenow) -
(pricenow - pricePlus10))
End Function

Quote:
> Hi Don,

> Thanks for that.  Another one to add to the knowledge bank.

> Peter Beach

> > Hi Peter,

> > No, I found that 10 basis points matched Bloomberg best.  The last time
I
> > checked my formulas against Bloomberg's was over 5 years ago, so they
may
> be
> > using something else now. When I used 100 it was usually slightly
> different
> > out around the third decimal place.

> > You are correct about using duration changes and it may be easier to use
> > than price change in Excel, sense duration is already a built in
function,
> > however I knew that this formula was accurate for me in the past, so I
> > thought I would be helpful to Steve.

> > Don

> > > Hi Don,

> > > > I changed the rate up and down 10 basis points rather than 100 as
> Peter
> > > > Beach recommended, because I found it matched the convexity on
> Bloomberg
> > > > better.

> > > That's interesting.  Perhaps my memory is playing me false.  I thought
> it
> > > was a pure upwards shift (rather than averaging up and down shifts).
It
> > > *should* be an average of up and down shifts of course, but my memory
> was
> > > that that was not the market convention.  Do you get closer to the
> > Bloomberg
> > > number if you take up and down shifts of 1 bp and multiply the result
by
> > 10?

> > > Just interested (and not currently having access to a Bloomberg
> terminal).

> > > Regards,

> > > Peter Beach

Wed, 27 Jul 2005 01:29:13 GMT

 Page 1 of 1 [ 7 post ]

Relevant Pages