In my post about USD Interest Rate Swaps in Excel, I have explained in detail how to calculate the price and risk of a single USD interest rate swap using a multi-curve approach and actual market data from Bloomberg as of 22 May 2019.
A comparative analysis between the single-curve and multi-curve approach has been laid out in my post focusing on swaps collateralized in a different currency.
A naturally arising question concerns the importance of adopting the theoretically more correct multi-curve framework in the presence of the model risk stemming from the choice of interpolation method used in the curve constructions.
In order to shed some light to this question, I examine below a forward starting USD Libor 3M vs fixed 6M interest rate swap that starts in 7 months from today and has a 5-year tenor, using actual market data from Bloomberg as of 22 May 2019.
The timing of the swap structure has been chosen so that its cash flows fall at times that do not coincide with the nodes of the curve grid that after the second year are one year apart.
Construction of the Required Curve Objects
The first sheet in my workbook is called Curves and contains the formulas that create the various yield curves that are needed to carry out the intended analysis.
Below is the screenshot of the section where the USD OIS-based discounting curve is created in cell C8 as the object with handle name &USD-OIS.1. This construction is the same as that used in the post mentioned in the first link above.
The next curve below contains the USD Futures prices until the first 2 years and the USD swap rates for later maturities.
The construction is based on discounting all cash flows with the produced curve rather than with the OIS curve created above, as the blank entry in cell W15 next to the key Disc Curve= betrays.
This is an intentional choice in order to use the curve &USD-Mkt.1 created in cell N8 as the single curve employed in the later single-curve pricing of my 7M to 5Y forward swap. If I were not interested in this single-curve valuation, I would have chosen to enter the handle name &USD-OIS.1 in cell N8 in order to build in one step the required forecasting curve.
Next, I produce my forecasting curve by re-using the already created curves &USD-Mkt.1 and &USD-OIS.1 as shown below. Details on this construction that involves the type ImpYC Forc shown in cell AB24 are supplied in the post mentioned in the second link above.
Now comes the final table where several curves are constructed with differing interpolations assumptions.
The blue entries on the two left columns contain hard-coded text entries that define the values for the keys Modelled Qty= and Interp Method= that are part of the input data of the Create function of the Yield Curve type. You may refer to my post about building a yield curve out of deposit rates for more information on these two keys.
The entry Nat Cubic refers to Natural Spline Cubic, whereby the second derivative at each grid endpoint is 0.
The entry Fin Cubic refers to Financial Spline Cubic, whereby the second derivative at the left grid endpoint and the first derivative at the right grid endpoint are both 0.
The entry Convex Mono refers to Forward Monotone Convex Spline.
The mathematical details concerning the Deriscope/QuantLib implementation of the various interpolation schemes can be found in the 2006 paper Interpolation Methods for Curve Construction from Hagan and West.
The green entries contain simple text concatenation formulas and the red entries contain the Deriscope formulas that produce the Yield Curve objects with the shown handle names.
Construction of the Forward Interest Rate Swap Object
Below are the three Deriscope formulas that produce the three objects &FxdLeg.1, &FltLeg.1 and &MySwap.1 in cells C4, C18 and H4 respectively.
Today's date is the May 22, 2019 and all USD spot swaps start accruing on May 24, 2019 due to the T+2 settlement convention.
I have set Start Date= 24/12/2019 and End Date= 24/12/2024 so that my swap represents a forward swap that starts in 7 months from today and has a tenor of 5 years. It is also a Payer swap that pays a fixed rate of 2.18%.
This timing is intentional so that the cash flows fall on dates that differ from the annual dates used in the swap part of the curve construction and their pricing is therefore sensitive on the interpolation assumptions.
I am now in a position where I can calculate the price, flat DV01 and 5Y DV01 of my swap under various interpolation assumptions.
Deriscope's powerful Show and Clone functions make it possible to set up the final table with only a minimal set of formulas, as shown in the screenshot below, where the cell D5 is selected so the formula with the Show function that returns the respective NPV is visible in the formula bar. Its only dependency is on the object of type Valuation in cell J5, which is produced by a Deriscope ds formula that references the three objects to its right.
Similar functions return the flat DV01 and 5Y DV01 in the adjacent cells to the right.
Below is the same table without the supporting formulas:
The top row corresponds to the single-curve valuation based on the &USD-Mkt.1 curve, which is built with Natural Cubic interpolation on zero yield.
The second row corresponds to the dual-curve valuation based on the discounting &USD-OIS.1 curve and forecasting &USD-3M.1 curve, which are both built with Natural Cubic interpolation on zero yield, same as in the single-curve case, so that the results of the top two rows reflect only the difference between single-curve and dual-curve valuation.
All remaining rows correspond to dual-curve valuation with the shown interpolation that applies on discount factor, zero yield or forward rate as indicated by the handle prefix in the first column.
The following three charts help visualize these numbers.
Click on IntRateSwapModelRisk.xlsx to download the spreadsheet produced with the above steps.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com