Weighted Average Formula

A 'weighted average' is something you calculate when you don't know all of the values in multiple sets, but you do know the number of values in each set and their average. Take this example.

The data here shows the average loan-to-value ratios for loans in the mortgage-backed securities deals Alpha and Beta, and the number of loans in each deal. We are missing however data on the LTV ratio for each of the 6000 loans in both deals. If we determined that the average LTV for the two deals was 80% by simply finding the average for 75% and 85%, we would not get an accurate result. Simply averaging out the two given deal LTV ratio averages would not account for the higher number of loans in Beta.

We can account for the higher number of loans in Beta, by calculating the weighted average. The weighted average is found by multiplying the number of values in the first set by the average of those values, and then adding this product to the product of the number of values in the second set and the average of the second set's values. This sum is then divided by total number of values in both sets.

The weighted average is found to be 78.33%.

The Excel SUMPRODUCT formula will multiple each of the values in the first named array, by each of the values in the second named array. So the full formula is written this way in Excel: =SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3)