1. Home
  2. Home Loan Calculator

Home Loan Calculator

◷ Reading Time: 18 minutes

In this tutorial, we are building a sample solution in order to create a home loan borrowing calculator.

The application gets information about the applicant’s personal details, earnings, and expenses as the input and as the output, it shows how much the applicant can borrow. We can also check whether the applicant is an existing customer of the bank by reading the database.

Input –> Personal details, earnings, and expenses of the applicant
Process –> Give a score depending on different criteria and calculate the amount accordingly
Output –> Amount the applicant can borrow

In the project, we are creating the following files. Having separate files for each task makes it easier to manage the project and reuse the files as necessary.

Document TypeDocument Name
Fact Concept Concept.xml
Decision Table PersonalDetails.xml
Decision Table Expenses.xml
Generic Flow MainFlow.xml
Natural Language Earnings.xml

We can start by creating the Fact Concept, followed by the flow, decision tables, and natural language file. There is no specific order and you can edit as you go.

In this tutorial, we create the project by using the following steps.

  1. Create the customer database
  2. Create the fact concept
  3. Give a score depending on the applicant’s personal details, earnings, and expenses
  4. Calculate the amount that can be borrowed
  5. Show the final message

The project (Developer-Tutorial-FlexRule.zip) is attached at the end of the page. The sample JSON input files are in the folder sampleJSON and the database script is in the folder DatabaseScript.

Create the customer database

First, create the MSSQL database using the script in the project folder Developer-Tutorial-FlexRule\DatabaseScript\customer.sql

This will create the database customer and table customerDetails.

Create a Fact Concept

Concept.xml

  1. You can start the project by creating a fact concept document to model data. In this fact concept, we have mapped three objects.
{
    "PersonalDetails":
    {
        "ApplicationType":null,
        "NumberOfDependants":0,
        "PropertyType":null,
        "CreditScore":0.0,
        "AccountNumber":"",
        "Email":""
    },
    "Earnings":
    {
        "YourIncome":0.0,
        "YourOtherIncome":0.0,
        "SecondApplicantIncome":0.0,
        "SecondApplicantOtherIncome":0.0
    },
    "Expenses":
    {
        "LivingExpenses":0.0,
        "CurrentHomeLoanRepayments":0.0,
        "OtherLoanRepayments":0.0,
        "OtherCommitments":0.0,
        "TotalCreditCardLimits":0.0
    }
}

Therefore, in the Concept.xml file, we created three Facts for each object. This concept file will be used across the project.

Also, we added a validation to the email attribute. It matches the input email with the following Regex to ensure that the email is valid.

(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])
Adding a Regex match to validate the email

Create the Main Flow

MainFlow.xml

Validate input

The main flow connects all the documents in the project. Therefore, we will also define the input variables here.

  1. Create a Generic flow document.
  2. Add a Start node, End node, and Terminate node.
  3. From the Project Explorer window, drag and drop the previously created Fact Concept, Concept.xml document in between the two nodes and connect them to each other.
Drag and drop Concept.xml file to the flow
The flow with the validator to validate the email

4. Click on the Validator node and edit the properties.

Validator node properties
Facts Check window: The object with the email attribute which is PersonalDetails

5. Click on the transition that goes to the End node and add a condition

IsValid==true
Transition properties

4. Click on Properties and under the Properties window, Variable Definition, add the In variable PersonalDetails. This is where we connect the created Fact Concept file as an input.

Also, add a Local variable named IsValid to store the result of the validation.

Add variables in document properties
Assembly and Types can be selected from the drop-down list
Add local variable IsValid

5. Now, from MainFlow.xml, if you click debug , you will be asked to enter input data.

Debug with JSON Composer window

You can either go step by step or Stop debugging to see the output instantly. In the Parameter window, you will see the IsValid result.

You can add a name to the node by double-clicking on it.

Read database

Once the input is valid, we check the customer database to see whether the customer account number and email exist.

  1. Drag and drop a database node

Press the Alt button on the keyboard while clicking on the node label to move/ resize the label separately from the node.

2. Create a Local variable, IsaCustomer.

3. Click on the added database node and go to Properties to add Connection String and the query to check whether the applicant exists in the current customer database customer, table customerDetails.

Connection String and the Query

Connection String (if there are a username and password)

Data Source=<server_name>;Initial Catalog=<databse>;User ID=<username>;Password=<password>

Connection String (if there it is a trusted connection)

Data Source=<server_name>;Initial Catalog=<databse>;Trusted_Connection=True

Query:

SELECT 1 FROM customerDetails 
WHERE [accountNumber]=@accountNumber 
AND [email]=@email
Query to select data from the table. The results will be copied to the variable IsaCustomer.
Two parameters in queries, accountNumber and email

5. Now, save the project and click on Debug with JSON Composer icon .

Enter an AccountNumber and email that exists in the database.

Now click OK and also click on Stop Debugging icon . At this point, you will be able to see the loaded table under Parameter window.

IsaCustomer is 1 because the customer exists in the database.

Create a decision table to assign a score according to the applicant’s personal details

PersonalDetails.xml

We are now going to create a decision table to assign a score to personal information.

  1. Create a Decision Table document.
  2. Add a Local variable PersonaldetailScore. This is where we assign the score in this decision table. It should be an Integer type and the initial value is 0.

3. Build the decision table by adding columns (Conditions and Actions). The expression of each column is as follows.

NameExpressionColumn Type
Application typePersonalDetails.ApplicationTypeCondition
Number of dependantsPersonalDetails.NumberOfDependantsCondition
Property typePersonalDetails.PropertyTypeCondition
CreditScorePersonalDetails.CreditScoreCondition
Is a customer already?IsaCustomer !=nullCondition
PersonalDetail ScorePersonaldetailScoreAction

Altogether there are 16 rows, which means 16 rules. We add a score for each rule and in the end, the applicant will be given a score according to the personal details he/ she inputs.

For example, if the applicant:
– is single (add 20)
– has 1 dependant (add 30)
– buys the property to live in (add 20)
– has 100 Credit Score (add 0)
– is an existing customer (add 10)

the PersonaldetailScore is 80 (20 + 30 + 20 + 0 + 10).

This score will be used to calculate the amount the applicant can borrow.

4. Drag and drop this decision table to the MainFlow.xml.

Drag and drop the decision table to the main flow

5. Now, from MainFlow.xml, if you debug, you can enter more information on the input window.

If you go step by step , you will be able to see the matching rules in the decision table in Green, as shown below.

At the end of the execution, you can see the PersonaldetailScore.

Create a natural language file to assign a score according to the applicant’s earnings

Earnings.xml

  1. Create a Natural Language document.
  2. Add a Local variable EarningsScore. This is where we assign the score in this natural language document. It should be Integer type and the initial value is 0.
    Also, add another Local variable Earnings to connect the concept file. Its Assembly is Concept.xml and Type is Earnings.
Natural Language Code
when main rule set
@processAll true
yourIncome and
yourOtherIncome and
SecondApplicantIncome and
SecondApplicantOtherIncome
end
when yourIncome
@processAll true
yourIncome_r1 and
yourIncome_r2 and
yourIncome_r3 and
yourIncome_r4 and
yourIncome_r5
end
when yourOtherIncome
@processAll true
yourOtherIncome_r1 and yourOtherIncome_r2 and yourOtherIncome_r3 and yourOtherIncome_r4 and yourOtherIncome_r5
end
when SecondApplicantIncome
@processAll true
SecondApplicantIncome_r1 and SecondApplicantIncome_r2 and SecondApplicantIncome_r3 and SecondApplicantIncome_r4 and SecondApplicantIncome_r5
end
when SecondApplicantOtherIncome
@processAll true
SecondApplicantOtherIncome_r1 and
SecondApplicantOtherIncome_r2 and
SecondApplicantOtherIncome_r3 and
SecondApplicantOtherIncome_r4 and
SecondApplicantOtherIncome_r5
end
when yourIncome_r1
Earnings.YourIncome > 10000 and
Earnings.YourIncome <= 18000
then
EarningsScore+=200
end
when yourIncome_r2
Earnings.YourIncome > 18000
and Earnings.YourIncome <= 35000
then
EarningsScore+=250
end
when yourIncome_r3
Earnings.YourIncome > 35000
and Earnings.YourIncome <= 70000
then
EarningsScore+=300
end
when yourIncome_r4
Earnings.YourIncome > 70000
and Earnings.YourIncome <= 100000
then
EarningsScore+=350
end
when yourIncome_r5
Earnings.YourIncome > 100000
then
EarningsScore+=400
end
when yourOtherIncome_r1
Earnings.YourIncome > 500 and
Earnings.YourIncome <= 18000
then
EarningsScore+=50
end
when yourOtherIncome_r2
Earnings.YourOtherIncome > 18000
and Earnings.YourOtherIncome <= 35000
then
EarningsScore+=100
end
when yourOtherIncome_r3
Earnings.YourOtherIncome > 35000
and Earnings.YourOtherIncome <= 70000
then
EarningsScore+=150
end
when yourOtherIncome_r4
Earnings.YourOtherIncome > 70000
and Earnings.YourOtherIncome <= 100000
then
EarningsScore+=200
end
when yourOtherIncome_r5
Earnings.YourOtherIncome > 100000
then
EarningsScore+=250
end
when SecondApplicantIncome_r1
Earnings.SecondApplicantIncome > 500 and
Earnings.SecondApplicantIncome <= 18000
then
EarningsScore+=100
end
when SecondApplicantIncome_r2
Earnings.SecondApplicantIncome > 18000
and Earnings.SecondApplicantIncome <= 35000
then
EarningsScore+=150
end
when SecondApplicantIncome_r3
Earnings.SecondApplicantIncome > 35000
and Earnings.SecondApplicantIncome <= 70000
then
EarningsScore+=200
end
when SecondApplicantIncome_r4
Earnings.SecondApplicantIncome > 70000
and Earnings.SecondApplicantIncome <= 100000
then
EarningsScore+=250
end
when SecondApplicantIncome_r5
Earnings.SecondApplicantIncome > 100000
then
EarningsScore+=300
end
when SecondApplicantOtherIncome_r1
Earnings.SecondApplicantOtherIncome > 10000 and
Earnings.SecondApplicantOtherIncome <= 18000
then
EarningsScore+=50
end
when SecondApplicantOtherIncome_r2
Earnings.SecondApplicantOtherIncome > 18000
and Earnings.SecondApplicantOtherIncome <= 35000
then
EarningsScore+=100
end
when SecondApplicantOtherIncome_r3
Earnings.SecondApplicantOtherIncome > 35000
and Earnings.SecondApplicantOtherIncome <= 70000
then
EarningsScore+=150
end
when SecondApplicantOtherIncome_r4
Earnings.SecondApplicantOtherIncome > 70000
and Earnings.SecondApplicantOtherIncome <= 100000
then
EarningsScore+=200
end
when SecondApplicantOtherIncome_r5
Earnings.SecondApplicantOtherIncome > 100000
then
EarningsScore+=250
end

3. Drag and drop the natural language file to the main flow, MainFlow.xml.

4. In MainFlow.xml, add Earnings as an In variable to connect the concept file. Its Assembly is Concept.xml and Type is Earnings.

Earnings as an In variable in MainFlow.xml

Create a decision table to assign a score according to the applicant’s expenses

Expenses.xml

Now we are going to create a decision table to give a score to expenses. This is similar to the personal details decision table (PersonalDetails.xml).

  1. Create a Decision Table document.
  2. Add a Local variable ExpensesScore. This is where we assign the score in this decision table. It should be an Integer type and the initial value is 0.

3. Build the decision table by adding columns (Conditions and Actions). The expression of each column is as follows:

NameExpressionColumn Type
Living expensesExpenses.LivingExpensesCondition
Current home loan repaymentsExpenses.CurrentHomeLoanRepaymentsCondition
Other loan repaymentsExpenses.OtherLoanRepaymentsCondition
Other commitmentsExpenses.OtherCommitmentsCondition
Total credit card limitsExpenses.TotalCreditCardLimitsCondition
Expenses ScoreExpensesScoreAction

Altogether there are 25 rows, which means 25 rules. We add a score for each rule and at the end, the applicant will be given a score according to the expenses he/ she inputs.

This score will be used to calculate the amount the applicant can borrow.

Expenses.xml decision table

4. Drag and drop this decision table to the MainFlow.xml.

5. In MainFlow.xml, add Expenses as an In variable to connect the concept file. Its Assembly is Concept.xml and Type is Expenses.

6. Now, if you debug from MainFlow.xml, you will be asked to enter all of the inputs.

7. You can go step-by-step or stop the debug flow. You will then be able to see all three scores in the Parameter window.

Calculate the amount an applicant can borrow

MainFlow.xml

Now we have the determined scores according to the details the applicant has entered. As a result, we will finally calculate the amount that can be borrowed and show it as a message. This part is done in MainFlow.xml.

1. Add three more variables, as follows:

DirectionNameType
LocalTotalIncomedecimal
LocalAmountdecimal
OutFinalMessagestring
Three new variables: TotalIncome, Amount, and FinalMessage

2. First, we will calculate the total income by adding all of the earnings. To add the expression, we will add an Activity node

TotalIncome = Earnings.YourIncome + Earnings.YourOtherIncome + Earnings.SecondApplicantIncome + Earnings.SecondApplicantOtherIncome

3. Add another Activity node to calculate the amount

Amount = TotalIncome * (PersonaldetailScore|asDecimal()/100) + TotalIncome * (EarningsScore|asDecimal()/100) - TotalIncome * (ExpensesScore|asDecimal()/100)

This is only a sample function we used and may not be exactly the same function that is being used by the banks.

4. Add another Activity node to create the final message

FinalMessage= $"You could borrow up to {Amount}"

5. If you debug the project now, you will be able to see the output message in the Parameter window.

Updated on April 23, 2020

Article Attachments

Was this article helpful?