floating rate bond pricing on excel using quantlib

[Silvia Buttarazzi]

BootstrapCurvaEonia&Swap6M&GvTItalia&PricingEngines.xlsx
Hi,
I'm trying to price a plain floating rate bond (namely a CCT€) using the quantlib functions developed for excel (please see attached file, sheet 12.BondTassoVariabile)
Unfortunately, the qlBondCleanPrice is always returning #NUM! although I think I set the needed parameters.
Could you please help me in understanding where I get wrong? Thank you very much indeed.
Moreover I would like to know if you could suggest a different way to price a floating rate bond. I was trying to use the function CCTE but stil I'm not able to get a price.
I look forward to hearing from you.
Many thanks in advance for your help.

Silvia








.BootstrapCurvaEonia&Swap6M&GvTItalia&PricingEngines.xlsx

[John O'Sullivan]

Silvia: have you tried calling ohRangeRetrieveError with the reference of the erroring cell as a paramter? That will give you a more detailed error message.

On 26 April 2017 at 14:16, Silvia Buttarazzi <[hidden email]> wrote:

BootstrapCurvaEonia&Swap6M&GvTItalia&PricingEngines.xlsx
<http://quantlib.10058.n7.nabble.com/file/n18225/BootstrapCurvaEonia%26Swap6M%26GvTItalia%26PricingEngines.xlsx>
Hi,
I'm trying to price a plain floating rate bond (namely a CCT€) using the
quantlib functions developed for excel (please see attached file, sheet
12.BondTassoVariabile)
Unfortunately, the qlBondCleanPrice is always returning #NUM! although I
think I set the needed parameters.
Could you please help me in understanding where I get wrong? Thank you very
much indeed.
Moreover I would like to know if you could suggest a different way to price
a floating rate bond. I was trying to use the function CCTE but stil I'm not
able to get a price.
I look forward to hearing from you.
Many thanks in advance for your help.

Silvia








. BootstrapCurvaEonia&Swap6M&GvTItalia&PricingEngines.xlsx
<http://quantlib.10058.n7.nabble.com/file/n18225/BootstrapCurvaEonia%26Swap6M%26GvTItalia%26PricingEngines.xlsx>




--
View this message in context: http://quantlib.10058.n7.nabble.com/floating-rate-bond-pricing-on-excel-using-quantlib-tp18225.html
Sent from the quantlib-users mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

[Ioannis Rigopoulos]

Hi Silvia

I saw you tried the function ohRangeRetrieveError already but you still got #NUM!.

I don't know why no error description is reported in your case.

After I applied a different diagnostic tool on your spreadsheet I found the following error message emanating from QuantLib:

"strike + displacement (-0.0075 + 0) must be non-negative"

Now the cause of the error becomes apparent. The reported value 0.0075 is exactly the spread amount you entered in cell AF3. This is incompatible with the 0% floor strike entered in cell AC3 because QuantLib regards the given floor strike as the least value that the sum rate + spread may attain. Thus a 0% floor strike translates into a negative value (-0.0075) as the minimum allowed value for the rate, which results in error if negative rates are not allowed.

Please try to set floor strike equal or higher than 0.0075 and obviously a higher value for the cap strike in cell AG3.

If this does not work, then try to delete the 0% from the cells AC3 and AG3, effectively leaving them blank. This should definitely work as it signals QuantLib to use the appropriate defaults!

Cheers

Ioannis

On 26.04.2017 15:57, John O'Sullivan wrote:

Silvia: have you tried calling ohRangeRetrieveError with the reference of the erroring cell as a paramter? That will give you a more detailed error message.

On 26 April 2017 at 14:16, Silvia Buttarazzi <[hidden email]> wrote:

BootstrapCurvaEonia&Swap6M&GvTItalia&PricingEngines.xlsx
<http://quantlib.10058.n7.nabble.com/file/n18225/BootstrapCurvaEonia%26Swap6M%26GvTItalia%26PricingEngines.xlsx>
Hi,
I'm trying to price a plain floating rate bond (namely a CCT€) using the
quantlib functions developed for excel (please see attached file, sheet
12.BondTassoVariabile)
Unfortunately, the qlBondCleanPrice is always returning #NUM! although I
think I set the needed parameters.
Could you please help me in understanding where I get wrong? Thank you very
much indeed.
Moreover I would like to know if you could suggest a different way to price
a floating rate bond. I was trying to use the function CCTE but stil I'm not
able to get a price.
I look forward to hearing from you.
Many thanks in advance for your help.

Silvia








. BootstrapCurvaEonia&Swap6M&GvTItalia&PricingEngines.xlsx
<http://quantlib.10058.n7.nabble.com/file/n18225/BootstrapCurvaEonia%26Swap6M%26GvTItalia%26PricingEngines.xlsx>




--
View this message in context: http://quantlib.10058.n7.nabble.com/floating-rate-bond-pricing-on-excel-using-quantlib-tp18225.html
Sent from the quantlib-users mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot

_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Virus-free. www.avast.com

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users