Re: Fixed-rate bond pricing can yield different results compared to Excel's built-in functions

Posted by StephenWong on Oct 30, 2011; 3:55pm
URL: http://quantlib.414.s1.nabble.com/Fixed-rate-bond-pricing-can-yield-different-results-compared-to-Excel-s-built-in-functions-tp6340p6348.html

Gavin08 wrote

StephenWong wrote

Nevermind about Excel, the help file has a wrong formula on it.

Anyway if you just want to find out which results to trust, you can always work something out by hand. Since these are all fixed rate bonds, this should be easy.

Just take the last two par bonds, change the maturity date to something shorter so that there will be like 4 or 5 months till maturity. Work it out by hand using

dfraction = (no. of days till next coupon date) / (no. of days in coupon period) using the 30/360 convention of course

Price = 100 * ( 1 + coupon rate/frequency *  dfraction ) / ( 1 + yield / frequency )^dfraction.

I got numbers that agree with quantlib and not Excel.

Thanks for your comment.
Isn't the substraction of accrued interests missing in your formula of the clean price?
And I don't see where the Excel PRICE formula (see online help) is flawed, it's pretty identical to yours (except for the accrued interests). Maybe I'm missing something. Can you elaborate?

I did not subtract anything in the formula because I did not see why I should subtract something that had never been included in the first place. The formula gives the discounted fraction of the coupon plus the principle that the holder of the bond will get. There is nothing to subtract. And I think this is what the quantlib function gives you. Agree?