¶ Introduction
An interest rate represents the cost of capital for a borrower or the yield on investment for a saver, typically expressed as an annual percentage of the principal amount. Within the Treasury Management System, interest rates are the "engine" for financial accuracy. They are utilized across several critical modules:
- Interest Rate Tables: These are your "Market Data" feeds (e.g., SOFR, EURIBOR, or T-Bill rates) that the system pulls daily to update valuations.
- Interest Trade Agreements: The specific legal terms (spreads, day-count conventions like Actual/360) applied to a transaction.
- Fixed vs. Variable Setups: The logic that tells the system to either use a static rate or "reset" the rate periodically based on the tables above.
- Loan Calculations: The engine that uses the rate, principal, and time to generate your actual payment schedules or interest accrual journal entries.
¶ Navigation
¶ Specific Setup
¶ Step 1: Setup Exchanges
The market for interest rates is driven by global financial activity, where specific benchmarks serve as the standard for pricing. SOFR (the replacement for LIBOR), Bloomberg, and Reuters (Refinitiv) are primary sources or providers of these market rate indices. In a Treasury Management System (TMS), the numerous market rates you monitor are categorized by an Index, which acts as the "source code" for calculating interest on your financial instruments.
- In the navigation pane, go to: Modules > Treasury > Common > Exchange
- Click the New button
- Enter the Exchange ID and Description
- Enter the Web address
¶ Step 2: Interest rate setup
Specific setup for the Interest rate table can be done on the Interest rates FastTab in Treasury parameters
- In the navigation pane, go to: Modules > Treasury > Setup > Treasury parameters
- On the General tab, expand the Interest rates FastTab
- Set the toggle to Yes or No for the following fields:
- Enable state field
- State field mandatory (the state field is a lookup to the standard D365FO state field in the Address setup)
- Restrict access by user group. If the toggle is set to Yes, then the User group field will become available, and a User group can be selected.
¶ Step 2.1: Setup Interest rates
- Create lines on the Interest rate form for each interest rate that applies to the loan.
- The following fields must all be recorded:
- Exchange (market for rate received)
- Date
- Maturity period
- Actual rate quoted
These rates show how much money banks are willing to lend with a given maturity date
Interest rates can be configured to update all interest rate agreements automatically.
- In the navigation pane, go to: Modules > Treasury > Common > Interest rate
- Then click the New button
- In the Rate name field, select a Rate name
- Select a Maturity period
- Enter a Date
- Enter a Rate %
- Enter a Margin rate
- Click the Save button
To run a batch job that will instantly update the Interest rate agreements on Loans and Credit facilities, go to:
- Treasury > Common > Periodic > Rate auto-updates on Interest agreements
- Click
OK
¶
Step 3: Setup Maturity
- In the navigation pane, go to: Modules > Treasury > Setup > Interest rate > Maturity
- Click on New
- Complete fields for Maturity and Description
- Click the Save button
¶ Step 4: Setup Rate names
- In the navigation pane, go to: Modules > Treasury > Setup > Interest rate > Rate names
- Click on the New button to create a new Rate name
- Complete the Rate name description
- Select Maturity from a dropdown list
- Maturity description will automatically display when Maturity is selected
- Select the relevant Period type
- Select the relevant Country/region. This is used when the update job is run to fetch the correct country for the chosen Rate
¶ Step 5: Setup Interest rate groups
The Interest rate group page contains the Group leader and Group participants FastTabs.
- Group leader
- Only one group leader is allowed
- One Company (Legal entity) can be selected from the drop-down menu
- Group participants
- There is no limit to the number of group participants
- Any company can only be part of one Interest rate group
- In the navigation pane, go to Treasury > Setup > Interets rate > Interest rate group
- Type a Group ID
- Enter a Group Name
- In the Group Leader FastTab, select a Company from the drop-down menu
- Expand the Group participants FastTab and click on the Add button
- Select all relevant group participant companies from the drop-down menu
- Click the Save button
Only users with the Treasury administrator role can make changes to Interest rate groups
To proceed to the next stage, navigate further down this document and refer to the segment titled Update Interest rate groups participant rates.
¶ Step 6: Setup Interest rate import
Interest rates can be imported to automatically update all interest rate agreements.
- In the navigation pane, go to: Modules > Treasury > Setup > Interest rate > Interest rate import setup
- Click on the New button to create a new link for interest rates
- Select the Rate name and select the Interest rate import file column type
- Enter the Import file path
- Enter the Archive file path
- Enter the Interest rate URL path
- Save the record
¶ Step 7: Integration setup
Interest rate integration allows fetching interest rates from a URL. The following website, built for the Ninja API, will be used: https://www.api-ninjas.com/api/interestrate
- In the navigation pane, go to: Modules > Treasury > Setup > Interest rate > Integration setup
- Enter a Content type
- Enter a Description
- Enter a Name
- Enter the URL
URL is the URL where the data should be fetched.
For ninja it is from their website, it must end with "="
The country is dynamically added later - Interest fetch type (Integration type) can be Interest rate - Country , Interest rate - Name, or Swift code
- Method must be Get
- The Key is found on the API website. (For ninja it is from their website)
- Key value is given after registration on API website and allows for calls to be made.
- JSONProperty is the header of the sample JSON given by the API Website. (For ninja, it is from their website)
The mapping connects the destination F&O table to the JSON response received from the website. Ensure your configuration matches the screenshot above.
The Country and rate_pct fields are mandatory for downstream logic to function correctly.
After the Integration setup is complete, the Interest rates can be fetched by running the Fetch daily interest rates batch job
¶ Daily use
¶ Step 8: Interest agreements
Interest agreements (Trade agreements) can be used on Loans, Investments and Registers
- Interest rate
- Margin rate
- Total interest rate
- Total effective interest rate
- Interest rate group
Interest rate Calculations
Interest rate = P x R x T
P = Principal amount (the beginning balance)
R = Interest rate (usually per year, expressed as a decimal)
T = Number of time periods (generally one-year time periods)
¶ Step 9: Interest rate table
- To update the Interest rate table, go to Treasury > Common > Interest rate
- Enter the following information:
- Rate name
- Maturity
- Start Date
- Interest rate
- Margin rate
- Total interest (the sum of the Interest rate and Margin rate)
- Created date and time
- Interest rate key is a hidden display field, and is made up of the following:
- The first three digits of the Rate name
- The first three digits of Maturity
- The State
- The Start date
- Click the Save button
¶ Step 9.1: Warning messages
The following warning messages may appear when certain information is entered, but they will not prevent the record from being created:
- If an interest rate is created for a date earlier than the current date, the following message will appear:
Please note that the entered interest rate date precedes the current date.
- When a negative interest rate is entered, the following Warning will appear:
Please note that the entered interest rate has a negative value.
- When an interest rate is entered with more than two decimals, the following message will appear:
Please be aware that the entered interest rate exceeds two decimal places.
¶ Step 10: Interest rates and Loans
Interest agreements allow you to establish agreed-upon interest rates for a loan. Rates can be updated manually or automatically, and both fixed and variable interest rates are supported.
¶ Step 10.1: Creating Interest agreements
- In the navigation pane, go to Modules > Treasury > Loans > Loans and open an existing loan.
- When opening a loan, by default you will be taken to the Header form.
- Click on the Lines form
- Expand the Interest agreements FastTab
Complete the following fields:
- Rate linked: select the tick box if this rate is linked to the interest rate table. This is used by the batch job to automatically create new interest rate agreements (Common > Periodic > Rate auto-updates on Interest agreements)
- Rate name can be selected from drop down list as it was set up
- Maturity - choose between various options, such as 12 Months, 1 Week, 2 Weeks, 6 Months, Overnight, etc.
- Rate Auto-update tick box
- Interest rate is a mandatory field. This is the interest rate negotiated with the institution and should be entered as a percentage
- Margin rate (formerly known as the Bank spread) is the difference between the interest rate that a bank charges a borrower and the interest rate a bank pays a depositor. It is a percentage that tells someone how much money the bank earns versus how much it gives out
- Total interest rate
- Total effective interest rate
- Interest rate group can be either fixed or variable
- A fixed interest rate means that the agreed interest rate is applicable for the entire period of the loan
- A variable interest rate means that the interest rate will fluctuate according to market conditions. When creating a new Interest rate, Variable interest rate should be the default.
- If you select a variable interest rate, you will be able to add additional lines on the trade agreement.
- When working with variable interest rate, ensure you enter a new start date for each line, but keep the end date for all new lines on the trade agreement the same.
- Start date gets its value from the loan details header record. The first line of interest agreements start date is populated from the loan header.
- When adding additional interest rate lines, from the second line, the start dates can be filled in.
- The initial start date is populated from the Loan header
- End date: The loan date together with the loan payment terms determines the number of payments that will be made and subsequently the number of statement lines that will be created.
- The end date is part of the base data for the loan and is negotiated with the institution on creation of the first agreement. This means that the end date must be the same across all agreement versions.
- The end date is automatically populated from the Loan header
- Number of statement lines is a non-editable field that is calculated using the start to end dates and loan payment terms (interest calculation intervals)
- Interest payment terms: options to choose from is Monthly, Quarterly, Half yearly and Yearly
- Number of payments in loan (display field)
When a loan group has an interest rate linked, the interest rate will be populated to the Loan trade agreement.
The user will not be able to manualy edit this interest rate on the Loan trade agreement
¶ Step 10.2: Interest rates on Loan statements
Interest rates will display as a percentage on the Loan statement.
The Expanded loan statement cater for the following Interest rate fields:
- Interest rate (%)
- Monthly Interest rate
¶ Step 10.3: Loan groups and interest instalment calculations
- When a loan group has an interest rate linked, the interest rate will be populated to the Loan trade agreement.
The user will not be able to manually alter an interest rate on a loan trade agreement if an Interest rate it is tied to a loan group.
¶ Step 11: Rate auto-update
By executing a recurring batch operation, interest trade agreements that are tied to an interest rate can be automatically updated.
- To automatically assign the latest interest rates to Loans as per the Interest rate table, go to Common > Periodic > Rate auto-updates on Interest agreements
- Multi-Company selection: Use the Company selection control within the periodic job dialog to pick one or more legal entities for the processing run
- Batch Efficiency: Select multiple legal entities simultaneously to run rate updates across companies within a single execution batch
- Click the OK button to run the batch job
¶ Step 12: Update Interest rate groups participant rates
This job will update the interest rate table of all the participants with the interest rate table of the leader. Once run, this job will update all rates in the participating entities rate table.
- In the navigation pane, go to Treasury > Common > Periodic > Update Interest rate groups participant rates
- Running this job will update all the participant entities with the same interest rates as the Group leader
- In the dialogue page, select the Interest rate group
- Click on the OK button
Interest rates can be set up to update all interest rates across the respective legal entities, as configured on the Interest rate group page
¶ Step 13: Fetch daily interest rates
- To fetch the daily interest rates, go to: Treasury > Common > Periodic > Fetch daily interest rates
- Users can enter a rate name and choose to run this in the background
- This job has a parameter named Rate name
- When ran, it loops through all rate names matching chosen option and fetches interest rates for the setup country/region that was set up on the Rate name
- It then applies the logic below to populate the Interest rate form: (Treasury > Common > Interest rate)
¶ Step 13.1: Fetch daily interest rates logic
When ran, it loops through all rate names matching chosen option and fetches interest rates for the relevant country/region that was set up on the Rate name.
When the Fetch daily interets rates job is run:
- Fetch the interest rate for the from the API, using data as setup in Rate name form
- Create a new record in the Interest rate table using the response received
Values from Rate names
- Rate name = Rate name
- Maturity = Maturity
Values from integration response
- last_updated = Start date
- rate_pct = Interest rate
- central_bank = Central Bank (if provided)
Validation
- Look at the latest record in the table for the same combination of Rate name, Maturity
- If the rate of the identified record is equal to the rate returned by the API, do not create a new record
- If a new record could not be created, give a warning message indicating New record has not been created – there has been no change to the interest rate
¶ Reporting
¶ Step 14: Interest rates report
To extracted the Interest rates report, go to:
- Treasury > Loans > Enquiries and reports > Interest rates report
Complete the following parameters:
- Select a Loan group
- Enter a From date
- Enter a To date
- Select an Interest rate group
- Click OK
- A report on interest rates will appear and can be exported
