1. Introduction
The Loan Management System (or LMS) is a standalone application that runs alongside Sage Evolution ERP software. It is used to manage loans, both borrowing and lending. Loan information can be entered in the LMS application, after which LMS will calculate all the necessary transactions automatically (based on the loan details you chose).
2. Accessing the LMS database
In order for LMS to work we need two database; a Sage Evolution database and an LMS database. Let’s assume you already have a Sage Evolution database in place at this point. Secondly, make sure the LMS database is restored in preferably the same SQL Server instance as the Sage Evolution database.
When first opening the LMS application, you will see a screen like the one below.
Click on “Add LMS database”. Another window will popup.
First we will fill the LMS tab.
- Server name: Enter the server instance where the LMS database is located.
- Database name: Enter the LMS database name.
- SQL login: Enter the SQL username of the instance where the LMS database is located.
- SQL Password: Enter the SQL password of the instance where the LMS database is located.
- Repeat this procedure for the other two tabs but with the database specific information for that tab.
- When all the information is filled on all three tabs, click on “Test Connections”.
- If everything is okay, you will receive a message confirming that the connection strings are set up correctly. If not, please check the connection strings.
- Click “Save” to save the connection strings when they are correct. You will then see an image like the one below.
Now, for username we use “Administrator” and for password “abcdef”, unless specified otherwise. After this, to login you will have to select a database, in this case “LMS – DEMO DB” and then click on the “Login” button.
LMS overview
After logging in you will be directed to the LMS overview page, see image below. From here, we can access all the important information like the administration tab and the loans tab. General information is displayed on the bottom (see parts highlighted in red), like the user we logged in with, the LMS, Sage and Common database and the version of the application.
Administration
Users: We logged in with the user “Administrator” now, but we can add (or edit) additional users in this part of the menu.
User groups: User can be managed through user groups, which can be managed here.
Interest types: We can add and manage interest types here. You can set a rate (percentage) and a date from which the rate is active. You can store multiple rates here.
Party categories: Multiple party categories can be set up here like home parties, related parties and third parties.
Transaction codes: All the different types of transactions are managed here, like:
- Repayment on principal
- Repayment on interest
- Addition to principal
- Addition to interest
Currencies: All the currencies used in your Sage and LMS database can be set here, so they can be used for transacting within the LMS application. It’s as easy as adding a currency code and symbol!
Statuses: Loan statuses can be set here.
Loans
Loans: Access and manage all your loans here. This is also the place where we will add new loans or where loan reports can be created.
Batch processing:
Parties: All the parties that we work with are managed through this screen.
Documents: Copies of physical documents are stored here. You can view or download them at any time.
Linked loans:
3. Managing loans and related information
Loans
You can access the loans overview by simply clicking on the “Loans” button in the Loans menu. After clicking on it, LMS will present all the loans currently in the database. We will find three main points of interest here, which are described below.
- These are the tabs of the open windows in the application. We can use these to navigate through the application quickly; simply clicking on the tab will bring you to the corresponding screen. The tabs can be closed by clicking on the “x”.
- This is where you will find several functions such as “Add Loan”, “Edit Loan” and “Delete”. Besides that it’s also possible to draw reports using the “Reports” button, we can use the “Mass Import” function to import multiple loans into the LMS application and the “Revalue FX Rate” button lets you revaluate the foreign currency exchange rate of a loan. Just select a loan and click on one of the buttons to interact.
- On the loan lines itself, some general information is being displayed. This will give a quick and clear overview of all the loans in the application. Additional information is presented when you open a specific loan by double clicking on it. We will look into that in the next part of this manual. Here are some important columns of the loans overview:
- Bor L: Borrowing or lending. Checked: Lending, not checked: borrowing.
- Code: The code of the loan. LMS automatically generates this number when creating a new loan and this number cannot be edited.
- Name: Name of the loan which can be entered manually when adding a loan. It’s best practice to enter the party’s name here.
- External reference: The party’s reference code.
- Agreement date: Date on which the loan is agreed to by both parties.
- Outstanding principal: Amount of initial principal that is still to be paid.
- Outstanding interest: Amount of principal that is due to be paid.
- Interest code: The interest type chosen for this loan. Remember that the interest types are set up in the Administration part of the module.
- Currency code: The currency in which the loan is agreed upon.
When in the loan overview, you can double click on a specific loan to open that loan. It will display all the loan’s information, including the repayment schedule, transactions and loan documents like confirmation letters. See the image below for an overview of this screen with information about some of the important parts of the screen.
Loan Header
In the first part of the Loan Overview page, we will find the loan header. It contains the following general loan information:
Name: The name of the loan. We often use the name of the party here.
External reference: The party’s reference for this loan.
Agreement date: The agreement date represents the first day of the first period.
Maturity date: The date of the final payment of this loan.
Date of Direct Debit Mandate: Issue date of the Direct Debit Mandate
Loan type: Either lending or borrowing.
Loan status: Either active or inactive.
Party: The name of the party we are lending to or borrowing from.
Last calculated: The date on which the loan is last calculated.
Loan details
In the second part of this screen, we will find more detailed information about the loan. Let’s start by looking at the initial information. First, we’ll find the initial information:
Currency: The currency in which the loan has been set.
Interest type: This explains how we calculate interest. The most common interest types are fixed rate and floating rate. If you want to add more interest types, this can be done in the administration part of the menu (Administration à Interest types).
Daycount Convention: Determines how interest accrues over time.
Daycount end of the Month: When this is enabled, LMS calculated the exact number of days for each month by looking at the last day for that month, affecting the repayment schedule accordingly.
Compound: Here we specify whether or not we use compounding. If applied, we increase the value of assets due to earned interest on both the principal and accumulated interest.
First compound: The day on which we first calculate compound.
Principal: The initial loan amount, excluding interest.
Nominal interest: Interest rate before taking inflation into account.
Outstanding principal: Part of the principal that is still to be paid.
Outstanding interest: Part of the interest that is still to be paid.
Outstanding total: Outstanding principal + outstanding interest. The total amount still to be paid.
Closed: Determines whether or not the loan is active.
Secondly, we’ll find the amortization information:
Repayment type: Either linear or annuity. Determines the amount of principal and interest is repaid each period.
Repayment periods: The number of repayment periods for the entire duration of the loan.
Grace period: The number of periods we apply grace. When grace period is active, there is no due principal applicable, only interest.
First period end date: The day on which the first period ends.
Repayment: Either amortization, transactions or none.
Balloon payment: Repayment of the outstanding principal sum made at the end of a loan period.
Period interval: Either annual, semi-annual, quarterly or monthly. Determines the duration of one period.
Recalculate: Use this button to recalculate the repayment schedule. This is useful when changes have been made to the set up of the loan.
Besides the loan details, there are three other tabs in this part of the loan overview screen; Repayment schedule, transactions and linked loan.
Repayment schedule
When you have entered all the necessary information on the first tab (Loan details) you can click on the “Recalculate” button to calculate the repayment schedule. It will calculate the repayments needed on both principal and interest for the full duration of the loan. It will also take into account the grace period and balloon payment, if applicable. See below for an example of the repayment schedule.
The first highlighted part shows a number of buttons we use to interact with the repayment schedule. You can add payments and transactions here and it also holds an allocation button.
Select lines: Lets you select all or no lines. Besides that you can manually select each line by clicking on the tick box in front of the line.
Add transaction: Lets you add the following transactions to the repayment schedule: interest adjustment, principal adjustment, principal instalment adjustment, penalty adjustment.
Add payments: Lets you add payments. You will need to specify a payment date, total amount and a reference.
Change interest rate: Here you can change the interest rate and recalculate the repayment schedule.
Allocations: Using this button, you will be brought to the allocations screen. Here you can allocate payments (which you can add with the button we just discussed) to your principal or interest outstanding amounts.
Export: Simply lets you export the repayment schedule to Excel or PDF.
Transactions
The transactions screen gives you an overview of all the transactions of this loan (outstanding amount of both principal and interest, as well as payments). You can post transactions to the Evolution database by selecting the desired lines and clicking on the “Post transaction” button. See image below.
Documents
This tab lets you link loan related documents to LMS. First, you will need to upload the documents to LMS using the Documents function under Loans in the LMS overview screen. Simply click on “Add Document”, then “Upload file” to upload the desired file.
From there, navigate back to the loan you want to link the document to. Click on the “Link documents” button in the documents tab and upload your documents. From there you can view, edit, download or delete the file.
Linked Loan
-
Evolution
In the final part of the loan overview screen, we will find some Sage Evolution specific information. This is the place where we specify the accounts used for:
- Interest income / expense
- Accrued interest
- Principal account
