How to calculate a weighted average interest rate in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)Weight each rate by its balance: =SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5).
- Result on the example
- 7.59% in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMPRODUCT multiplies each balance by its rate and adds the results, which is the total yearly interest: about $4,933. Dividing by the total balance of $65,000 gives the blended rate, 7.59%. The large student loan at 5.05% pulls the average down far more than the small card balance at 21.9% pulls it up.
=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)Result0.07589692308
B2:B54 rows × 1 col: $18,400, $32,600, $4,200, $9,800A range: 4 cells in one column.
C2:C54 rows × 1 col: 6.90%, 5.05%, 21.90%, 11.20%A range: 4 cells in one column.
SUMPRODUCT(B2:B5,C2:C5)4933.3SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B54 rows × 1 col: $18,400, $32,600, $4,200, $9,800 - array2
C2:C54 rows × 1 col: 6.90%, 5.05%, 21.90%, 11.20%
- array1
SUM(B2:B5)65000SUM: Adds numbers and ranges. Skips text and blanks.- number1
B2:B54 rows × 1 col: $18,400, $32,600, $4,200, $9,800
- number1
SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)0.07589692308The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5). We ran it in Google Sheets mode on the same data and it returns 7.59%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =AVERAGE(C2:C5) | 11.26%Different result | A plain average treats every loan as equally large, which overstates the rate here. Unweighted: 11.26%. |
Common mistakes
- The plain AVERAGE of the rates is wrong whenever the balances differ.
- Federal student loan consolidation rounds the weighted rate up to the next 1/8 of a percent:
=CEILING(E2,0.00125).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate a weighted average interest rate
E2, calculate the average interest rate across the loans, weighted by their balances.Questions
What is the total yearly interest?
The SUMPRODUCT on its own: =SUMPRODUCT(B2:B5,C2:C5).
Should I consolidate at a lower rate?
Compare the new rate with this blended rate, and check the term: a longer term can cost more in total even at a lower rate.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.