Build a Dynamic Loan Calculator in Power BI with What-If Parameters

[Ilgar_Zarbali]

Let’s insert a new page into our Power BI Desktop file and rename it Loan Calculator.

To begin creating the loan calculator, we’ll set up three slicers using What-If Parameters.

The first slicer will represent the Credit Amount.

Navigate to the Modeling tab and select New Parameter.

From the available options, choose Numeric Range.

Input the following values into the appropriate fields:

1. Name: Credit Amount
2. Data Type: Whole Number
3. Minimum: 0
4. Maximum: 100,000
5. Increment: 1,000

Then click the OK button to create the parameter.

Once the parameter is created, a slicer is automatically added to the canvas.

Make sure the "Add slicer to this page" option was checked during parameter creation.

Let’s apply some formatting changes to enhance the appearance of the slicer:

• Set the Slicer Header font size to 14 and make it Bold
• Resize the slicer to make it more compact on the canvas
• Apply a shade to the slicer for better visual distinction
• Set the slicer’s background color to blue for a clean, consistent look

Now, let’s add the second slicer to represent the Credit Terms:

1. Name: Credit Terms, Mths
2. Data Type: Whole Number
3. Minimum: 1
4. Maximum: 120
5. Increment: 1

After entering these values, click the OK button to create the parameter and generate the slicer on the canvas.

To keep the design consistent, use the Format Painter tool:

1. Select the first slicer (Credit Amount) that you already formatted.
2. In the Home tab, click on Format Painter.
3. Then, click on the second slicer (Credit Terms, Mths) to apply the same formatting—this will copy over the font size, bold header, size, background color, and shading.

It’s time to create the third slicer, which will represent the Interest Rate:

1. Name: Interest
2. Data Type: Decimal Number
3. Minimum: 0
4. Maximum: 1
5. Increment: 0.01

After entering these values, click the OK button to create the parameter and add the slicer to the canvas.

First, apply the formatting of the first slicer to the third slicer:

1. Select the first slicer (Credit Amount).
2. Click on the Format Painter in the Home tab.
3. Click on the third slicer (Interest) to apply the same formatting.

Next, organize the layout of all three slicers:

1. Select all three slicers (Credit Amount, Credit Terms, Interest).
2. Go to the Format tab.
3. Click Align → Align Left to line them up vertically.
4. Then click Distribute Vertically to space them evenly.

This will arrange the slicers in a clean and aligned vertical stack as shown in the reference picture.

Now it's time to build the Loan Calculator.

We know the main components of a loan calculator typically include:

• Months (Number of months)
• Beginning Balance
• Main Amount (Principal Payment)
• Interest
• Monthly Payment
• Ending Balance

To start:

1. Add a Table visual to the report canvas.
2. Resize the table so that its height matches the combined height of the three slicers.

This ensures a visually balanced layout, keeping everything neatly aligned.

Let’s format the Table visual for better readability and a clean look:

1. Select the table visual on the canvas.
2. In the Format pane:

• Expand the Column headers section
• Set Font size to 14
• Turn on Bold
• Expand the Values section
• Set Font size to 14

3. Scroll down to theSubtotals or Totals section

• Turn off Totals to hide the total row at the bottom of the table

This formatting will give your Loan Calculator a more professional appearance.

Let’s start by calculating the Main Amount, which represents the fixed portion of the loan paid each month.

1. In the Fields pane, locate the What-If Parameters group (where your slicer-generated measures are stored).
2. Right-click on the group and select New Measure.

Main Amount = 'Credit Amount'[Credit Amount Value] / 'Credit Terms, Mths'[Credit Terms, Mths Value] 

This measure divides the total credit amount by the number of months to get the fixed monthly principal payment.

After adding the Table visual, let’s proceed with selecting values on the slicers:

• Set Credit Amount to 10,000
• Set Credit Terms, Mths to 12
• Set Interest to 10%

Since the Interest slicer was created using a decimal range, we need to update its format to display as a percentage:

1. Go to the Data view
2. Select the Interest table
3. Click on the Interest column

In the ribbon, under Column tools, change the Data type to Percentage

This will display interest values like 0.1 as 10% in both the slicer and visuals.

Let’s now add the Main Amount measure to the Table visual:

Now, let’s calculate the Ending Balance.

The formula for Ending Balance is:

Credit Amount Value – (Main Amount × Max(Credit Terms, Mths Value))

To create the measure:

1. Right-click on the What-If Parameters group and select New Measure
2. Enter the following DAX formula:

Ending Balance = 
'Credit Amount'[Credit Amount Value]-[Main Amount]*Max('Credit Terms, Mths'[Credit Terms, Mths])

This measure calculates the remaining balance after all scheduled principal payments have been made.

Let’s now add the Ending Balance measure to the Table visual:

1. In the Fields pane, locate the Ending Balance measure you just created.
2. Drag and drop the Ending Balance measure into the Table visual.

This will display the remaining loan balance at the end of the term, which should be 0 if the full loan is repaid over the selected period.

Let’s now add the Credit Terms, Mths to the Table visual

To display one row for each month of the loan term (e.g., 12 rows for a 12-month term), follow these steps:

• Create a new table using GENERATESERIES

Go to the Modeling tab → New Table, and enter the following DAX formula:

LoanSchedule = 
GENERATESERIES(1, 'Credit Terms, Mths'[Credit Terms, Mths Value], 1)

This table creates a series from 1 up to the selected number of months.

• Rename the Values column in the Months table to Month.
• Now, add theMonth column from the Months table to the Table visual by dragging it into the visual from the Fields pane.
• Remove the Credit Terms, Mths field from the Table visual.
• Then, for the Month field (from the Months table), open the dropdown in the Values well of the visual and select Don’t summarize to display each month as a separate row.

To fix the issue of displaying all 120 months and seeing an Ending Balance of 0 for all rows, update your Ending Balance measure by replacing the reference to the credit term slicer with the actual month from the Months table.

Ending Balance = 
'Credit Amount'[Credit Amount Value] - 
[Main Amount] * MAX('Months'[Month])

This ensures that the Ending Balance is calculated dynamically for each row based on the current month in the visual.

Some negative values have appeared in the table. To fix this, I’ll use a filter:

Select the Table visual, go to the Filters pane, and for the Ending Balance field, apply a filter where the value is greater than -1. I’m using -1 as the threshold so that 0 values are still visible.

The next step is to calculate the Beginning Balance.

To calculate the Beginning Balance, create a new measure with the following DAX formula:

Beginning Balance = 'Credit Amount'[Credit Amount Value]-[Main Amount]*(MAX(Months[Month])-1)

To organize the Table visual properly, move the Beginning Balance column so that it appears before Main Amount:

Now, let’s calculate the Interest for each month and add it to the table:

Create a new measure using this DAX formula:

Interest = [Beginning Balance]*Interest[Interest Value]/12

This formula calculates monthly interest based on the current month’s beginning balance and the selected annual interest rate.

Drag and drop the newly created Interest measure into the table. This will show the interest portion for each monthly payment.

Let’s create the final measure to calculate the Monthly Payment, which is the sum of the Main Amount and Interest.

Monthly Payment = 
[Main Amount] + [Interest]

This measure reflects the total amount to be paid each month, including both the principal and interest portions.

Drag the Monthly Payment measure into the Table visual to complete your dynamic loan calculator.

Your Dynamic Loan Calculator is now ready!

You can interact with any of the slicers—Credit Amount, Credit Terms, or Interest Rate—and the loan calculator will automatically update to reflect the new values, providing real-time financial insights.

Thank you for exploring our latest newsletter on building a dynamic Loan Calculator using What-If Parameters in Power BI! We hope this guide has inspired you to create interactive financial tools that respond instantly to user inputs. Stay tuned for upcoming editions, where we’ll dive deeper into advanced Power BI techniques, share real-world use cases, and help you level up your reporting skills. Don’t miss out—subscribe now and keep pushing the boundaries of your Power BI capabilities!