Create your Own Calculation Sheet

Create a new XLS file with Microsoft Excel. Make sure you installed the Valuation Plugin as described in Install the Plugin.

Import Files

To import a document file in Excel
  1. In your newly created XLS file, rename a worksheet to Document.
  2. From Financial Model Builder, copy the content of the IRS.json document file. The code is also listed in the Prepare the Files section of the Interest rate swap tutorial.
  3. On the Valuation ribbon, locate to Active Sheet section and click Import From ClipBoard.
Fig. 17: Importing a document from Clipboard to the active sheet.

Fig. 17: Importing a document from Clipboard to the active sheet.

To import a pricing data file in Excel
  1. In your newly created XLS file, rename a worksheet to Pricing Data.
  2. From Financial Model Builder, copy the content of the IRS.scn pricing data file. The code is also listed in the Prepare the Files section of the Interest rate swap tutorial.
  3. On the Valuation ribbon, locate to Active Sheet section and click Import From ClipBoard.
Fig. 18: Importing pricing data from Clipboard to the active sheet.

Fig. 18: Importing pricing data from Clipboard to the active sheet.

Perform Computation

The functions that you use to perform computations are implemented as Excel formulas. Note that the separator of the formulas’ arguments may vary according to your operating system’s regional settings. In the code samples provided in this section, the comma - , is used.

To perform a computation in Excel
  1. In the newly created XLS file, rename a worksheet: Computation.

  2. Add the input script as follows:
    • Enter the keyword script, in the cell: A1.
    • Enter the title tutorial_IRS, in the cell: A2.
  3. Add the input documents as follows:
    • Enter the keyword documents, in the cell B1.
    • Enter the formula =JsonArray(Document!A1), in cell B2.
      • The goal is to create an array containing your document from the previous section using the JsonArray() function. This allows you to price multiple documents in parallel. Each document will be assigned a unique ID, unless you provide a distinct dealStamp attribute for each document.
  4. Add the pricing data input as follows:
    • Enter the keyword pricingData, in the cell C1.
    • Enter the formula ='Pricing Data'!A1, in the cell C2.
      • For the pricing data you need to reference your scenarios and calculation dates stored in the Pricing Data tab created in the previous section. Remember that you can price multiple scenarios as well as multiple calculation dates in parallel – just make sure you have properly specified an array of calculation dates in the pricing data file dates section. For more details, see Pricing Data Formalism.
  5. Prepare the pricing request content as follows:
    • Enter the formula =JsonObject(B1:C1,B2:C2), in the cell D2.
      • To prepare the pricing request content to be sent, you need to create an object using the JsonObject() function. You must select the previous keywords documents and pricingData and associate them to corresponding references.
  6. Execute the pricing request as follows:
    • Enter the formula =FppExecuteScriptRequest(A2,D2,FPP_LIB), in the cell E2.
      • To compute the price, you need to execute a pricing request. In this example, we execute the script IRS using the Valuation Library and the FppExecuteScriptRequest() function. The function returns a JSON reference.
Fig. 19: Computing and visualizing the net present value of the interest rate swap.

Fig. 19: Computing and visualizing the net present value of the interest rate swap.

  1. Display the results of your computation as follows:
    • Enter the formula =FppGetResultValues(E2), in the cell F2.
      • The FppGetResultValues() function extracts the values from the response JSON and displays them as numbers.
      • Alternatively, select the result reference and click Open JSON in Editor from the Valuation Ribbon.
      • The net present value of the interest rate swap is the same as you computed in Interest Rate Swap Tutorial.
  2. (Optional) Change the pricing data values or deal attributes and recompute all, as follows:
    • Click Recompute All from the Valuation ribbon. The price of your interest rate swap is updated on-the-fly.
To troubleshoot your computation
  • Verify you properly configured the connection as described in Configure the Connection.
  • Verify that the input and the returned request are valid JSON references. To verify, click View JSON from the Valuation ribbon. If valid, the JSON reference is displayed as JSON-formatted text in your default text editor.
  • Refer to the sample Excel file provided at the beginning of this section.

The option Show of the wizard displays the results in a single-cell, in static mode - the cell is not updated when input or script content change or when connection is not valid anymore. By contrast, the option Extract displays the results in a single-cell, in dynamic mode.

You can also use the FppGetResultValues() function to display the results in the selected range, in dynamic mode.

Display the Results Live

To update your script file and recompute your Excel sheet
  1. Log into Financial Model Builder and open the Editor.
  2. Open the IRS script. You created this script in Interest rate swap tutorial of the
  3. Replace leg.notional with 2*leg.notional in the paying cashflow of the fixed leg.
Fig. 20: Editing the script by multiplying the notional.

Fig. 20: Editing the script by multiplying the notional.

  1. Run your new code. The updated net present value of your interest rate swap is displayed.
Fig. 21: New net present value of the interest rate swap in Financial Model Builder Editor.

Fig. 21: New net present value of the interest rate swap in Financial Model Builder Editor.

  1. Redeploy your library, as described in Deploy your Library. This step is needed, otherwise the udpated library is available only in the Financial Model Builder Editor.
  2. Open your XLS file and click Compute from the Valuation ribbon. The value of your interest rate swap is displayed, and it is computed with the latest version of your script. The new price equals the value from the Financial Model Builder Editor.