Home Loan Calculator

◷ Reading Time: 21 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.

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.

Video Tutorial

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 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])+)\])

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.

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

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

IsValid==true

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.

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

5. Now, from Main.xml, if you click Debug with JSON Composer , you will be asked to enter input data.

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.

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 (if there are a username and password)

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

Connection String (if there 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

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 the Parameter window.

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 and an InOut variable PersonalDetails for the PersonalDetails fact we created in the Concept file. 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.

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.

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.

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 its Type is Earnings.

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:

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.

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:

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.

3. Add an activity with the following expression.

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

4. Complete the flow with an end node.

Download the project

Use the attachment at the end of the page to download the sample project.