Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.

Tiered Interest Rate Calculator Excel


Download Practice Workbook

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


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.

Dataset for Tier Table

  • 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.

Inserting Account Balance Values based on Tier Table

  • 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)

Tiered Interest Rate Calculator Excel

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

Result of 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.

Final Output of Tiered Interest Rate Calculator in Excel


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%.

Inserting New Tier in Tier Table for Calculating Tiered Interest Rate

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

Applying Formula to Calculate First Rate Interval

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

Applying Formula for Second Rate Interval

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

Getting All Values of Rate Interval in Tier Table

  • 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))

Combining SUMPRODUCT, MAX & MIN Functions to Calculate Tiered Interest Rate

  • Finally, press Enter > AutoFill to get all the Interest Rates at once.

Final Output of Combining SUMPRODUCT, MAX & MIN Functions to Calculate Tiered Interest Rate

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.

Tier Table for VBA to Insert Tiered Interest Rate Calculator

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.

Creating Table for Tiered Interest Rate Calculator Excel

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

Inserting Values for Calculation of Tiered Interest Rate

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

Selecting Visual Basic from Developer Tab

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

Choosing Module in the Visual Basic Editor

  • 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

Tiered Interest Rate Calculator Excel

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)

Inserting Code Generated Formula to Calculate Tiered Interest Rate

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

Final Output of Tiered Interest Rate Calculator


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. Follow ExcelDemy for more tutorials like this.

Guria

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

Leave a reply

ExcelDemy
Logo