Using Excel-like Formulas

If you frequently use the Portfolio page to price a particular strategy consisting of multiple instruments, it is useful to then save that strategy as a portfolio. This is because once you have saved a portfolio, you can then easily open it on a future date and use it to create a new portfolio—without having to redefine the component instruments each time and instrument settings.

However, if you do this it would be useful if you could also define certain values (such as the strikes and the notionals) using Excel-like formulas and relative shortcuts that would then also be saved with the strategy for future use.

Working with Excel-like Formulas in the Portfolio Page

In the Portfolio page when you create a strategy you can now define supported fields using Excel-like formulas.

What is a formula? A formula is an equation that performs calculations on values. It starts with an equal sign, "=", and can contain different elements, e.g., constants, operators, references to other cells (or fields) within the Portfolio page, etc. For example, the following formula instructs the system to calculate the fixed rate of Option 2 by taking the fixed rate of Option 2 and adding 0.5% to it:

2(fixedrate1)=1(fixedrate1)+0.5

For more information on how to actually define a formula, see Defining Formulas in the Portfolio Page.

For which fields can you define a formula? In SDX Interest Rates you can define formulas in the Portfolio page in numerical fields. The following numerical fields are supported— Strike, Fixed Rate, Spread and Notional.

However, you cannot add a formula to:

A Notional field if you have applied amortizing notionals to the underlying coupons.

Any field that has variable values assigned to its underlying coupons.

The formula functionality has the obvious advantage of giving you added flexibility in defining these fields. So now you can easily set the strike of Option 1 to the strike of Options 2 + 5, or set the notional of Option 6 to be twice the notional of Option 2. Being able to link between the fields (or cells) in the Portfolio page will save time for the Sales and Structuring teams.

However, this feature's major added value is that if you define a field's value to be calculated using a formula, the link to the formula (which is in effect its relative value) is kept even when you calculate the portfolio and its absolute value is displayed. This means that:

Whenever you calculate the portfolio, each formula field's value is automatically updated by the system using the relevant formula definition.

Updating a reference field's value automatically affects all fields whose formulas are based on that reference field's value. As soon as you edit the reference field's value, the value in each linked field is recalculated on the fly without you having to do anything (other than click outside the edited reference field).

When you save a portfolio (in any of the supported formats—Portfolio, template, structure template and structure), if you have defined formulas for one or more fields, each of these fields is always saved with both its formula and its absolute value. This gives you great flexibility when you next open the portfolio (see Opening Portfolios With Formulas in the Portfolio Page ). For example, you can open it with its formulas preserved (ready for use as a template) or you can open it with the absolute values that were saved when you last saved the portfolio. This is necessary for revaluing an existing portfolio as of today, when you want to use the current trade date and market data but you also need to preserve the fields' values as they were when you saved the portfolio.

In order to let you work efficiently with formulas, the Portfolio page has two modes in which you can work as seen in the image below.

Figure 1: Switching Between the Values & Formulas Mode in the Portfolio Page

The two modes are as follows:

Formulas Mode

This mode lets you define the values of supported fields as formulas.

For more information on actually defining formulas for the supported fields see Defining Formulas in the Portfolio Page.

In this mode, as seen in the image below, in any field for which you have defined a formula you see the formula definition; in addition, to make it clear that a formula has been defined, the field is highlighted in yellow. For all other fields in this mode, you see the actual values (if these were defined in the Values mode).

Values Mode

In this mode values are manually defined by the user or the value can result from the formula defined for this field (whether this was defined in the Formula mode or directly in the Values mode).

For more information on actually defining formulas for the supported fields see Defining Formulas in the Portfolio Page.

If the value results from a formula (i.e., it was not manually defined by the user), the field is highlighted in yellow (as seen in Figure 3) to ensure you know it came from a formula. You can then click in such a field to see the actual formula used. Once you have displayed the field's underlying formula, to return to the calculated value click outside the field itself.

In this mode you can still enter a value manually in a field even if it has a formula defined for it. However, note that in such case the system will then remove the formula from this field (it does of course display a warning first and check if you want to continue).

You can switch between these two modes freely by simply clicking the relevant mode button.

In both the Formulas and Values modes, when you click in a field for which a formula has been defined, in the Formula textbox you see the field name and the complete formula (as seen in Figure 4). You can also edit existing formulas via this textbox.

As soon as you make a change in the Formula textbox the formula in the field itself is updated as well. However, to save the change (and update the displayed value) you must click the <Enter> button on the keyboard.

Figure 4: The Formula Textbox

For more information on:

Defining formulas see Defining Formulas in the Portfolio Page.

Saving a portfolio with formulas (taking into account the new modes) see Saving Portfolios With Formulas From the Portfolio Page .

Opening portfolios (taking into account the new modes) see Opening Portfolios With Formulas in the Portfolio Page .

Defining Formulas in the Portfolio Page

What is a formula? A formula is an equation that performs calculations on values. It starts with an equal sign, “=”. For example, the following formula instructs the system to take the strike of Option 2 and add 5 to it:

=2(strike)+5

You can define formulas in either the Formulas mode or the Values mode.

A formula definition, which always begins with an equal sign, “=”, can contain any or all of the following elements:

Operators

An operator is a sign or symbol that specifies the type of calculation to perform within an expression. Currently the system supports arithmetic operators. These are used to perform basic mathematical operations such as addition, subtraction, or multiplication, and to combine numbers. They produce a numeric result. The system supports the +, -, *, ^ and / operators.
For example, you can enter =1(strike) - 2(Strike).

Constants

A constant is a value that is not calculated.

References

A formula can contain references to other fields in the same portfolio (e.g., to an 's deal parameters such as the Strike field or Notional field).

A reference identifies a field elsewhere in the portfolio and in effect tells the system where to look for the value(s) and/or data you want to use in the formula. In a single formula you can reference data contained in several other fields; similarly you can use a single field in several formulas.

You can link any of the supported fields to any other supported field. You do this by manually entering the reference using the relevant convention, or by simply clicking in the reference field.

If you reference a field by clicking in it, there is no need to specify the leg of the reference field—the system automatically takes the correct leg. In this case “1” refers to the left leg and “2” refers to the right leg, so =1(fixedRate1) means that the reference field is taken from Option 1’s left leg’s Fixed Rate field. If you reference a field manually and the trade has more than one leg, currently you need to specify the leg of the reference field.

It is important to note that in a formula each direct reference to another instrument or field in the portfolio is always maintained by the system in the form of an absolute reference. That is, once a formula contains a link to a reference field, that link is maintained even if the actual placement of the instrument within the portfolio changes.

So if you insert a new instrument or delete an existing one (something that will inevitably affect the numbering of the remaining instruments), all relevant formulas are automatically updated accordingly. So for example, for Option 3 you have a formula in the Strike field which says 3(strike)=2(strike). You then insert a new option prior to Option 2—and therefore the original Option 2 becomes Option 3 and the original Option 3 becomes Option 4. Because the system automatically updates any formulas as necessary (to keep them in the form of an absolute reference), the formula in the Strike field for the new Option 4 is updated to =3(strike), that is, the link with the relevant option is retained.

However, currently if you copy and paste an option which includes a formula based on a reference to another instrument, when you copy it the system copies each field which contains a formula as an absolute value and removes the formula.

When defining a formula you can refer to the various instruments in the portfolio in a number of ways including the following:

o1

opt1

1

These instances are not case sensitive.

You can refer to other fields using the following convention:

With a round bracket divider between the reference instrument and the reference cell, e.g., 1(strike) or o1(strike).

Once you have defined a formula in a field, to close the formula definition you must then click the <Enter> key.

Once you have closed the definition, the field is highlighted in yellow and, if you are in the Values mode, the system calculates the value on the fly and displays it.

Saving Portfolios With Formulas From the Portfolio Page

Once you have defined a portfolio with formulas, you can then save it in any of the supported formats—Portfolio, template, structure template and structure. This lets you revalue it at a later date and also use it as a template later if required.

It is important to note that if the portfolio includes fields with formulas, the system automatically saves each of these fields with both the formula and its calculated absolute value. In order to do that, you have to calculate the portfolio before you can save it.

This gives you great flexibility when you next open the portfolio (see Opening Portfolios With Formulas in the Portfolio Page ). For example, you can choose to open it with its formulas preserved (ready for use as a template) or you can choose to open it with the absolute values that were saved when you last saved the portfolio. This latter option is necessary for revaluing an existing portfolio as of today, when you want to use the current trade date and market data but you also need to preserve the fields' values as they were when you saved the portfolio.

Opening Portfolios With Formulas in the Portfolio Page

When you open a saved portfolio by default it is opened for the current trade date with the current market rates and with any formulas previously defined for it. Accordingly the system calculates the fields with formulas relative to the current trade data and current market rates.

However, when you select the portfolio in the Portfolio Management window you can always choose different open settings.

You can choose to open:

A portfolio using the trade date and the market rates in the system at the time the portfolio was saved.

You do this using the Open with Saved Market Rates checkbox as seen in Figure 5.

A portfolio without its formulas activated.

If you do this, for any field that had a formula defined, the formula is disabled and the field is instead populated with the absolute values at the time it was saved. This setting is essential if you want to revalue an existing portfolio as of today, i.e., using today's dates.

You do this using the Open With Formulas checkbox as seen in Figure 5.