¶ Introduction
An interest rate tells you how high the cost of borrowing is, or high the rewards are for saving. So, if you're a borrower, the interest rate is the amount you are charged for borrowing money, shown as a percentage of the total amount of the loan.
Interest rates serve several functions within the Treasury management system, including but not limited to, interest trade agreements, interest rate tables, fixed and variable interest rates, interest rates and loans, and various others.
- 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)
- Interest rate = P x R x T
¶ Navigation
¶ Specific Setup
¶ Step 1: Setup Exchanges
The market for interest rate indices is referred to as an exchange. LIBOR, Bloomberg Foreign Exchange, and Reuter's Foreign Exchange are examples of common market rate indices. The numerous market rates that you monitor are categorized by an index.
- In the navigation pane, go to: Modules > Treasury > Common > Exchange
- Click the New button
- Enter the Exchange ID and Description
- Complete the Web address as well.
¶ 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 the
OK
button to run the batch job
¶
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 Description
- Select Maturity from a dropdown list and
- 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 consists of the following two FastTabs:
- Group leader
- Only one group leader is allowed. (Users can select a company/legal entity)
- 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
- Under the Group participants FastTab, click on the Add button
- Select all relevant group participant companies from the drop-down menu
- Click the Save button
Only the Treasury administrator role is allowed to make changes to the Interest rate group
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 of interest rates from url. The following website, that was built for Ninja API, will be used: https://www.api-ninjas.com/api/interestrate
- In the navigation pane, go to: Modules > Treasury > Setup > 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.
- Integration type must be "Interest rate"
- 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, allows for calls to be made.
- JSONProperty is the header of the sample JSON given by API Website. (For ninja it is from their website)
.png)
The mapping maps the F&O table where the data will go to the JSON text received from the website. Setup must match screenshot above. Country and rate_pct are mandatory for further logic to work.
After the Integration setup has been done, the Interest rates can be fetched by running the Fetch daily interest rates batch job
¶ Daily use
¶ Step 8: Interest (Trade) agreements
Interest agreements can be used on Loans, Investments and Registers
- Interest rate
- Margin rate
- Total interest rate
- Total effective interest rate
- Rate group
¶ Step 9: Interest rate table
- To update the Interest rate table, go to Treasury > Common > Interest rate
- The following information can be entered:
- 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 will appear when certain information is captured, but this will not prevent the record from being created:
- When an interest rate is created for a date older than the current date, the following message will display:
- ”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 for the establishment of interest rates agreed against the loan. It can be manually updated or automatically. Both fixed and variable interest rates are available.
¶ 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
- Now 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> Batch trade agreements creation–new interest rates)
- Rate name can be selected from drop down list as it was set up
- Maturity - choose between 12 months, 1 week, 2 weeks, 6 months or Overnight
- 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
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 Standard 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
- 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 setup to be used to update all interest rates on the respective Legal entities, a per set up on the Interest rate group page
Interest rates can run as a batch job, in addition to the existing option for immediate execution
¶ Step 13: Fetch daily interest rates
- To fetch the daily interest rates, go to: Treasury > Common > Periodic > Fetch daily interest rates
This job has a single parameter - 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 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
- An interest rates report can be extracted. Go to: Treasury > Loans > Enquiries and reports > Interest rates report
The following parameters can be completed:
- 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
