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

Get FREE Advanced Excel Exercises with Solutions!

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,

• A1, A2, and An are the Loan Balances.
• i1, i2, and in are the Interest Rates.

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

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.

### 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. ### 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 the array1 and array2 arguments where the values of columns 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. ## 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. Admittedly, we’ve skipped some of the relevant examples of how to calculate a weighted average with percentages in Excel, which you may explore if you wish.

## 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. ## Related Articles Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  