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

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

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?