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

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

Gavin08 wrote

StephenWong wrote

Why are you comparing clean price with dirty price? qlBondCleanPriceFromYield calculates the price without  accrued interests while PRICE from Excel includes that interests. No?

Not true, PRICE excludes accrued interests too. In most of these examples, the accrued interests are about 1 or 2 percentage points, i.e. the dirty prices are about 101 or 102.

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.