Unquestionably, Microsoft Excel excels at crunching numbers! So, this means we can perform tedious calculations like computing the **weighted averages** in the blink of an eye. In this regard, Excel becomes a convenient and valuable tool. Keeping this in mind, this article demonstrates how to a calculate **weighted average interest rate** in Excel. Moreover, we’ll also learn to calculate a weighted average with percentages.

**Table of Contents**Expand

## What Exactly a Weighted Average Interest Rate Is

Let’s start with a quick explanation of what the weighted average interest rate is, so you don’t have to spend all day on this.

In simple terms, the **Weighted Average Interest Rate**, mainly refers to the average that has been modified, to account for the impact that each loan has made on the overall debt.

## Formula to Calculate Weighted Average Interest Rate

Here, the formula to calculate the weighted average interest rate is given below.

where,

- A
_{1}, A_{2}, and A_{n}are the**Loan Balances**. - i
_{1}, i_{2}, and i_{n}are the**Interest Rates**.

## How to Calculate Weighted Average Interest Rate in Excel: 3 Ways

First of all, let’s consider the **Loan Portfolio of Clients** dataset shown in the **B4:D12** cells containing the *“Clients”*, *“Balance”*, and *“Interest Rate”* columns respectively. In this situation, we want to calculate the weighted average interest rate in Excel using the arithmetic formula and Excel functions. Henceforth, let’s see each method in detail and with the necessary illustrations.

Here, we have used the *Microsoft Excel 365* version; you may use any other version at your convenience.

### 1. Using Arithmetic Formula

First and foremost, let’s calculate the weighted average interest rate using the **arithmetic formula discussed previously**.

📌 ** Steps**:

- Initially, go to the
**D14**cell >> enter the formula given below >> click the**ENTER**key.

`=(C5*D5+C6*D6+C7*D7+C8*D8+C9*D9+C10*D10+C11*D11+C12*D12)/(C5+C6+C7+C8+C9+C10+C11+C12)`

Here, we’ve computed the product of the *“Balance”* and *“Interest Rates”* columns. Then, divide the result by the total *“Balance”*.

Boom! The final output should look like the screenshot shown above.

**Read More: **How to Calculate Effective Interest Rate in Excel with Formula

### 2. Utilizing SUM Function

For one thing, functions are the heart and soul of an Excel spreadsheet. Therefore, in our next method, we’ll apply the **SUM function** to get the weighted average interest rate. It’s simple and easy; just follow along.

📌 ** Steps**:

- In the first place, move to the
**D14**cell >> enter the expression given below >> press**ENTER**key.

`=SUM(C5:C12*D5:D12)/SUM(C5:C12)`

For instance, the **C5:C12** and **D5:D12** arrays represent the *“Balance”* and *“Interest Rate”* columns respectively.

**Read More: **How to Use Nominal Interest Rate Formula in Excel

### 3. Applying SUMPRODUCT and SUM Functions

Besides, we can combine the **SUM** and **SUMPRODUCT** functions to compute the weighted average interest rate. In this case, the **SUMPRODUCT** function returns the sum of the products of *“Balance”* and *“Interest Rate”* while the **SUM** function gives the sum of the *“Balance”*.

📌 ** Steps**:

- To begin with, copy and paste the equation into the
**Formula Bar**in the**D14**cell >> hit**ENTER**.

`=SUMPRODUCT(C5:C12,D5:D12)/SUM(C5:C12)`

**Formula Breakdown**

**SUM(C5:C12) →**adds all the numbers in a range of cells. Here, the function adds all the values in the**C5:C12**range.**Output → $2,991,107**

**SUMPRODUCT(C5:C12,D5:D12) →**returns the sum of the products of the*“Balance”*and*“Interest Rate”*arrays. Here, the**C5:C12**and**D5:D12**are theand*array1*arguments where the values of columns*array2***C**and**D**are multiplied to give the output.**Output → 252789.785**

**SUMPRODUCT(C5:C12,D5:D12)/SUM(C5:C12)****→**becomes**$2,991,107 / 252789.785 → 8.45**

*📃 ***Note: **Make sure to open the **Format Cells **dialog box by pressing **CTRL +**** 1***,* *then change the cell formatting to percentage.*

**Read More: **Nominal vs Effective Interest Rate in Excel

## How to Calculate Weighted Average with Percentages in Excel

Last but not least, we can also calculate the weighted average with percentages utilizing the **SUM** and **SUMPRODUCT** functions. On this occasion, let’s suppose the **Marks Distribution of Students** dataset is in the **B4:E11** cells with the *“Name”*, *“Mid Term 1”*, *“Mid Term 2”* and *“Final”* columns. In addition, we have the *“Weightage in Percentage”* values for the corresponding exams in the **B13:E13** cells.

📌 ** Steps**:

- At the very beginning, enter the formula into the
**F5**cell >> press the**ENTER**key >> drag the**Fill Handle**tool to copy the formula to the cells below.

`=SUMPRODUCT(C5:E5,$D$13:$F$13)/SUM($D$13:$F$13)`

Here, the **C5:E5** range points to the marks scored by *“Zoe”*, whereas the **D13:F13** cells indicate the *“Weightage in Percentage”*. In this scenario, the **SUMPRODUCT** function yields the summation of the products of the marks scored in the *“Mid Term 1”*, *“Mid Term 2”* and *“Final”* exams and their *“Weightage in Percentage”* values.

📃 *Note:**Please make sure to use **Absolute Cell Reference** by pressing the F4 key on your keyboard.*

**Read More: **How to Calculate Periodic Interest Rate in Excel

## Practice Section

We have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

**Download Practice Workbook**

## Conclusion

In essence, this article shows **3** effective methods of **how to calculate weighted average interest rate** in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful, and if you have any further queries or recommendations, please feel free to comment here.

**<< Go Back to How to Calculate Interest Rate in Excel | ****Excel for Finance**** | ****Learn Excel**