# How to Use Tiered Interest Rate Calculator in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In banking or other money exchange fields, we often face the term Tiered Interest Rate. It means that, according to the policy of the specific country, there are some ranges of values that create different amounts of interest. The ranges are accommodated with initial interest rates. It may sound like a complex process but Microsoft Excel has made it really simple. In this article, we will describe how to use a tiered interest rate calculator in Excel with 3 useful examples.

## 3 Examples to Use a Tiered Interest Rate Calculator in Excel

To apply a tiered interest rate, we need some range of amounts with different interest rates. Each will create a single tier. Then, providing your account balance will check which tier it falls under. After that, you will get the value of the tiered interest. You can use them as a calculator further by just giving your preferred Account Balance value. Letâ€™s follow the methods below to calculate this.

### Example 1: Use SUMPRODUCT Function to Apply the Tiered Interest Rate on the Current Account Balance

The SUMPRODUCTÂ function can single-handedly calculate the tiered interest rate. For this, go through the following steps.

• First, create a Tier Table according to your regional policy like this.

• Then, create another table titled Account Balance and Interest.
• Here, insert the amounts that you have in different accounts in the Cell range B12:B15.

• Next, insert this formula in Cell C12.
`=SUMPRODUCT((B12<=\$C\$6:\$C\$9)*(B12>\$B\$6:\$B\$9)*(B12-\$B\$6:\$B\$9)*\$D\$6:\$D\$9)+SUMPRODUCT(((B12>\$C\$6:\$C\$9)*(\$C\$6:\$C\$9-\$B\$6:\$B\$9))*\$D\$6:\$D\$9)`

• Then, hit Enter to get the first Tiered Interest Rate.

ðŸ”Ž How Does the Formula Work?

• In this formula, there are 2 SUMPRODUCT functions.
• The first one is used to calculate the balance based on Cell B12 which is above or below tiers B6:B9 and C6:C9. It also calculates the corresponding percentage from D6:D9.
• Then the second SUMPRODUCT function finds out the value that reached the tier level and corresponding percentages.
• Finally, it adds both numbers and gives the final interest rate.
• Lastly, use the AutoFill tool to get all the Interest values for each Account Balance.

### Example 2: Combine SUMPRODUCT, MAX & MIN Functions for a More Dynamic Application of Tiered Interest Rate Calculator

If you want to make the calculation more dynamic, you can combine the SUMPRODUCT, MAX and MIN functions. To do the task, simply follow the process below.

• In the beginning, insert a new tier that takes any value of more than 1000000 with a percentage of 0.68%.

• Then, insert this formula to get the first Rate Interval.
`=C6`

• Next, apply this formula to get the second Rate Interval in the Tier Table.
`=C7-C6`

• Afterward, apply AutoFill to get all the values like this.

• Now, insert this formula in Cell C13.
`=SUMPRODUCT((MIN(B13,MAX(\$B\$6:\$B\$10))-\$B\$6:\$B\$10)*\$D\$6:\$D\$10*((MIN(B13,MAX(\$B\$6:\$B\$10))-\$B\$6:\$B\$10)>=0))`

Here, the MAX and MIN functions check if the value in Cell B13 is the minimum or maximum value among the Cell range B6:B9. Following, it is multiplied by the Cell range D6:D9 which represents the corresponding percentages. Lastly, they are added by the SUMPRODUCT function.

### Example 3. Apply Excel VBA to Create and Apply a Tiered Interest Calculator

In this last method, let us calculate the Tiered Interest Rate based on dates and due amounts. This process is very effective when you have to keep day-to-day records. For this, we are considering the following conditions in the Tier Table.

Now, follow the steps below to create the tiered interest rate calculator.

• First, create a new table with the titles Invoice Received, Bill Paid, Due Amount and Interest.

• Then, insert the initial values according to your requirement as shown below.

• Next, select Visual Basic under the Code group from the Developer tab.

• Afterward, choose Module from the Insert tab in the Microsoft Visual Basic for Applications window.

• Then, insert this code in the Code page.
``````Function TieredInterest(Inv As Date, Bill As Date, DueAmt As Double)
Dim Lt As Long
Lt = Bill - Inv
Const Tier0 = 0.0068 Â  Â  '-- First 45 Days
Const Tier1 = 0.0093 '-- 46 to 90 Days
Const Tier2 = 0.0112 Â  '-- 91 to 135 Days
Const Tier3 = 0.0137 '-- 136+ days
Select Case Late
Case 0 To 45: TieredInterest = Round(Tier0 * DueAmt * (Lt / 45), 2)
Case 46 To 90: TieredInterest = Round(Tier1 * DueAmt * ((Lt - 45) / 45), 2)
Case 91 To 135: TieredInterest = Round(Tier1 * DueAmt * (45 / 45), 2) + Round(Tier2 * DueAmt * ((Lt - 90) / 45), 2)
Case 136 To 10000: TieredInterest = Round(Tier1 * DueAmt * (45 / 45), 2) + Round(Tier2 * DueAmt * (45 / 45), 2) + Round(Tier3 * DueAmt * ((Lt - 135) / 45), 2)
End Select
End Function``````

Here, the code generates a new function TieredInterest that will directly calculate interest rates based on the given tier. You can modify tier conditions according to your preference.
• After this, press Ctrl + S to save the code and close the window.
• Now, insert this formula in Cell E12 to get the Interest value for the given Due Amount dated between 45 days.
`=TiredInterest(B12,C12,D12)`

• Lastly, provide any dates more than 45 days and it will give you the Interest based on the Tier Table.

Get this sample file we prepared for this article and practice by yourself.

## Conclusion

Henceforth, this article describes 3 useful examples to use a tiered interest rate calculator in Excel. Let us know your insightful suggestions in the comment box.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF