Comprehensive Formula Builder Guide for Banking Professionals

Introduction and Scope

This guide provides detailed instructions for creating and managing formulas within the Banclo loan processing system. It has been designed for banking professionals who are familiar with spreadsheet applications but require guidance on the specific features and capabilities of the Formula Builder.

Target Audience

This documentation is intended for:

Loan officers and managers requiring custom calculation capabilities
Banking professionals responsible for configuring loan assessment criteria
Financial analysts implementing business rules within the system
Operations staff managing formula-based workflows

Accessing and Managing Formulas

The Formulas List View

The main formulas page (Settings → Formulas) displays all formulas in your system:

Key Information Displayed:

Name & Description: Formula title and purpose
Category: User (individual applicant) or Loan Request (entire application)
Variables: Count of variables defined in the formula
Visibility: Whether the formula is shown in the UI (Visible/Hidden)
Type: System (read-only, provided by Banclo) or Custom (bank-specific)

Important Notes:

System formulas cannot be edited but can be viewed for reference
Custom formulas can be modified to suit your bank's requirements
The edit button (pencil icon) allows access to the Formula Builder for custom formulas

Formula Fundamentals: Relationship to Spreadsheet Applications

Banking professionals familiar with spreadsheet formulas will find the Banclo Formula Builder follows similar principles, with enhancements specific to loan processing requirements.

Part 1: Core Concepts and Definitions

Definition of a Formula

A formula represents an automated calculation that processes data from loan applications to produce specific metrics or assessments. Similar to spreadsheet applications, formulas in Banclo commence with an equals sign (=) and support:

Standard arithmetic operations (addition, subtraction, multiplication, division)
Data references (income figures, applicant age, loan parameters)
Built-in functions (SUM, AVERAGE, financial calculations)
Conditional logic (IF statements and decision trees)

Formula Builder Interface Overview

The Formula Builder interface comprises four primary sections, each serving a distinct function in the formula creation process:

Visual Guide: The Four Tabs of Formula Builder

1. Details Tab - This is where you configure the basic properties of your formula:

Formula name (system identifier)
Display title (what users see)
Description for documentation
Category (User or Loan Request)
Display format (Currency, Percentage, Number)
Task association (links formula to specific document types)

2. Formula Editor Tab - The main workspace for writing formulas:

Search bar for finding functions, variables, and settings
Category filter dropdown
Quick access tabs for Task Fields, Variables, Formulas, Loan Fields, Bank Settings, and Functions
Formula expression editor starting with "="
Color-coded syntax highlighting for easy reading

3. Variables Tab - Manage dynamic variables within your formula:

Add new variables with custom names
Configure variants for each variable (different data sources)
Set default values and formatting options
Define simple values or complex formula expressions

4. Preview & Test Tab - Validate your formula before saving:

Real-time formula validation
Test with sample data
View calculation results
Error messages for debugging

Comparative Analysis: Spreadsheet vs Banclo Formula Syntax

Operation Required	Spreadsheet Syntax	Banclo Syntax
Basic addition	`=10 + 20`	`=10 + 20`
Cell/field reference	`=A1`	`={{loan.amount}}`
Function application	`=SUM(A1:A10)`	`=SUM({{$vars.income}}, {{$vars.bonus}})`
Conditional logic	`=IF(A1>1000, "High", "Low")`	`=IF({{loan.amount}}>1000, "High", "Low")`

Part 2: Creating Your Initial Formula - Detailed Procedure

This section demonstrates creating a practical formula based on actual system implementations.

Interactive Example: Understanding Basic Income Calculation

Basic Income Calculation

Learn how to sum monthly income components

Formula:

=SUM(B2:D2)

	A	B	C	D	E
1		Monthly Salary	Overtime	Bonus	Total
2	Income	3,000	500	200	=

Formula Breakdown

Step 1:Sum all incomeB2:D2= 3700

Annual Income Projection

Calculate annual income from monthly total

Formula:

=B2*12

	A	B	C	D	E
1		Monthly Total	Annual Income
2	Income	3,700	=

Formula Breakdown

Step 1:Multiply monthly by 123700*12= 44400

Real Example: Maximum Monthly Commitment Formula

Based on the bank's debt-to-income ratio settings, this formula calculates the maximum monthly loan commitment a client can afford.

Step 1: Configure Formula Metadata

Navigate to the "Details" tab
Complete the following fields:
- Formula Name: Enter "maxMonthlyCommitment" (note: spaces are not permitted)
- Display Title: Enter "Maximum Commitment"
- Description: Enter "Calculates maximum monthly payment based on DTSI ratio"
- Category: Select "User" (applies to individual applicants)
- Display Format: Select "Currency" (for proper monetary display)

Step 2: Compose the Formula Expression

Navigate to the "Formula Editor" tab
Enter the actual formula used in production:

=({{$formula:user.assets}}*({{bank.settings.dtsi}}/100)/12) - {{$formula:user.expenses}}

This formula:
- Takes the user's total assets (annual income)
- Multiplies by the bank's DTSI percentage

Interactive Example: Maximum Monthly Commitment Formula

Maximum Monthly Commitment Calculation

Banclo Formula Builder showing task fields and formula references

Formula:

=({{$formula:user.assets}}*({{bank.settings.dtsi}}/100)/12) - {{$formula:user.expenses}}

	A	B	C	D
1		Field	Value	Formula
2	Assets	Annual Income	=	{{$formula:user.assets}}
3	Liabilities	Monthly Expenses	=	{{$formula:user.expenses}}
4	Result	Max Monthly Commitment	=

Formula Breakdown

Step 1:Get assets from sheet=SUM(3500+200+150)*12= 46,200

Step 2:Get DTSI from settingsBankSettings!C2= 40

Step 3:Apply DTSI formula(46200*40/100)/12= 1,540

Step 4:Get liabilities from sheet=SUM(500+300+50)= 850

Step 5:Calculate max commitment1540-850= 690

This comprehensive example demonstrates the complete formula structure:

Sheet 1 - Main: Shows the Banclo formula with three key components:

Assets: References {{$formula:user.assets}} which calculates from Assets sheet
Liabilities: References {{$formula:user.expenses}} which calculates from Liabilities sheet
Result: The maximum monthly commitment calculation

Sheet 2 - BankSettings: Bank configuration parameters used by the main formula (DTSI ratio, etc.)

Sheet 3 - Assets: Income components that decide what to include from Task FS3:

GrossEmolument, Overtime, FringeBenefits
Each references specific fields from the Task FS3 sheet

Sheet 4 - TaskFS3: The actual payslip task data with all income fields

Sheet 5 - Liabilities: Expense components that reference Task Expenses

Sheet 6 - TaskExpenses: The actual expense task data with all monthly obligations

The formula flow: Task data → Assets/Liabilities selection → Main formula calculation using bank settings

Divides by 12 for monthly amount
Subtracts existing monthly expenses

Step 3: Reference Dependencies

This formula references:

{{$formula:user.assets}} - Another formula calculating total income
{{bank.settings.dtsi}} - Bank configuration for debt-to-income ratio (e.g., 40%)
{{$formula:user.expenses}} - Formula calculating existing monthly commitments

Step 4: Validate Formula Functionality

Navigate to the "Preview & Test" tab
Input test values:
- Assets formula result: €60,000
- Bank DTSI setting: 40
- Expenses formula result: €500
Expected result: €1,500.00 per month ((60000 * 0.40 / 12) - 500)

Part 3: Understanding Reference Types

Within the Banclo system, data references replace traditional spreadsheet cell references. The following reference types are available:

Part 4: Advanced Formula Construction - Real Production Examples

Example 1: Debt Service-to-Income Ratio (DTSI)

Objective: Calculate the percentage of income allocated to loan repayments

DTSI Calculation with Payslip Data

Calculate Debt Service-to-Income Ratio using payslip and loan information

Formula:

=({{loan.selectedPeriodSummary.repaymentAmount}}*12) / {{$formula:user.assets}}*100

	A	B	C	D
1		Field	Value	Task Reference
2	Basic Salary	Gross Emolument	3,500	{{$tasks:fs3.grossEmolument}}
3	Overtime	Overtime Pay	200	{{$tasks:fs3.overtime}}
4	Bonus	Monthly Bonus	150	{{$tasks:fs3.bonus}}
5	Deductions	Tax & Insurance	-350	{{$tasks:fs3.deductions}}
6
7	Total Net	Monthly Income	=
8	Annual Income	User Assets Formula	=	{{$formula:user.assets}}

Formula Breakdown

Step 1:Get loan repayment from loan data{{loan.selectedPeriodSummary.repaymentAmount}}= 1200

Step 2:Calculate annual payment1200*12= 14,400

Step 3:Get user assets formula{{$formula:user.assets}}= 42,000

Step 4:Calculate DTSI ratio14400/42000= 0.3429

Step 5:Convert to percentage0.3429*100= 34.29%

Business Logic:

Retrieves monthly repayment amount from loan period summary
Multiplies by 12 for annual commitment
Divides by total annual income (assets formula)
Returns as percentage for DTSI assessment

Example 2: Maximum Loan Amount Calculation

Objective: Determine maximum loan amount using present value formula with stress testing

Maximum Loan Amount Calculation

Determine maximum loan amount using present value formula with stress testing

Formula:

=FLOOR({{$formula:user.maxMonthlyCommitment}} * (((1+{{$formula:user.interestPerMonthWStress}}%) ^ {{$formula:user.availableMonths}})-1) / ({{$formula:user.interestPerMonthWStress}}% * ((1+{{$formula:user.interestPerMonthWStress}}%) ^ {{$formula:user.availableMonths}})), 500)

	A	B	C	D
1		Parameter	Value	Banclo Reference
2	Monthly Commitment	Max Monthly Payment	690	{{$formula:user.maxMonthlyCommitment}}
3	Interest Rate	Monthly Rate (Stressed)	0.54	{{$formula:user.interestPerMonthWStress}}
4	Term	Available Months	240	{{$formula:user.availableMonths}}
5
6	Loan Amount	Maximum Loan	=

Formula Breakdown

Step 1:Get max monthly commitment{{$formula:user.maxMonthlyCommitment}}= 690

Step 2:Get stressed interest rate{{$formula:user.interestPerMonthWStress}}= 0.5417%

Step 3:Get available months{{$formula:user.availableMonths}}= 240

Step 4:Calculate present value factor((1.005417^240)-1)/(0.005417*(1.005417^240))= 134.25

Step 5:Multiply by monthly payment690 × 134.25= 92,592.50

Step 6:Round down to nearest 500FLOOR(92592.50, 500)= 92,500

Business Logic:

Uses maximum monthly commitment as payment capacity
Applies stressed interest rate for conservative calculation
Considers available months until retirement
Rounds down to nearest €500 for clean loan amounts

Example 3: Risk Warning with Conditional Logic

Objective: Generate warnings when loan exceeds risk thresholds

Risk Warning with Conditional Logic

Generate warnings when loan exceeds risk thresholds

Formula:

=IF(({{$formula:user.dtsi}}*100) > {{bank.settings.dtsi}}, "Exceeds Risk Ratio", "-")

	A	B	C	D
1		Parameter	Value	Banclo Reference
2	DTSI	Current DTSI %	34.29	{{$formula:user.dtsi}}
3	Bank Limit	Max DTSI %	40	{{bank.settings.dtsi}}
4	Loan Amount	Requested Principal	180,000	{{loan.principal}}
5	Max Allowed	Maximum Loan Amount	92,500	{{$formula:user.maxLoanAmount}}
6
7	Warning 1	DTSI Check	=
8	Warning 2	Amount Check	=

Formula Breakdown

Step 1:Get user DTSI{{$formula:user.dtsi}}= 0.3429 (34.29%)

Step 2:Convert to percentage0.3429 * 100= 34.29

Step 3:Get bank threshold{{bank.settings.dtsi}}= 40

Step 4:Compare DTSI34.29 > 40= FALSE

Step 5:First warning resultIF(FALSE, "Exceeds Risk Ratio", "-")= "-"

Step 6:=

Step 7:Get loan principal{{loan.principal}}= 180,000

Step 8:Get max loan amount{{$formula:user.maxLoanAmount}}= 92,500

Step 9:Compare amounts180000 > 92500= TRUE

Step 10:Second warning resultIF(TRUE, "Exceeds Maximum Loan Amount", "-")= "Exceeds Maximum Loan Amount"

Business Logic:

First formula checks if DTSI exceeds bank's configured threshold
Second formula validates loan amount against calculated maximum
Returns specific warning messages or dash for no issues
Includes multiple scenarios showing different risk combinations

Example 4: Complex Task Field References

Objective: Calculate loan eligibility using multiple document task fields

Loan Eligibility with Task Documents

Complex formula referencing task fields from different document types

Formula:

=IF(AND(TaskFields!C2="Verified", TaskFields!C3="Approved", Calculations!C4<=Calculations!D4), "Eligible", "Not Eligible")

	A	B	C
1		Field	Status
2	Payslip	Employment Status	Verified
3	Bank Statement	Account Review	Approved
4	Credit Report	Credit Score	720
5	Property	Valuation Status	Complete
6
7	Comments	Notes	All docs received

Formula Breakdown

Step 1:Check employment verifiedTaskFields!C2='Verified'= TRUE

Step 2:Check account approvedTaskFields!C3='Approved'= TRUE

Step 3:Calculate available payment3500-800= 2700

Step 4:Check against limit2700<=1400= FALSE

Step 5:AND all conditionsTRUE AND TRUE AND FALSE= FALSE

Step 6:Return decisionIF(FALSE)= 'Not Eligible'

This example demonstrates how formulas can reference:

Task Fields: Data from specific document types (payslips, bank statements)
Multiple Sheets: Different data sources in separate tabs
Complex Conditions: AND/OR logic with multiple criteria
Cross-sheet References: Using Sheet!Cell notation like Excel

Part 5: Understanding Variables - How Banclo Selects and Combines Data

Variables in Banclo allow you to select which income components and expenses to include in your calculations. Think of them as checkboxes that determine what gets counted in your formulas.

How Variable Selection Works

When processing a loan application, Banclo presents income and expense options from your documents. You can select which items to include:

Income Component	Amount	Source Document	Selected	Included in Total
Gross Emolument	€3,500	Payslip (FS3)	✅ Yes	€3,500
Overtime	€200	Payslip (FS3)	✅ Yes	€200
Fringe Benefits	€150	Payslip (FS3)	❌ No	€0
Bonus	€300	Payslip (FS3)	❌ No	€0
Total Selected Income (used in formulas)				€3,700

Variable Lifecycle in Practice

Step 1: Document Processing When you upload documents (payslips, bank statements), Banclo extracts all possible income and expense items.

Step 2: Variable Registration Each extracted item becomes a selectable variable:

Variable Name	Description	Value Source	Default Selection
`{{$vars.grossEmolument}}`	Basic monthly salary	Task: FS3 Payslip	✅ Selected
`{{$vars.overtime}}`	Overtime payments	Task: FS3 Payslip	⚪ Optional
`{{$vars.fringeBenefits}}`	Additional benefits	Task: FS3 Payslip	⚪ Optional

Step 3: Selection and Calculation When formulas are evaluated, only selected variables are included:

Conditional Logic Example

Using IF statements for loan approval logic

Formula:

=IF(AND(B2>=18, C2>25000), 'Approved', 'Review Required')

	A	B	C	D
1		Age	Income	Decision
2	Applicant 1	25	30,000	=
3	Applicant 2	17	35,000	=
4	Applicant 3	30	20,000	=

Formula Breakdown

Step 1:Check age >= 1825 >= 18= TRUE

Step 2:Check income > 2500030000 > 25000= TRUE

Step 3:AND conditionTRUE AND TRUE= TRUE

Step 4:Return resultIF(TRUE)= 'Approved'

Real-World Example: Income Calculation

Consider a formula for calculating total assets:

{{$formula:user.assets}} = SUM(selected income variables) × 12

Scenario 1: Conservative Calculation

Component	Value	Selected	Reasoning
Gross Salary	€3,500	✅	Guaranteed income
Overtime	€200	❌	Not guaranteed
Bonus	€300	❌	Variable compensation
Monthly Total		€3,500
Annual Assets		€42,000

Scenario 2: Optimistic Calculation

Component	Value	Selected	Reasoning
Gross Salary	€3,500	✅	Guaranteed income
Overtime	€200	✅	Regular overtime
Bonus	€300	✅	Consistent bonus history
Monthly Total		€4,000
Annual Assets		€48,000

Variable Management for Multiple Applicants

Each applicant can have different selections:

Applicant	Income Type	Main Applicant Selection	Co-Applicant Selection
Income Sources	Employment Income	✅ €3,500	✅ €2,800
	Rental Income	❌ Not applicable	✅ €600
	Investment Returns	✅ €200	❌ Not selected
Total		€3,700	€3,400

Key Points to Remember

Variables are Flexible: You can select which income and expense items to include based on your lending criteria
Each Participant is Independent: Co-applicants can have different variable selections
Formulas Adapt Automatically: When you change selections, all dependent formulas recalculate
Audit Trail: The system tracks which variables were selected for each calculation

Task-Specific Variables

Variables tied to specific document types (tasks)
Only available when that document is processed
Example: Variables from payslip vs FS3 form

Formula Variations

Same formula can have multiple variations based on document source
System tracks which variation is active
Example: Income calculation from payslip vs tax return

Advanced Variable Patterns

1. Multi-Source Aggregation

javascript

// System can combine variables from multiple participants
{{$participants:user.assets}}  // Aggregates across all applicants

2. Cascading Variable Resolution

javascript

// Variables can reference other variables
Variable A: "=SUM({{$vars.baseIncome}})"
Variable B: "={{$vars.A}} * 12"  // References Variable A

3. Dynamic Variable Selection

UI allows toggling between variants
Real-time formula recalculation on selection change
Audit trail of which variants were used

Variable Best Practices

Naming Conventions:

Use camelCase: "monthlyIncome" not "monthly_income"
Be descriptive: "creditCardPayments" not "ccPay"
Group related variables: "income", "incomeOT", "incomeBonus"

Variant Organization:

Order variants from most to least conservative
Provide clear variant names for UI display
Include metadata for proper formatting

Performance Considerations:

Variables are evaluated once per calculation cycle
Cached results prevent redundant calculations
Complex variable formulas can impact performance

Part 5.5: Critical Understanding - The Task-Variable-Formula Relationship

The TaskRegistry Connection

Variables and formulas are intrinsically linked to the TaskRegistry system, which defines what documents can be processed in the loan application workflow.

How TaskRegistry Links Everything Together

1. Task Definition → Formula Association

Each formula in the database has an optional taskRegistryId that links it to a specific document type:

sql

-- Example from production database
Formula: "assets" (Income calculation)
TaskRegistryId: [FS3 Document]
Variables: Gross Emolument, Overtime, Fringe Benefits

Formula: "age" (Age calculation)
TaskRegistryId: [ID Document]
Variables: None (uses dateOfBirth directly)

2. Document Processing → Data Availability

When a document is processed:

Task is executed (e.g., "Payslips" upload)
AI/OCR extracts data fields
Data becomes available in taskResults
Formulas with matching taskRegistryId can access this data

3. How Documents Connect to Formulas

Each document type provides specific information that formulas can use:

Document Types and Their Data

Document Type	What It Provides	Example Formula Use
FS3	• Gross Income • Overtime • Benefits	Calculating total employment income
Payslips	• Net Salary • Gross Salary • Deductions	Verifying actual take-home pay
Profit & Loss	• Business Revenue • Expenses • Net Profit	Assessing self-employed income
Your Expenses	• Credit Card Debts • Loan Payments • Monthly Commitments	Calculating total monthly obligations

Smart Formula Selection

The system automatically uses the right formula based on available documents:

Example: Calculating Income

If You Have...	System Uses...	Result
FS3 Document	FS3 Income Formula	Uses gross income + overtime + benefits
Payslips	Payslip Income Formula	Uses net salary from payslips
Profit & Loss Statement	Self-Employed Formula	Uses business revenue - expenses

Multiple Document Sources

When multiple documents are available, the system can:

Use the most recent document
Allow manual selection between sources
Combine information from multiple documents

Example Scenario:

Applicant Has	System Options
Both FS3 and Payslips	• Use FS3 (official annual summary) • Use Payslips (recent actual income) • Agent selects most appropriate

Available Document Categories

Category	Common Documents	Purpose in Loan Assessment
Employment	• FS3 • Payslips • Employment Contract	Verify employment income and stability
Self-Employment	• Profit & Loss • Balance Sheet • Tax Returns	Assess business income and viability
Financial	• Bank Statements • Investment Reports • Rental Income	Complete financial picture
Identity	• ID Card • Passport • Residence Permit	Verify applicant identity
Expenses	• Credit Card Statements • Loan Agreements • Monthly Bills	Calculate debt obligations

Formula Flexibility

The system adapts formulas based on available documents:

Scenario	System Behavior
Single income source	Uses the available document type's formula
Multiple income sources	Presents options for agent to select most appropriate
Missing documents	Prompts for required documents or uses alternative calculations

Key Benefits

Automatic Adaptation: Formulas adjust to available documents
Multiple Paths: Same result achievable through different document combinations
Flexibility: Agents can select most appropriate data source
Consistency: Same calculation logic applied regardless of document source
Transparency: Clear visibility into which documents drive calculations

Debugging Task-Variable Issues

Common issues and solutions:

Problem: Variable shows as undefined Check: Is the associated task completed and data extracted?

Problem: Wrong formula variation selected Check: Verify formulaVariations[].selected index

Problem: Field not accessible in formula Check: Does the task's data structure include this field?

Problem: Formula not evaluating Check: Is the taskRegistryId matched with an executed task?

Part 6: Practical Formula Applications for Loan Assessment - Production Examples

Age Calculation from ID Document

Actual Formula:

=DATEDIF(DATEVALUE(LEFT("{{dateOfBirth}}",10)), TODAY(), "Y")

Business Logic:

Extracts date of birth from identification document
Calculates years between birth date and current date
Critical for retirement age and loan term calculations

Age Calculation from ID Document

Calculates applicant's current age from date of birth extracted from ID document

Formula:

=DATEDIF(DATEVALUE(LEFT("{{dateOfBirth}}",10)), TODAY(), "Y")

	A	B	C	D
1		Field	Value	Formula
2	Age	Current Age	=
3	Retirement	Years to Retirement	=
4	Max Term	Maximum Loan Term	=

Formula Breakdown

Step 1:Date of birth from IDDATE(1985,6,15)= 15-Jun-1985

Step 2:Get today's dateTODAY()= 16-Sep-2025

Step 3:Extract current yearYEAR(TODAY())= 2025

Step 4:Extract birth yearYEAR(DATE(1985,6,15))= 1985

Step 5:Calculate age2025 - 1985= 40

Total Monthly Debt Expenses

Actual Formula with Complex Variables:

=SUM({{$vars.allExpenses}})

Variable Configuration:

json

{
  "allExpenses": [
    {"name": "Credit Card Payments", 
     "value": "=IF({{hasCreditCards}}, {{creditCards.monthlyPayment}}, 0)"},
    {"name": "Personal Loan Payments", 
     "value": "=IF({{hasPersonalLoans}}, {{personalLoans.monthlyPayment}}, 0)"},
    {"name": "Home Loan Payment", 
     "value": "=IF({{hasHomeLoan}}, {{homeLoan.monthlyPayment}}, 0)"},
    {"name": "Overdraft Interest", 
     "value": "=IF({{hasOverdraft}}, {{overdraft.monthlyInterest}}, 0)"}
  ]
}

Business Logic:

Each expense type has conditional inclusion
Only includes expenses if client has that debt type
Aggregates all monthly debt obligations
Critical for DTSI calculations

Total Monthly Debt Expenses

Calculates total monthly debt obligations from various loan types

Formula:

=SUM({{$vars.allExpenses}})

	A	B	C	D
1		Field	Value	Formula
2	Total Expenses	Monthly Debt Total	=	{{$formula:user.expenses}}
3	Income Required	Min Income for DTSI	=	Based on 40% DTSI
4	Available Income	After Debt Service	=	Salary minus debts

Formula Breakdown

Step 1:Credit Card PaymentIF(TRUE, 250, 0)= €250

Step 2:Personal Loan PaymentIF(TRUE, 450, 0)= €450

Step 3:Home Loan PaymentIF(FALSE, 0, 0)= €0

Step 4:Overdraft InterestIF(TRUE, 75, 0)= €75

Step 5:Total Monthly Debt250 + 450 + 0 + 75= €775

Interest Rate with Stress Testing

Base Interest Formula:

=SUM({{$period:interestRate}}, IF({{$period:baseRate}},{{$period:baseRate}},0))/12

Stressed Interest Formula:

=SUM({{$formula:user.interestPerMonth}},({{bank.settings.stressBps}}/100/12))

Business Logic:

Base formula combines period interest rate with optional base rate
Stress formula adds bank's configured stress basis points
Divides by 12 for monthly rate
Used in maximum loan amount calculations to ensure conservative lending

Part 7: Common Implementation Errors and Resolutions

Error 1: Incomplete Reference Syntax

❌ Incorrect: =$vars.income

✅ Correct: ={{$vars.income}}

Error 2: Invalid Variable Naming Convention

❌ Incorrect: ={{$vars.monthly income}}
✅ Correct: ={{$vars.monthlyIncome}}

Error 3: Incorrect Reference Path Structure

❌ Incorrect: ={{payslip.salary}}
✅ Correct: ={{taskResults.payslip.salary}}

Error 4: Unhandled Division by Zero

❌ Incorrect: ={{loan.amount}} / {{$vars.months}}
✅ Correct: =IF({{$vars.months}} > 0, {{loan.amount}} / {{$vars.months}}, 0)

Part 8: Quick Reference Documentation

Formula Syntax Reference Table

Operation Type	Syntax Structure	Implementation Example
Arithmetic Operations	`+ - * /`	`=100 + 50`
Order of Operations	`( )`	`=(100 + 50) * 2`
Variable Reference	`{{$vars.name}}`	`={{$vars.salary}}`
Loan Field Access	`{{loan.field}}`	`={{loan.amount}}`
Task Result Reference	`{{taskResults.task.field}}`	`={{taskResults.payslip.net}}`
Formula Cross-Reference	`{{$formula:name}}`	`={{$formula:income}}`
Bank Configuration	`{{bank.setting}}`	`={{bank.interestRate}}`

Display Format Specifications

Format Type	Application	Display Example
None	Unformatted numerical or text values	`1234.56`
Number	Standard numerical formatting	`1,234.56`
Currency	Monetary values with currency symbol	`€1,234.56`
Percentage	Ratio and rate calculations	`35.50%`

Part 9: Advanced Implementation Techniques

Technique 1: Multi-Source Data Aggregation

={{taskResults.payslip1.net + taskResults.payslip2.net + taskResults.payslip3.net}} / 3

This formula calculates the average net income across three consecutive payslip periods.

Technique 2: Nested Conditional Evaluations

=IF({{$vars.creditScore}} >= 750, "Excellent",
  IF({{$vars.creditScore}} >= 700, "Good",
    IF({{$vars.creditScore}} >= 650, "Fair", "Poor")))

Technique 3: Modular Formula Architecture

Implement reusable formula components to maintain consistency:

Define base formula totalIncome containing comprehensive income calculations
Reference in dependent formulas: ={{$formula:totalIncome}} * 0.35

Technique 4: Enhanced Formula Readability

Employ descriptive variable naming conventions for improved maintainability:

Preferred: {{$vars.monthlyGrossIncome}}
Avoid: {{$vars.x}}
Preferred: {{$vars.debtServicePayments}}
Avoid: {{$vars.y}}

Part 10: Formula Category Classifications

User Formula Applications

Individual applicant financial metrics
Personal debt-to-income ratios
Age-based eligibility calculations
Individual creditworthiness assessments

Loan Request Formula Applications

Aggregate loan amount calculations
Combined applicant financial assessments
Property valuation metrics
Comprehensive risk scoring models

Troubleshooting and Diagnostics

Formula Diagnostic Procedures

Syntax Verification
- Confirm all references are enclosed in double curly braces
- Verify correct variable name spelling
- Ensure no spaces in variable identifiers
Incremental Testing Methodology
- Decompose complex formulas into components
- Validate each component independently
- Integrate components after individual verification
Error Code Reference

Error Code	Description	Resolution Method
#ERROR!	Syntax error in formula expression	Review bracket pairing and reference syntax
#DIV/0!	Division by zero encountered	Implement conditional logic to handle zero values
#NAME?	Unrecognized reference or function	Verify variable names and function spelling
#VALUE!	Type mismatch in operation	Ensure appropriate data types for operations

Additional Resources

Function Library: Consult the Built-in Functions Reference
Technical Documentation: Reference the Technical Formula Specifications
Loan Analysis: Review the Comprehensive Loan Analysis Documentation

This guide represents a comprehensive resource for banking professionals seeking to leverage the Formula Builder's capabilities for enhanced loan processing efficiency and accuracy.

Comprehensive Formula Builder Guide for Banking Professionals ​

Introduction and Scope ​

Target Audience ​

Accessing and Managing Formulas ​

The Formulas List View ​

Formula Fundamentals: Relationship to Spreadsheet Applications ​

Part 1: Core Concepts and Definitions ​

Definition of a Formula ​

Formula Builder Interface Overview ​

Visual Guide: The Four Tabs of Formula Builder ​

Comparative Analysis: Spreadsheet vs Banclo Formula Syntax ​

Part 2: Creating Your Initial Formula - Detailed Procedure ​

Interactive Example: Understanding Basic Income Calculation ​

Basic Income Calculation

Formula Breakdown

Annual Income Projection

Formula Breakdown

Real Example: Maximum Monthly Commitment Formula ​

Step 1: Configure Formula Metadata ​

Step 2: Compose the Formula Expression ​

Interactive Example: Maximum Monthly Commitment Formula ​

Maximum Monthly Commitment Calculation

Formula Breakdown

Step 3: Reference Dependencies ​

Step 4: Validate Formula Functionality ​

Part 3: Understanding Reference Types ​

Part 4: Advanced Formula Construction - Real Production Examples ​

Example 1: Debt Service-to-Income Ratio (DTSI) ​

DTSI Calculation with Payslip Data

Formula Breakdown

Example 2: Maximum Loan Amount Calculation ​

Maximum Loan Amount Calculation

Formula Breakdown

Example 3: Risk Warning with Conditional Logic ​

Risk Warning with Conditional Logic

Formula Breakdown

Example 4: Complex Task Field References ​

Loan Eligibility with Task Documents

Formula Breakdown

Part 5: Understanding Variables - How Banclo Selects and Combines Data ​

How Variable Selection Works ​

Variable Lifecycle in Practice ​

Conditional Logic Example

Formula Breakdown

Real-World Example: Income Calculation ​

Variable Management for Multiple Applicants ​

Key Points to Remember ​

Task-Specific Variables ​

Advanced Variable Patterns ​

Variable Best Practices ​

Part 5.5: Critical Understanding - The Task-Variable-Formula Relationship ​

The TaskRegistry Connection ​

How TaskRegistry Links Everything Together ​

Document Types and Their Data ​

Smart Formula Selection ​

Multiple Document Sources ​

Available Document Categories ​

Formula Flexibility ​

Key Benefits ​

Debugging Task-Variable Issues ​

Part 6: Practical Formula Applications for Loan Assessment - Production Examples ​

Age Calculation from ID Document ​

Age Calculation from ID Document

Formula Breakdown

Total Monthly Debt Expenses ​

Total Monthly Debt Expenses

Formula Breakdown

Interest Rate with Stress Testing ​

Part 7: Common Implementation Errors and Resolutions ​

Error 1: Incomplete Reference Syntax ​

Error 2: Invalid Variable Naming Convention ​

Error 3: Incorrect Reference Path Structure ​

Error 4: Unhandled Division by Zero ​

Part 8: Quick Reference Documentation ​

Formula Syntax Reference Table ​

Display Format Specifications ​

Part 9: Advanced Implementation Techniques ​

Technique 1: Multi-Source Data Aggregation ​

Technique 2: Nested Conditional Evaluations ​

Technique 3: Modular Formula Architecture ​

Comprehensive Formula Builder Guide for Banking Professionals

Introduction and Scope

Target Audience

Accessing and Managing Formulas

The Formulas List View

Formula Fundamentals: Relationship to Spreadsheet Applications

Part 1: Core Concepts and Definitions

Definition of a Formula

Formula Builder Interface Overview

Visual Guide: The Four Tabs of Formula Builder

Comparative Analysis: Spreadsheet vs Banclo Formula Syntax

Part 2: Creating Your Initial Formula - Detailed Procedure

Interactive Example: Understanding Basic Income Calculation

Real Example: Maximum Monthly Commitment Formula

Step 1: Configure Formula Metadata

Step 2: Compose the Formula Expression

Interactive Example: Maximum Monthly Commitment Formula

Step 3: Reference Dependencies

Step 4: Validate Formula Functionality

Part 3: Understanding Reference Types

Part 4: Advanced Formula Construction - Real Production Examples

Example 1: Debt Service-to-Income Ratio (DTSI)

Example 2: Maximum Loan Amount Calculation

Example 3: Risk Warning with Conditional Logic

Example 4: Complex Task Field References

Part 5: Understanding Variables - How Banclo Selects and Combines Data

How Variable Selection Works

Variable Lifecycle in Practice

Real-World Example: Income Calculation

Variable Management for Multiple Applicants

Key Points to Remember

Task-Specific Variables

Advanced Variable Patterns

Variable Best Practices

Part 5.5: Critical Understanding - The Task-Variable-Formula Relationship

The TaskRegistry Connection

How TaskRegistry Links Everything Together

Document Types and Their Data

Smart Formula Selection

Multiple Document Sources

Available Document Categories

Formula Flexibility

Key Benefits

Debugging Task-Variable Issues

Part 6: Practical Formula Applications for Loan Assessment - Production Examples

Age Calculation from ID Document

Total Monthly Debt Expenses

Interest Rate with Stress Testing

Part 7: Common Implementation Errors and Resolutions

Error 1: Incomplete Reference Syntax

Error 2: Invalid Variable Naming Convention

Error 3: Incorrect Reference Path Structure

Error 4: Unhandled Division by Zero

Part 8: Quick Reference Documentation

Formula Syntax Reference Table

Display Format Specifications

Part 9: Advanced Implementation Techniques

Technique 1: Multi-Source Data Aggregation

Technique 2: Nested Conditional Evaluations

Technique 3: Modular Formula Architecture

Technique 4: Enhanced Formula Readability

Part 10: Formula Category Classifications

User Formula Applications

Loan Request Formula Applications

Troubleshooting and Diagnostics

Formula Diagnostic Procedures

Additional Resources