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.

Formula to calculate weighted average interest rate in excel

where,

  • A1, A2, and An are the Loan Balances.
  • i1, i2, and in 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.

dataset for how to calculate weighted average interest rate in excel

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

how to calculate weighted average interest rate in excel using Arithmetic Formula

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.

Utilizing SUM function

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 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 interest rate in excel applying SUMPRODUCT and SUM functions

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.

Dataset for How to Calculate Weighted Average with Percentages in Excel

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

Using SUM and SUMPRODUCT functions

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.

Practice Section


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo