Derivatives Resources

"Mastering" Master Curves:

An essential skill for linking multiple workbooks to the same curve

The Need for a Master Curve

All FINCAD Analytics Suite for Excel workbook solutions that price and assess risk for interest rate instruments and derivatives contain one or more discount factor (DF) curves for discounting and accruing, which act as a main determinant of calculated statistics.

Clients who use more than one workbook solution must maintain numerous independent curve sheets, which can become cumbersome and inefficient when all instruments need to use the same set of curve data.

Several solutions are available to address the problem of having to replicate the DF curves into each pre-built workbook:

  1. You can enter curve data separately into each curve worksheet. This is not recommended as mistakes can easily be made and also due to the time required to perform the task.
  2. You can link all curve worksheets inputs to a single worksheet. While this eliminates most of the input errors that can occur from (1), this is not recommended because it is not optimal to have more than one copy of the same curve.
  3. You can create a master curve template and link all pricing applications to it. This is the preferred solution as it virtually eliminates input error, creates a central repository of market data, and potentially allows any user in the organization to use the same set of curves in pricing and assessing risk if the workbook is put on a central server or shared network drive.

Creating a Master Curve Workbook

Any one of the workbook solutions in FINCAD Analytics Suite for Excel can be used as a starting point for the master curve workbook, where the curve worksheets within the workbook will become the master curve template. However, it is recommended to have the master curve workbook contain the market data and curve generation functionality but not to contain any prices. Therefore, starting with a stand-alone curve workbook is preferred.

In FINCAD Analytics Suite for Excel, you can open a swap curve workbook with your choice of market data source:

  1. User Data
  2. Bloomberg Finance LP* Data
  3. FINCAD Market Data

Simply go to FINCAD > Workbooks > Curve Settings and select the desired swap curve type and open a workbook that contains a discount factor curve.

The stand-alone curve workbooks can be found in FINCAD > Workbooks > Curves (Interest Rates)

Let's work through an example which will provide the foundation for applying the methodology to other workbook solutions. We want to create a master interest rate curve workbook and link the vanilla interest rate swap workbook to it. Assume D:\ is our shared drive.

STEP 1 - Open and save a curve workbook

  1. Open the Swap Curve workbook template through the FINCAD menu (FINCAD > Workbooks > Curves (Interest Rate) > Swap Curve), and save it to the shared drive. You may wish to save the file named as Master Curve.xls. Do not close this workbook. This workbook has two discount factor curves: one base curve and one tenor-adjusted curve.
  2. Find the defined names that refer to the discount factor curves through the Insert > Name > Define menu. In this workbook, the named range df_curve refers to the base curve and the named range df_curve basis refers to the tenor-adjusted curve. You may click in the Refers to: area at the bottom of the Define Name window which will outline what range the defined name is referencing.

STEP 2 - Open a pricing application

  1. Open the Vanilla Interest Rate Swap Portfolio workbook FINCAD > Workbooks > Swaps
    FINCAD > Workbooks > Swaps
  2. Find the defined name that refers to the discount factor (DF) curve through the
    Insert > Name > Define menu. In this workbook it is also df_curve.
  3. Delete the Curve worksheet and the Curve - Tenor Adjusted worksheet from the pricing application (right-click on the tab name, select Delete).
  4. Save this workbook.

STEP 3 - Link the pricing application to the master curve

  1. Recall that the defined name of the DF curves in the master curve workbook are df_curve and df_curve_basis.
  2. In the pricing application, open the defined name that referred to the curve that was deleted from this workbook. First, we will update df_curve.
  3. Replace the formula in the Refers to: area of window to ='Master Curve.xls'!df_curve. The general form is: ='name_of_master_curve_sheet.xls'!defined_name_of_curve_in_master_curve_workbook
  4. Apply a similar change for df_curve_basis. df_curve_basis now refers to ='Master Curve.xls'!df_curve_basis.

STEP 4 - Link valuation date

  1. We recommend using the valuation date from the master curve sheet as the valuation date in pricing applications.
  2. b. Set the pricing application value date input = value date input on the master curve workbook.

Things to Consider

  • The master curve workbook must remain open when recalculating the pricing applications. This is because Excel cannot handle dynamic named ranges in a linked worksheet that is closed.
  • The defined names in the pricing application will automatically change when the master curve workbook is closed.
  • You may wish your master curve template to be Bloomberg Finance LP enabled.

Conclusion

The steps required to create the recommended master curve workbook include:

  1. Start with a stand-alone curve workbook
  2. Save this workbook to a network, or shared, drive; it will become the master curve workbook
  3. Update the DF curve named ranges in all pricing applications to refer to the DF curve named range of the master curve workbook
  4. Update the valuation date of all pricing applications to = valuation date of the master curve workbook

This article discussed the need for and creation of a master interest rate curve workbook. The ideas presented could be extended and applied to the creation of a master market data workbook which could contain all raw and calculated data used by an organization. The methodology presented can be used with any stand-alone pricing application that can work with a master curves workbook solution.

Disclaimer

Your use of the information in this article is at your own risk. The information in this article is provided on an "as is" basis and without any representation, obligation, or warranty from FINCAD of any kind, whether express or implied. We hope that such information will assist you, but it should not be used or relied upon as a substitute for your own independent research.

For more information or a customized demonstration of the software, contact a FINCAD Representative.

 

* Bloomberg is a trademark of Bloomberg Finance LP. FINCAD is not associated in any way with Bloomberg Finance LP.