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

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

Gavin08 wrote

StephenWong wrote

The formula that I wrote does not include the accrued interests that is why nothing needs to be subtracted. If you look again, it has more difference from the Excel formula than just the last term. In my opinion, I think the problem with the Excel formula is that it subtracted the undiscounted accrued interests which is way too much. I think it forgot the discount factor.  

What you obtain from the plain present value formula is the dirty (or invoice) price. The clean (quoted) price is obtained after substracting accrued interest for the period behind us that has elapsed since the last coupon payment until today, the settlement day. Accrued interests are not to be discounted, as they lie refer to the past.

To have another view on the question, I typed the bond characteristics of the 4th bond (from the top) in the Excel sheet into Wolfram Alpha's online bond pricer. Here's the result Wolfram calculation

Wolfram's clean price of 99.86 does not help in distinguishing whether QL or Excel is right because their prices only differ from the 3rd decimal place on, but it displays the formula and it's similar to Excel's in that it substracts accrued interests at the end. You can also easily toggle to the dirty price (which is much higher at 102.91) in the top section of the form and you see how the accrued interests substraction is missing there.

I have applied the present value formula as stated before on all the bonds. I changed all the bonds to mature within 5 months. The prices from the qlBondCleanPriceFromYield match the values obtained from that formula.

One can make all the prices agree between qlBonCleanPriceFromYield with the PRICE from Excel if all the settlement dates are on coupon dates. The prices differ whenever the settlement dates don't fall on a coupon date. The settlement dates indeed are on coupon dates for your first three bonds but not for the remainder.  

In conclusion, the two functions are using different formulae to get different results.