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

[Gavin08]

Hi,
when I price a fixed-rate coupon bond with Quantlibxl, sometimes I get slight differences in the clean price compared to what Excel 2010's PRICE formula gives. For example, Quantlib returns 99.995 and PRICE returns 100.005. However, on other occasions, results look identical up to the the 6 or 7 digits I check.

Of course I use the same day count convention (30/360 US-style, for US bonds) with both functions.
I use the NullCalendar as calendar in Quantlib, but have also tried other calendar settings. As for the other bond settings, I keep them all at their Quantlib default values (although tweaking around with them didn't improve results robustly).

Any ideas as to where these calculation differences come from? Which one to trust more - quantlibxl or Excel that's been around for decades now? ;)

Best,
Gavin

[MikeD]

If you are pricing US Treasury Notes/Bonds, you are using the wrong settings somewhere.  Copy the settings in the attached example spreadsheet, and you will see how everything matches properly.

- Mike

On Thu, Oct 27, 2011 at 1:07 PM, Gavin08 <[hidden email]> wrote:

Hi,
when I price a fixed-rate coupon bond with Quantlibxl, sometimes I get
slight differences in the clean price compared to what Excel 2010's PRICE
formula gives. For example, Quantlib returns 99.995 and PRICE returns
100.005. However, on other occasions, results look identical up to the the 6
or 7 digits I check.

Of course I use the same day count convention (30/360 US-style, for US
bonds) with both functions.
I use the NullCalendar as calendar in Quantlib, but have also tried other
calendar settings. As for the other bond settings, I keep them all at their
Quantlib default values (although tweaking around with them didn't improve
results robustly).

Any ideas as to where these calculation differences come from? Which one to
trust more - quantlibxl or Excel that's been around for decades now? ;)

Best,
Gavin

--
View this message in context: http://old.nabble.com/Fixed-rate-bond-pricing-can-yield-different-results-compared-to-Excel%27s-built-in-functions-tp32733269p32733269.html
Sent from the quantlib-users mailing list archive at Nabble.com.


------------------------------------------------------------------------------
The demand for IT networking professionals continues to grow, and the
demand for specialized networking skills is growing even more rapidly.
Take a complimentary Learning@Cisco Self-Assessment and learn
about Cisco certifications, training, and career opportunities.
http://p.sf.net/sfu/cisco-dev2dev
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

------------------------------------------------------------------------------
The demand for IT networking professionals continues to grow, and the
demand for specialized networking skills is growing even more rapidly.
Take a complimentary Learning@Cisco Self-Assessment and learn
about Cisco certifications, training, and career opportunities.
http://p.sf.net/sfu/cisco-dev2dev
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

[Gavin08]

I was pricing U.S. corporate bonds. If it helps I can compile a sheet where the problem manifests.

Mike DelMedico wrote

If you are pricing US Treasury Notes/Bonds, you are using the wrong settings
somewhere.  Copy the settings in the attached example spreadsheet, and you
will see how everything matches properly.

- Mike

On Thu, Oct 27, 2011 at 1:07 PM, Gavin08 <[e-mail]> wrote:

>
> Hi,
> when I price a fixed-rate coupon bond with Quantlibxl, sometimes I get
> slight differences in the clean price compared to what Excel 2010's PRICE
> formula gives. For example, Quantlib returns 99.995 and PRICE returns
> 100.005. However, on other occasions, results look identical up to the the
> 6
> or 7 digits I check.
>
> Of course I use the same day count convention (30/360 US-style, for US
> bonds) with both functions.
> I use the NullCalendar as calendar in Quantlib, but have also tried other
> calendar settings. As for the other bond settings, I keep them all at their
> Quantlib default values (although tweaking around with them didn't improve
> results robustly).
>
> Any ideas as to where these calculation differences come from? Which one to
> trust more - quantlibxl or Excel that's been around for decades now? ;)
>
> Best,
> Gavin
>
> --
> View this message in context:
> http://old.nabble.com/Fixed-rate-bond-pricing-can-yield-different-results-compared-to-Excel%27s-built-in-functions-tp32733269p32733269.html
> Sent from the quantlib-users mailing list archive at Nabble.com.
>
>
>
> ------------------------------------------------------------------------------
> The demand for IT networking professionals continues to grow, and the
> demand for specialized networking skills is growing even more rapidly.
> Take a complimentary Learning@Cisco Self-Assessment and learn
> about Cisco certifications, training, and career opportunities.
> http://p.sf.net/sfu/cisco-dev2dev
> _______________________________________________
> QuantLib-users mailing list
> [e-mail]
> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>

 
------------------------------------------------------------------------------
The demand for IT networking professionals continues to grow, and the
demand for specialized networking skills is growing even more rapidly.
Take a complimentary Learning@Cisco Self-Assessment and learn
about Cisco certifications, training, and career opportunities.
http://p.sf.net/sfu/cisco-dev2dev
_______________________________________________
QuantLib-users mailing list
[e-mail]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

[Gavin08]

Download this worksheet (using "save as") for a few examples of bonds where the prices coincide (the top 3 rows), and where they differ slightly (the rows below). Every row represents a different case/bond. Check out the blue area to compare prices.

Would be glad to hear some feedback.

Thanks and regards,
Gavin

Gavin08 wrote

I was pricing U.S. corporate bonds. If it helps I can compile a sheet where the problem manifests.

[StephenWong]

Gavin08 wrote

Download this worksheet (using "save as") for a few examples of bonds where the prices coincide (the top 3 rows), and where they differ slightly (the rows below). Every row represents a different case/bond. Check out the blue area to compare prices.

Would be glad to hear some feedback.

Thanks and regards,
Gavin

Gavin08 wrote

I was pricing U.S. corporate bonds. If it helps I can compile a sheet where the problem manifests.

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

[Gavin08]

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.

[StephenWong]

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.



 

[Gavin08]

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?

[StephenWong]

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?

[Gavin08]

StephenWong wrote

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?

I beg to differ. Your formula is for the dirty price. The clean price (which is what the QL function BondCleanPrice is about) is obtained by substracting the accrued interests from the dirty price. Accrued interests are the amount you need to pay the person selling you the bond to compensate her for missing on the next coupon payment, because you are trading at a mid-coupon date. In your example she deserves 1 or 2 months divided by 6 of the next coupon payment.
And as I pointed out in an earlier post, the accrued interests would be a much larger order of magnitude than the small differences with observe currently, i.e. 1 or 2 per cent instead of <0.01. There must be another source for that difference.

[StephenWong]

Gavin08 wrote

StephenWong wrote

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?

I beg to differ. Your formula is for the dirty price. The clean price (which is what the QL function BondCleanPrice is about) is obtained by substracting the accrued interests from the dirty price. Accrued interests are the amount you need to pay the person selling you the bond to compensate her for missing on the next coupon payment, because you are trading at a mid-coupon date. In your example she deserves 1 or 2 months divided by 6 of the next coupon payment.
And as I pointed out in an earlier post, the accrued interests would be a much larger order of magnitude than the small differences with observe currently, i.e. 1 or 2 per cent instead of <0.01. There must be another source for that difference.

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.  

[Gavin08]

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.

[StephenWong]

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.

[Gavin08]

StephenWong wrote

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.

True, it's a good observation that prices only diverge whenever we are at a mid-coupon date and perfectly match at coupon dates. But which formula - QL's or Excel's - is more accurate? Or where does the difference come from? Is there a loss of precision at some point of the calculation due to rounding (hard to believe)?

Would be great if somebody knowledgeable about the internals of Quantlib could shed light on this.