Bond duration problem

[Przemo]

Hey guys,

I'm stuck - I thought I know how to calculate duration/price for any bond, but it seems I was wrong. I have troubles when I need to calculate price or the duration of the bond if there is time to maturity that is not consistent with coupons. E.g. like in the topic of the thread:

Settlement: 2010/01/01
Maturity: 2013/07/01
Coupon: 1%
YTM: 1%
Par: $100

And now - the price should be $100 I guess (I thought I know, but Excel seems to disagree and the function PRICE() returns 99.99876). I assume that when calculating duration, what I need to do is to assume that the last coupon is half the previous ones, am I right? And the time periods are t= [1, 2, 3, 3.5]. Such approach however, gives different results than the excel build-in function DURATION(). I'm stuck for a few days right now, can you help me anyhow?

 

[Lyle_C]

Well, you probably need to think about the coupon payment frequency. If it's the usual semiannual, then it would be 0.5% coupon every half a year. Every coupon would be the same size.

 

[Przemo]

I did not add it but I mean annual frequency of coupons. There is a problem - what about this last cash flow and periods.

 

[Lyle_C]

If it's annual, then 1% coupon would be paid on 07/01 of every year. Every coupon would still be the same size. Since settlement date is not a coupon date, partial coupon is accrued.

 

[Przemo]

It makes sense actually thanks I’ll try

 

[Przemo]

Yes, it's correct of course, however the results are still inconsistent with the ones obtained with the function... :/ anything?

 

[Lyle_C]

Well, show us how you implemented it, what outputs you are getting, and what outputs you expected.

 

[Przemo]

Here you go, there is a file attached as well:

Honestly, I have no idea what is wrong here....I just cannot spot any mistake :/

 

[Lyle_C]

I made a few corrections:
- You are using wrong formulas for clean price, accrued interest, and dirty price. There are built-in Excel formulas for clean price and accrued interest.
- DCF*t not discounted correctly.
- 2012 is leap year. You need to subtract one day from some of the time length calculations. It's just how it is with Act/365.
- You have duration with both dirty price and clean price. As far as I know, duration is used with dirty price.

 

[Przemo]

Man, you’re incredible!! Thank you so much...I haven’t done enough exercises like that, I thought differently about the accrued interest. Also the leap year was somehow confusing. Thank you!!!