How to Calculate the Weighted Average Interest Rate in Excel – 3 Methods

 

The Weighted Average Interest Rate

The Weighted Average Interest Rate refers to an average that is adjusted to show the impact of each loan on total debt.


Formula to Calculate the Weighted Average Interest Rate

Formula to calculate weighted average interest rate in excel

 

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

The Loan Portfolio of Clients dataset contains “Clients”, “Balance”, and “Interest Rate”. 

dataset for how to calculate weighted average interest rate in excel

 

Method 1 – Using an Arithmetic Formula

Steps:

  • Go to the D14 >> enter the formula below >> press ENTER.

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

The product of the “Balance” and “Interest Rates” columns was calculated and divided by the total “Balance”.

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

This is the output.

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


Method 2 – Utilizing the SUM Function

Steps:

  • Go to D14 >> enter the expression below >> press ENTER.

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

C5:C12 and D5:D12 arrays represent the “Balance” and “Interest Rate” columns.

Utilizing SUM function

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


Method 3 – Applying the SUMPRODUCT and the SUM Functions

Steps:

  • Enter the formula in D14 >> press ENTER.

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

Formula Breakdown
  • SUM(C5:C12) →  adds all numbers in a range of cells. Here, the values in C5:C12.
    • Output → $2,991,107
  • SUMPRODUCT(C5:C12,D5:D12) → returns the sum of the products of the “Balance” and “Interest Rate” arrays. Here, C5:C12 and D5:D12 are array1 and array2 arguments in which the values of columns C and D are multiplied.
    • Output → 252789.785
  • SUMPRODUCT(C5:C12,D5:D12)/SUM(C5:C12) becomes
    • $2,991,107 / 252789.785 → 8.45

Note: Open the Format Cells dialog box by pressing CTRL + 1 and 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 the Weighted Average with Percentages in Excel

The dataset showcases students’ “Name”, “Mid Term 1”, “Mid Term 2” and “Final” marks. Exam values in B13:E13 are in percentage.

Dataset for How to Calculate Weighted Average with Percentages in Excel

Steps:

  • Enter the formula into F5 >> press ENTER >> drag the Fill Handle to copy the formula to the cells below.

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

C5:E5 points to the marks scored by “Zoe”, whereas D13:F13 indicates the “Weightage in Percentage”. The SUMPRODUCT function yields the summation of the products of the marks scored in“Mid Term 1”, “Mid Term 2” and “Final” exams and their “Weightage in Percentage” values.

Note: Use Absolute Cell Reference by pressing the F4.

Using SUM and SUMPRODUCT functions

Read More: How to Calculate Periodic Interest Rate in Excel


Practice Section

Practice here.

Practice Section


Download Practice Workbook


 

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

Get FREE Advanced Excel Exercises with Solutions!
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