# How to calculate a weighted average interest rate in Excel

Weight each rate by its balance: `=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)`.

**Excel:** `=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Loan | Balance | Interest rate |  | Blended rate |
| Car loan | 18400 | 0.069 |  |  |
| Student loan | 32600 | 0.0505 |  |  |
| Credit card | 4200 | 0.219 |  |  |
| Personal loan | 9800 | 0.112 |  |  |

Formula in E2: `=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)`

Result: 7.59%

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

## Other ways

- `=AVERAGE(C2:C5)`: 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)`.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/weighted-average-interest-rate