C# for Financial Markets

Appendix 4

Cap, Floor and Swaption Using Excel-DNA

A4.1 INTRODUCTION

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.

A4.2 DIFFERENT WAYS OF STRIPPING CAP VOLATILITY

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 , where and . Using the command button ‘CalibrateCapVol’, we can build the caplet ...