Mortgage Loan Calculator Using Input Widgets

Input widgets are a powerful feature in CRM Analytics (CRMA) that allow users to interact with dashboards and input data in real-time. The release of this feature has expanded the range of possibilities for scenario planning and data analysis within CRMA. One use-case that I have explored is building a mortgage repayment simulator using input widgets. The idea for this came about when I returned home to the UK from an exhausting Dreamforce trip to a lot of panic about the state of the economy, particularly in relation to mortgages and rising interest rates set by the Bank of England, and when the input widget functionality was released soon after, this made sense as a first use-case for investigation. In this blog post, I will walk you through the process of building a mortgage calculator, similar to the one you might find online, using input widgets in CRMA to help understand the impact of different interest rates on mortgage payments, which can be used to make informed decisions about financial planning. It goes without saying though that nothing in this post or the calculator itself should in any way be considered a substitute for sound professional financial advice.

First, it is important to note that mortgage terms and conditions can vary significantly between countries. For the purposes of this blog post, we will be working with a mortgage scenario that is typical in the UK. This scenario involves a 25-year mortgage for a house with a purchase price of £670,000, a 20% (£134,000) deposit, and an arrangement fee of £999. The mortgage has an initial fixed interest rate of 2.03% for the first 2 years, after which it will shift to the lender’s standard variable rate (SVR), which is currently 5.74%. The monthly repayment schedule for this mortgage is provided in a CSV file, which we will upload into CRMA to create our dataset. By using input widgets, we will be able to explore how different interest rates and other variables can impact the mortgage repayment schedule.

Now that we have talked about our use case, let’s start building the solution in CRM Analytics.

Create Repayment Schedule Dataset

Let’s start by clicking on Create > Dataset on the Home screen, and then upload the Mortgage Repayment Schedule csv. Keep the precision and scale as their default values, ensuring all columns are correctly formatted. This dataset has the following fields and field types:

• Date (date) – the date of each monthly payment
• Payment No (number) – the number for each monthly payment. In this scenario, there are 300 (12 months * 25 years) payments.
• Opening Balance (number) – the mortgage amount outstanding at the start of each payment period.
• Interest Rate (percentage) – the amount charged for borrowing, shown as a percentage of the total amount of the loan
• Repayment (currency) – the amount of each equal monthly payment
• Capital (currency) – the portion of the repayment that goes toward reducing the amount borrowed
• Interest (percentage) – the portion of the repayment that goes toward the interest
• Closing Balance (currency) – the mortgage amount outstanding at the end of each payment period. This amount is equal to the following month’s Opening Balance

Note: Out of the columns mentioned above, we’ll use only ‘Payment No’ column in our calculations. The rest of the columns are meant for comparing our result with the expected result.

Upon exploring the dataset, you’ll see that the values are static, so we will need to calculate each of the fields above in order to pass in values from the input widget(s). For this, we’ll need to build a dashboard in CRMA, write down our query and do the necessary bindings to get a dynamic Mortgage Loan Calculator dashboard. This is what we’ll do in the next section.

Create the Mortgage Loan Calculator dashboard

My objective behind this blog is to keep it informative and easy to follow so that you can create this solution in your own org. To make this task easier, I have broken down the rest of the blog into steps.

Step 1 – Explore and save Payment Months

Let’s create a blank dashboard and save it in the desired app. In your dashboard, drag a table widget onto the canvas, and query the dataset we’ve just created by going into Table Mode, selecting Compare Table, and grouping by Date and Year-Month.

Step 2 – Create static columns for interest rates

At the end of this section, we’d have created 4 interest columns with static values and would have renamed the Sum of Payment No column. The end result would be like this –

Below are the steps I have taken to get to the stage above.

Step 2.1 – Calculate Payment No

Let’s change the first column to show the sum of Payment No.

Step 2.2 – Calculate Fixed Interest rate

The next column we’ll create is for the fixed interest rate. To do this, click on the ▼ next to Sum of Payment No and select Clone Column. Click ▼ again, but this time now select Edit Column, and this will then take you to the editor for this column. Here, enter a field name in the Column Alias field and a field label in Column Label. I’ll be using fixed_interest_rate and Fixed Interest Rate respectively. This is the format I will be using for all my calculated fields, and of course, you are free to select your own, but just remember to make the necessary changes when copying code from here to use in your examples. For this column, we’ll simply be using the value 0.0203, which is the fixed interest rate expressed as a decimal. Click Apply, and you should see this value returned for all rows.

Step 2.3 – Calculate Variable Interest rate

Next, click on the + sign to the right of “EDITING COLUMN” at the top of the editor, and repeat this process for the variable interest rate, which will be expressed as 0.0574. Although we are hard-coding these values now, we will change these to dynamic values later using the input widgets. 

Before moving on to the next field, let’s rename the Sum of Payment No field. I am using payment_no and Payment number as my alias and label.

Note: I have used the custom number format ###.#### for interest rates. This is what I’ll be using for the rest of the columns too.

Step 2.4 – Calculate the overall interest rate per annum

Remember that the interest rate is fixed for 2 years before changing to the variable, so we’ll need to use a case statement to make this calculation. The case statement we’ll need is –

case when 'payment_no' <= 24 then 'fixed_interest_rate' else 'variable_interest_rate' end

You’ll notice that this will then return the fixed rate up until the 24^th payment, and then the variable after.

Step 2.5 – Calculate the Monthly Interest Rate

Next, we’ll calculate the monthly interest rate, as this value will be used in subsequent calculations, and this is simply ‘interest_rate’ / 12. After this, hit Close, and your table should look like the one at start of the section.

If you’re as – let’s call it – “perfectionist” as I can sometimes be, you’ll probably want to change the format of the percentage fields to display them as percentages rather than decimals. You can do this by clicking on the ▼ and then selecting Percent under Format Numbers. As we will only display the interest Rate field in the final table, feel free to hide the other created fields by clicking the ▼ and then ‘Hide’. 

Step 3 – Calculate the Repayment Amount

Create one field each for purchase_price, deposit, and arrangement_fee, using the static values of 670000, 134000, and 999. After these, we also want to create a field called start to calculate the amount borrowed and use below mentioned formula as the calculation for this field. Again, feel free to hide these fields as they will not be used in the final table.

'purchase_price' - 'deposit' + 'arrangement_fee'

Next, we’ll be calculating the repayment amount, and the formula for this calculation is pv * i / (1 – (1 + i)^-n ) where pv is the opening balance, i is the monthly interest rate, and n is the number of months. Let’s convert this into syntax that CRMA understands, and use 

('start' * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -300))

Step 4 – Calculate Opening Balance, Interest Payable, and Capital

In this section, we’ll create calculated columns. I have also mentioned mathematical formulas used to create the formula in CRM Analytics compare table. Let’s create them one by one.

Step 4.1 – Calculate Opening Balance

Opening Balance is the remainder owed at the beginning of each payment period. The formula for the present value of a loan in month x is (pmt/r) x (1 – (1/(1+r)ⁿ) where pmt is the repayment, r is the monthly interest rate, and n is the number of months remaining. Again let’s convert this into CRMA syntax and use

('repayment'/'monthly_interest_rate') * (1 - (1/(power((1 + 'monthly_interest_rate'), 301 - payment_no))))

The opening balance value for the first row should be our total mortgage amount of £536,999, and the subsequent values will gradually reduce to zero over the remaining rows. At this point, your table should be looking as follows –

It’s important to note that the values we are calculating using SAQL are approximations only and may not exactly match the values in the original mortgage repayment schedule dataset that we uploaded. This is due to several factors, including rounding differences that can become more significant over time, as well as differences in the way financial institutions apply their calculations. For instance, some lenders may have slightly different policies for how they apply compound interest or may adjust the size of the initial and final payments in the mortgage schedule. In our calculations, we are assuming that the mortgage payments are equal each month, which may not be the case in all scenarios. Despite these differences, the values we are calculating are close enough, and can still help us understand the impact of different interest rates and other variables on mortgage repayments.

Step 4.2 – Calculate Interest Payable

Calculate the interest payable with the formula –

'opening_balance' * 'monthly_interest_rate'

Step 4.3 – Calculate Capital

Calculate the capital field, using the formula –

'repayment' - 'interest'

Step 4.4 – Calculate Closing Balance

The last field we’ll create is for the closing balance, using the formula –

'opening_balance' - 'capital'

Update and confirm. Your table should now look something like this –

Step 4.5 – Visualise and compare the closing balance

Now let’s visualise our calculated closing balance against the quoted one in the original dataset. To do this, drag a chart widget onto the canvas, and drag the query we’ve just created onto the widget. Double-click to edit, and change the chart-type to Timeline. Hide all the columns we created except for the Closing Balance, and then add another column for the sum of Closing Balance in the original dataset. Use the Date column in Time Axis, and group your data by Year-Month.

Step 5 – Verify data, and fix discrepancies(if any)

If created correctly up to this point, you’ll notice that a larger discrepancy between the two closing balances starts to occur from Month 25 (December 2024 in this example) onward. The reason for this is that we have not yet calculated the repayment amount from when the interest rate goes from fixed to variable.

To fix this discrepancy, we will change our repayment formula to –

case when payment_no <= 24 then ('start' * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -300)) else  ((('start' * (fixed_interest_rate/12)) / (1 - power(1 + (fixed_interest_rate/12), -300))/('fixed_interest_rate'/12)) * (1 - (1/(power((1 + ('fixed_interest_rate'/12)), 301 - 25)))) * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -276)) end

In the paragraphs below, I have explained how I reached this formula. 

Remember that the repayment formula uses the original amount spread out across the duration of the loan. So let’s think of it as a new loan starting after the fixed term, but this time over 23 years (276 months) instead of 25. So we now need to modify our repayment calculation using a case statement to recalculate after month 24. The first thing we need is the closing balance at month 24 (which is the opening balance at month 25). Now, we could just hard-code this using the value from our table, but obviously, we want to calculate it so that it still works when we start using dynamic values.

To begin with let’s re-write our repayment column’s formula to use the original calculation up to month 24, and then a new one after. So let’s change it to –

case when payment_no <= 24 then ('start' * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -300)) else ('start' * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -300)) end

We now want to change the latter part of this statement, after else. To do this, the first thing is to change -300 to -276, which is the new number of months remaining at the point the interest rate changes. That’s the easy part, and now we need to replace the ‘start’ portion of the calculation with the opening balance amount from week 25. To do this, let’s copy the calculation we’ve used to create the opening balance:

('repayment'/'monthly_interest_rate') * (1 - (1/(power((1 + 'monthly_interest_rate'), 301 - payment_no))))

and let’s change it to

('repayment'/'monthly_interest_rate') * (1 - (1/(power((1 + 'monthly_interest_rate'), 301 - 25))))

Next, as the calculation references the repayment field, and field can’t reference themselves, we’ll need to replace the ‘repayment’ part of this with the actual calculation. To do that let’s first copy the original calculation for repayment

('start' * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -300))

and then replace the ‘monthly_interest_rate’ part with (‘fixed_interest_rate’ / 12). This is because the balance up to that point has to be calculated using the old interest rate rather than the new one. So we now have 

('start' * ('fixed_interest_rate' / 12)) / (1 - power(1 + ('fixed_interest_rate' / 12), -300))

Let’s take that now and substitute it for the ‘start’ section of the opening balance calculation. You will need to add another set of brackets around it first, to ensure it is calculated correctly when substituted in. So –

('repayment'/'monthly_interest_rate') * (1 - (1/(power((1 + 'monthly_interest_rate'), 301 - 25)))) 

now becomes 

((('start' * ('fixed_interest_rate' / 12)) / (1 - power(1 + ('fixed_interest_rate' / 12), -300)))/ ('fixed_interest_rate'/12)) * (1 - (1/(power((1 + ('fixed_interest_rate'/12)), 301 - 25))))

And this then needs to be substituted back into the modified repayment statement to replace the ‘start’ portion. Again, you’ll need to add another set of brackets so that 

case when payment_no <= 24 then ('start' * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -300)) else ('start' * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -300)) end 

becomes

case when payment_no <= 24 then ('start' * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -300)) else  ((('start' * (fixed_interest_rate/12)) / (1 - power(1 + (fixed_interest_rate/12), -300))/('fixed_interest_rate'/12)) * (1 - (1/(power((1 + ('fixed_interest_rate'/12)), 301 - 25)))) * 'monthly_interest_rate') / (1 - power(1 + 'monthly_interest_rate', -276)) end

You’ll now see that the new repayment from Month 25 has changed from £3,375.05 to £3,288.76. This is still slightly different from the repayment amount in the quoted dataset though, due to the same rounding discrepancies referenced earlier.

We now have a completed set of calculated values with which we can explore using the input widgets.

Step 6 – Display for fixed rate repayment amount and variable rate repayment amount in number widgets

Let’s add two number widgets to our dashboard to display both the fixed-rate and variable-rate repayments. The repayment amounts are already calculated and stored in ‘repayment’ column of our first query. As per our use case, the fixed rate repayment amount is for 1 to 24 months, and after that is the variable rate repayment amount. We’re going to pass value of the 1st and 25th repayment amount through result binding to display our fixed rate and variable rate repayment amount respectively.

Step 6.1 – Create dummy derived measure

Let us do a quick count of rows query and call it fixed_rate_display –

--saql for the fixed_rate_display query

q = load "Mortgage_Repayment_Schedule_1";
q = group q by all;
q = foreach q generate count() as 'count';
q = limit q 2000;

Just like this, create another query or clone the previous one and call it variable_rate_display. In the next section, we’ll add our bindings.

Step 6.2 – Result binding

Let’s perform result binding for fixed rate repayment amount first. Go to the ‘fixed_rate_display’ query and replace count() as ‘count’ with the result binding. My result binding and measure projection look something like this –

{{cell(LoanCalculator_1.result, 0, \"repayment\").asString()}} as 'fixed_rate_amount'

Note: Remove the column map from the visualization parameter. While in ‘Advanced Interaction Editor’ mode, switch to the widget section, and change the value of “measureField” to the name of your derived measure. It’s ‘fixed_rate_amount’ in my case. If you do not see “measureField” under widget properties, add it to the json.

You can also simply drag the LoanCalculator query into a number widget, and change the “measure” to ‘repayment’ in widget properties, and you’ll see the same result. This is because, by default, the number widget displays the value available in the very first row(0th row) for a given column.

Repeat the same step for variable_rate_display query. The difference here would be the row_number in our binding, instead of 0 we’ll use 24. My result binding and measure projection looks something like this –

{{cell(LoanCalculator_1.result, 24, \"repayment\").asString()}} as 'variable_rate_amount'

Step 7 – Make it dynamic with Input Widget

We’re now finally ready to start using input widgets and make this solution dynamic. First, we’ll change the variable and fixed interest rates.

Step 7.1 – Dynamic variable and fixed interest rates

We’ll add input widget for the variable interest rate, and see how that then affects the variable repayment after the fixed-term period. Drag an input widget onto the canvas, and in the widget properties select ‘Slider’ rather than free-form, change the display format to Percentage, choose any minimum and maximum values, and set the starting value to 5.74 and increments to 0.01.

We’ll now bind the input value of the “input widget” to the variable interest rate column in our ‘LoanCalculator’ query. My binding syntax looks like this –

{{cell(variable_rate.selection, 0, \"input\").asString()}}

The result of this binding by default will be 5.74. We’ll have to divide it by 100 to get the correct interest value. Take the above binding, divide it by 100 and locate the LoanCalculator query and replace the value 0.0574 with –

{{cell(variable_rate.selection, 0, \"input\").asString()}}/100

Save and in preview mode, check if the binding is working correctly.

Let’s now do exactly the same thing again, but this time for the fixed interest rate, remembering to use 2.03 as the starting value in the widget, and replacing that value with the binding in the JSON.

Save the changes, and we’ll notice that the values in the input widget are affecting ‘Closing Balance’ in the chart. And just like that, we have created a mortgage loan calculator that gives a picture of Closing Balance at end of each month. But, we need not stop here. We can make other parameters dynamic as well. Let’s do some of those changes in the next section.

Bonus Step – Add more automation

Drag another input widget onto the canvas for the purchase price, change the display format to Currency, select maximum and minimum values of your choosing, and change the start value to our original value of 670000. As these are house prices, it makes sense to set the value of the increment to either 5000 or 10000. At the same time, let’s create a similar one for our Arrangement Fee. Arrangement fees in the UK can vary from £0 to over £2000, but again use any minimum and maximum that you prefer. The binding syntax for each of these will be exactly the same as before, with just the field name changing each time. So go back into the JSON code and replace our original value of 670000 with –

{{cell(purchase_price.selection, 0, \"input\").asString()}}

and 999 with –

{{cell(arrangement_fee.selection, 0, \"input\").asString()}}

Lastly, let’s do the same again for the deposit, but as a percentage (between 10% and 50%) rather than as an amount. Set the minimum and maximum values to 10 and 50 respectively, the start value to 20 (as per our initial example), and increments of 5. Again we’ll need to create our binding syntax for this widget, but this time it will be a calculation to express the deposit as a percentage of the purchase price. The calculation therefore will be –

({{cell(deposit.selection, 0, \"input\").asObject()}}/100) * {{cell(purchase_price.selection, 0, \"input\").asObject()}})

and will replace the 134000 value in our query.

And there we have it. We can now explore any combination of the purchase price, deposit, arrangement fee, and interest rates to understand their impact of repayments and affordability.

By adjusting the values of these input widgets, we can see how changes to these factors affect the outcome of the mortgage calculation. We could also expand this calculator to consider additional factors, such as overpayments or changes to the fixed rate and total duration of the loan. With these interactive elements, we can now easily analyse the financial implications of different mortgage scenarios and make informed decisions about our home financing options.

I hope this post has been helpful in showing some of the potential of this new functionality in CRMA, and I look forward to seeing how it is developed in future releases.

Written by

Manny Omideyi

I am a 3x CRM Analytics Ambassador and have been building solutions in CRM Analytics as an independent consultant since 2018.

See author's posts