Convexity in excel

riotburn · February 29th, 2008, 3:35 am

I need help trying to calculate convexity in Excel. Since it does not have the formula, I have been trying to wrap my brain around it to enter it manually but unsuccessful. Can anyone give me advice on how to either program a formula using the information I have. I have yields for four years of information. I have figured out price, accrued interest, and duration. Any help?

Antonio · February 29th, 2008, 8:09 am

I assume you mean the convexity of a bond. If you have figured out how to calculate the price and the duration, what's your problem in getting the convexity, it's exactly the same kind of formula.

DavidJN · February 29th, 2008, 1:16 pm

As Antonio has noted, this is easily derived or found on the net. Here is an approximation formula that works well and can be found in the writings of Frank Fabozzi:result = (pyieldup + pyielddown - 2.0*dirty_price) / (dirty_price*shift*shift*100.0);shift is a small shift from the current bond yield (say one basis point)dirty_price is the bond dirty price per $100 par (i.e. clean price plus accrued)Pyieldup is the clean bond price when the yield is shifted upwards by the shift amount and so on.

riotburn · February 29th, 2008, 4:30 pm

Well for Antonio, I used Excel formula to calculate price and duration which is it a lot easier, at least for duration. And Excel doesnt have a formula for convexity of a bond. The formula I have is Cx = {Summation[PV(CFt)*t^2] + PV(FV)*t^2}/2Price I can't figure out how to use it to calculate the convexity for the daily changes in yield and price.

Antonio · February 29th, 2008, 6:33 pm

ok, sorry for the rough answer, it's been a pretty bad day. Anyway, I thaught you were calculating it not using Excel's formula (which, from my opinion you should do if you've never done it), meaning basically, summing and multipling cells. Otherwise, you can just create the function Convexity in VBA, and then Call it from Excel.

DavidJN · February 29th, 2008, 7:57 pm

What are you analyzing historical data for? Historical price and yield data have nothing whatsoever to do with a duration or convexity calculation. Duration and convexity are measures which give insight into how bond prices may move in the future relative to current values.

riotburn · February 29th, 2008, 8:10 pm

Got to do it, doesn't matter why. Still trying to figure it out, how exactly do i summate cash flows, are cash flows the accrued interest for that day? and if so what should t be equal? days left to coupon payment or expiration?Always a lovely day for a guinness.

SeaHawk · October 4th, 2011, 2:45 pm

Do you know what the rationale is to divide the convexity number by 100 in DavidJN's result? result = (pyieldup + pyielddown - 2.0*dirty_price) / (dirty_price*shift*shift*100.0);

haowilhelm · October 6th, 2011, 1:59 pm

same question on this, where the 100 comes from?tks.QuoteOriginally posted by: SeaHawkDo you know what the rationale is to divide the convexity number by 100 in DavidJN's result? result = (pyieldup + pyielddown - 2.0*dirty_price) / (dirty_price*shift*shift*100.0);

bearish · October 6th, 2011, 3:43 pm

The effect of dividing by 100 is to scale the convexity to be the change in duration (in years) per 1% shift in the yield (curve). If it doesn't feel natural, I would suggest taking a few minutes and playing with a simple example in Excel.