1. Home
  2. Sample Projects
  3. Home Loan Calculator

Home Loan Calculator

◷ Reading Time: 20 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 Main.xml
Natural Language Earnings.xml
DRD (Decision Requirement Diagram)Calculate Borrowing Amount.xml

We can start by creating the Fact Concept, followed by the flow, decision tables, natural language file, and DRD. 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 is attached at the end of the page. The database script is in the folder DatabaseScript.

Running the Project

1. Create the customer database.

2. Change the connection string according to your connection string.

3. Open the document, Main.xml.

4. Use a Logic Run Template to debug/ run the project.

You will see the output message in the Parameters window.

Create the customer database

First, create the MSSQL database using the script in the project folder Home-Loan-Calculator\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

Main.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 Main.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 Main.xml.

Drag and drop the decision table to the main flow

5. Now, from Main.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, Main.xml.

4. In Main.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 Main.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 Main.xml.

5. In Main.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 Main.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

Now we have the determined scores separately according to the details the applicant has entered. As a result, we will finally calculate the amount that can be borrowed. This will be the main decision we make. Therefore, we are building the decision hierarchy using a DRD.

In this step, we will be separating these decisions, moving them from the flow to the DRD.

1. Create a folder name Risk Score Calculation and move all the logic files.

Calculate Borrowing Amount.xml

1. Create a DRD document.

2. Add the variables, as follows:

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

2. Drag and drop the logic files

3. Drag and drop a decision element and name it “Calculate Risk Score”. This will be the main decision that we want to make using the logic files.

4. Create another decision to calculate the total income.

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

5. Add the final decision to calculate the burrowing 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.

Now we completed creating the decision hierarchy of the decision to calculate the borrowing amount.

Main.xml

1. Drag and drop the DRD to the flow.

2. Add a variable as follows.

DirectionNameType
OutFinalMessagestring

3. Add an activity with the following expression.

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

4.Complete the flow with an end node.

Updated on March 11, 2021

Article Attachments

Was this article helpful?

Related Articles