Formulas are used for complex calculation of rates. They are particularly helpful for treasury transactions that have multiple floating rates, such as investments in Brazil. Additionally, they can be used to create complex rates for any internal calculations. Functionality for rate calculations has existed in SAP Treasury and Risk Management since release 4.7 of SAP Corporate Finance Management (CFM). The scenario that I write about is based on a system using SAP ERP Central Component (ECC) enhancement package 6. I use a scenario based on a formula that has an Intercontinental Exchange London Interbank Offered Rate (LIBOR) as well as a manual internally created rate.

The standard system setup for variable rates allows for the selection of one rate plus or minus a number. Formulas are used to allow for calculation of a combination of values needed to determine a rate. The formulas screen is available by opening an interest rate instrument by executing transaction code FTR_EDIT. Alternatively, select Accounting > Financial Supply Chain Management > Treasury and Risk Management > Transaction Manager > Money Market > Back Office > Edit Financial Transaction (Figure 1). Select the detail view for interest conditions by clicking the detail view interest conditions  icon.

Figure 1

Interest structure from the Edit Financial Transaction screen

To select an existing formula, click the Formula button (Figure 2).

Figure 2

Condition details interest rate instrument

The existing standard formulas appear in the screen shown in Figure 3.

Figure 3

Standard formulas

1. Create an initial formula
2. Add the components of the formula
3. Add the formula to make it visible within the money market functionality of Treasury and Risk Management

Step 1. Create an Initial Formula

To create an initial formula, execute transaction code SE16. In the screen that appears (Figure 4), click the create icon .

Figure 4

Create a new formula for Treasury

In the next screen (Figure 5), populate the following fields:

• Formula Ref: In this field you enter a custom identifier for the formula (e.g., Z001). Entries of up to four characters are possible.
• Formula: In this field enter the formula itself (e.g., Z1 + Z2).
• No. of variables: In this field, you enter the number of variables included in the formula (e.g., 02).

Figure 5

An example of a custom formula

The Form Routine and Module Pool field are not used for custom formulas, so leave them blank. Click the save icon (not shown) to save your data.

Next, add the entry to a transport. An entry can be manually added to a transport by going to Table Entry > Transport entries.

Step 2. Add the Components of the Formula

To add components of the formula, execute transaction code SE16. In the Table name field of the screen that displays (not shown), enter AT31 and click the display contents icon . Display all entries in the table as shown in Figure 6 by clicking the execute icon .

Figure 6

Create formula variable values

Click the create icon to create a new entry. In the next screen (Figure 7), enter the values created in the formula shown in Figure 5. In the Formula Ref. field, enter the formula reference that you entered in Figure 5 (e.g., Z001). In the Variable field, enter one of the variables from the formula you entered in Figure 5 (e.g., Z1). In the Percentage Rate field, enter a default percentage rate (e.g., 0.000000). After you click the save icon to save the entry for Z1, enter the values for Z2 by clicking the same create icon. Enter the values for Z2 and click the save icon.

Figure 7

An example of an entry for a formula variable

Click the save icon to save the completed entry. Create each individual entry as needed. Next, add the entries to a transport. Table entries can be manually added to a transport by going to Table Entry > Transport entries.

Step 3. Add the Formula to Make It Visible in Money Market 

Use table AT18 to add the formula to make it visible in the money market subcomponent of Transaction Manager in Treasury and Risk Management. This formula is then available when you create money market transactions via transaction codes FTR_CREATE or TM_51. Follow menu path Accounting > Financial Supply Chain Management > Treasury and Risk Management > Transaction Manager > Money Market. In the screen that appears (Figure 8), click the create icon to create a new entry.

Figure 8

Create the formula for use in Treasury transactions

Select the formula reference that you created in Figure 5 (Z001) and then click the green checkmark (Figure 9). Click the save icon.

Figure 9

Create a formula reference

Click the save icon to save the completed entry. Next, add the entry to a transport. An entry can be manually added to a transport by going to Table Entry > Transport entries.

Now that the formula has been created, it can used on Treasury instruments, such as interest rate instruments in money market. Let’s revisit the example I initially started in Figure 1. Enter the transaction by opening an interest rate instrument in transaction FTR_EDIT. Alternatively, select Accounting > Financial Supply Chain Management > Treasury and Risk Management > Transaction Manager > Money Market > Back Office > Edit Financial Transaction. Next, select the detail view for interest conditions icon and click the Formula button. After you click the Formula button, your new formula can be selected from the formula list (Figure 10).

Figure 10

Formula list

Next, add the variable values from the appropriate reference interest rates that have already been created. Click the new formula just created, Z001. A pop-up screen appears (Figure 11) in which you enter the appropriate variable interest rates to create the formula. A completed example is shown in Figure 11.

Figure 11

Add values to the formula

After you click the green checkmark, the screen in Figure 12 appears. Interest conditions are used to calculate the total amount of interest due on a transaction at a particular interval. Interest is typically either nominal or capitalized. Capitalized interest becomes part of the principal balance, while nominal interest is typically paid in cash. The update rules specify how often the interest is calculate (monthly or daily). The End of Interest period states the date to end the interest calculation. That date can either be included as part of the calculation, or excluded. The Due date is when the interest is paid. It may either be the same as the End of Interest period or later. After you create your interest conditions, click the back icon to go back to the transaction structure.

Figure 12

Interest condition for money market instrument

In the screen shown in Figure 13, click the Int. rate…. (Interest rate adjustment) tab. This tab shows you all the interest fixing dates when rates are reset. Here the individual variable rates of the formula are displayed. This screen display allows you to always see the individual values on their own and is standard functionality in the SAP system.

Figure 13

The Interest rate adjustments tab

For each interest adjustment, you see all the variable rates that require adjustment. Click the save icon (not shown) to save the transaction. While the transaction itself is created, there is an additional step that needs to be performed to set the values for the interest rates. This is called fixing the rates. Execute transaction code TJ05 to perform an automatic update to fix the rates.

In the screen that appears (Figure 14), enter data in the Company Code and Fixing Date fields. Select the All Transactions check box and then click the execute icon to set the reference rates.

Figure 14

Interest rate fixing for 01/01/2015

The system selects the already created rates and applies them to your Treasury transaction. The Automatic Rate/Price Adjustment logging screen displays (Figure 15).

Figure 15

Log from interest rate fixing

The logging function, shown in Figure 15, shows each individual reference rate that has been updated, along with the appropriate transaction reference information.

You can now look at your Treasury transaction to see the newly updated rates. Enter the transaction by opening an interest rate instrument in transaction FTR_EDIT. Alternatively, select Accounting > Financial Supply Chain Management > Treasury and Risk Management > Transaction Manager > Money Market > Back Office > Edit Financial Transaction. In Figure 16, the updated rates are displayed.

Figure 16

Updated reference interest rates

Finally, in the Cash flow tab for the transaction, you can see the total amount of the interest rate, similar to Figure 17.

Figure 17

Cash flow with total percentage rate shown

You have now successfully created your own custom formula, made it visible for entry on money market transactions in SAP Treasury and Risk Management, and updated the rates on your treasury transaction.