How to Calculate Weighted Average Interest Rate in Excel (3 Ways)

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.

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₁, A₂, and A_n are the Loan Balances.
• i₁, i₂, 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.

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.

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.

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

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