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.
Read More: How to Convert Monthly Interest Rate to Annual 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%.
- 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))
- Finally, press Enter > AutoFill to get all the Interest Rates at once.
Read More: How to Use Nominal Interest Rate Formula in Excel
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
- 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.
Read More: How to Find Interest Rate in Future Value Annuity
Download Practice Workbook
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.
Related Articles
- Perform Interest Rate Sensitivity Analysis in Excel
- How to Perform Interest Rate Swap Calculation in Excel
- Calculate Weighted Average Interest Rate in ExcelÂ
- How to Calculate Interest Rate from EMI in ExcelÂ
- Loan Amortization Schedule with Variable Interest Rate in Excel
- How to Calculate Interest Rate on a Loan in Excel
