Appendix 4

Cap, Floor and Swaption Using Excel-DNA


In this appendix we extend a number of examples that we introduced in Chapter 17 using the Excel-DNA library. More details on the Excel DNA library are covered in Chapter 22. An understanding of Chapter 17 and Chapter 22 are prerequisites to this appendix. Furthermore, we recommend that you experiment with the corresponding code from the software distribution medium.


In this example we check the behaviour of a mono strike caplet volatility bootstrap. We test all derived classes of the base class MonoStrikeCapletVolBuilder as discussed in Section 17.5.2. We use Excel to manage the process. Input data can be changed from the Excel sheet, so we can also replicate the results of Section 17.6.5.

Using the Excel-DNA library we are able load data from a cap volatility matrix and perform a stripping of cap volatility according to a defined methodology. To this end we open the Excel file ‘CapFloorSwaption.xls’. In the ‘CapVol’ sheet we see information similar to that in Figure A4.1.

Figure A4.1 Screenshot of cap volatilities from Excel


The contents of Figure A4.1 show a cap volatility matrix inline, where and . Using the command button ‘CalibrateCapVol’, we can build the caplet ...

Get C# for Financial Markets now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.