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

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

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.