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.

**Table of Contents**hide

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

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

**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
```

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

**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