article
How to Build a Workbook to Value a Total Return Swap on a Floating Rate Loan
October 31, 2020

Overview

A Total Return Swap (TRS) is a bilateral financial contract where one counterparty pays out the total return of a specified asset, including any interest payment(s) and capital appreciation or depreciation, and in return receives a regular fixed or floating cash flow. Typical reference assets of total return swaps are corporate bonds, loans and equities. A total return swap can be settled at the terminating date or periodically, e.g., quarterly. For convenience we call the asset's total return a TR-leg and the fixed or floating cash flow a non-TR leg.

A Total Return Swap is a means to transfer the total economic exposure, including both market and credit risk, of the underlying asset. The payer of a total return swap can confidentially remove all the economic exposure of the asset without having to sell it. The receiver of a total return swap, on the other hand, can access the economic exposure of the asset without having to buy the asset. For example, a bank that keeps a large book of loans may want to reduce its economic exposure to some of its loans, but want to keep its relationship with the customers who have the loans. The bank can enter into a total return swap with a counterparty, e.g., a pension or a hedge fund that wants to gain economic exposure to the high yield loan market.

This article demonstrates how the Total Return Swap (TRS) workbook can be modified to value a TRS on a floating rate loan.

Note: This workbook assumes that interest rates, recovery rates and default events are independent of one another. The only credit risk considered in the workbook is the default risk of the floating rate loan, which is characterized by a default curve (either a par CDS spread or default probability curve).

The working process can be performed by:

  1. Embedding the aaRatefwd2 function in the existing TRS workbook to calculate the expected forward rates on each coupon date for the TR leg.
  2. Embedding the aaCredit_DfltProb_DSSprd3 function in the TRS workbook to calculate a default probability curve used in pricing the TRS, if the default curve is given as a par CDS spread curve.

To download the latest trial version of FINCAD Analytics, contact a FINCAD Representative.

Steps to be followed:

A. Calculating Forward Rates

  1. Open the Total Return Swap (Loan) Workbook via FINCAD XL → Workbooks (User data) → Credit Derivatives (Other) → Total Return Swap (Loan).
  2. Go to the Loan Tables tab, right click on column F, and select insert to insert another column - as shown in Figure 1 below.
    Figure 1
    Loan table
  3. Type "reset status" in cell F7.
  4. Copy and paste this formula in cell F8: =IF(B8="","",IF(B8>value_date,"implied",IF(C8Figure 2
    Loan table
  5. Copy the formula all the way down to row 497 by dragging the bottom right corner of cell F8 (click and hold the left button to drag).
  6. Copy and paste this formula in cell E8: =IF(B8="","",IF(B8>value_date,aaRatefwd2(B8,C8,df_curve,Calculator!$C$27,7,$D$3),IF(C8Figure 3
  7. Copy the formula all the way down to row 497 by dragging the bottom right corner of cell E8 (click and hold the left button to drag). The worksheet now should look like Figure 4 below.
    Figure 4
  8. Update the loan table. Enter the effective and terminating dates for each coupon period and the principal amount applicable to that period. The user can use aaDateGen3 to generate dates based on a fixed frequency.
  9. For the cell that has "enter rate", please supply the current active reset rate for the underlying floating-rate loan. Please note there should be only one cell that has "enter rate". Press shift+F9 to recalculate the sheet.
  10. Alternatively, if the floating rate loan does not amortize, in stead of using aaRatefwd2 and aaDateGen3 as above, the function aaFRN_cf can be used to calculate all the numbers that are needed for the loan table.
    Making the format consistent:
  11. Since rate and reset status columns are calculated values, the user should make the background color of the two columns light yellow: select the range from E8 to F497 and "Fill color" select "Light Yellow".
  12. Add border to the reset status column by selecting the range F7 to F497 and select "All Borders".
    See Figure 5 below.
    Figure 5

Pricing a TRS requires the user to input a default probability curve which can be built from par CDS spreads by using the aaCredit_DfltProb_DSSprd3 function. For simplification we choose to import the existing Default Probability Calculator workbook into the existing TRS workbook. This is needed only for users with par CDS spread curves but no default probability curves.

B. Importing a Default Probability Calculator Workbook

We will be importing the Default Probability Calculator into the Total Return Swap (Loan) workbook from Section A. Hence, keep this workbook open.

  1. Open the "Default Probability from Bonds" workbook via FINCAD XL → Workbooks (User data) →Credit Derivatives (Utilities) →Default Probability from Swap Spreads
  2. Press Alt + F11 to activate the "Visual Basic Editor" and make the FC_Switches worksheet visible as shown in Figure 6.
    Figure 6
  3. Repeat step 2 for the Total Return Swap (Loan) workbook (in Section A).
  4. Go to the FC_Switches tab in the Default Probability from Swap Spreads workbook and select the range A1 to A63 and copy (press ctrl + X) the whole range.
  5. Go to the FC_Switches tab of the Total Return Swap (Loan) workbook and select cell B6. Press ctrl + V to paste the copied range in step 4.
  6. Delete the "Curve", "Holidays", and "Graph" tabs in the Default Probability from Swap Spreads workbook.
  7. Revisit the "Visual Basic Editor" by pressing Alt + F11.
  8. Right click on the current project and insert module.
  9. Add the following subroutine in the module by copying and pasting the code below:
    Sub Remove_Names()
    Dim n As Name
    For Each n In ActiveWorkbook.Names
    If InStr(n.RefersTo, "#REF") <> 0 Then
    n.Delete
    End If
    Next
    End Sub
  10. Press Alt + F11 to go back to Excel and run the macro. Go to Tools Macros or press Alt + F8. Select Remove_Names in the list of macros. Click Run.
  11. Right click the "Probability Calculator" tab and select "Move or Copy". Choose the destination workbook (the Total Return Swap workbook) in the To Book dropdown list and Click OK.
    Figure 7
  12. Go to the "Probability Calculator" tab and go to Insert →Name →Define … and select "value_date" under "Names in workbook" and click on Delete to delete the name range.
  13. Type in =value_date in cell C6.
  14. When the user wishes to use the Default Probability Curve calculated in "Probability Calculator", simply copy the result and paste it under Default Probability Curve in "Default and Recovery" tab, starting from cell B6.

After completing the above-mentioned steps, the user can begin to enter the trade details. After updating the necessary inputs and recalculating the spreadsheet (by clicking the Calculate All button in Calculator tab), the results will be displayed under Swap Results, starting from cell G5.

Finally, the values displayed under the Bond Results section reflect the fair value of the underlying loan based on the supplied default probabilities, as in Figure 8 below.

Figure 8

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.