How to Calculate a Weighted Average in a Pivot Table

[grehce]

The below will show you how to calculate a weighted average for a
group of data in 3 simple steps. In this case, a weighted average
interest rate based on a prime or subprime borrower.

Given data:

Credit Type Borr UPB IntRate
Prime Kelly $20,000 6.875
Prime Kline $68,000 7.5
Prime Stephens $450,000 6.5
Prime Jordan $784,000 6.125
Subprime Smith $125,000 8
Subprime Westerfield $200,000 7.5
Subprime Ross $312,000 7
Subprime Gerry $615,000 7.25

STEP 1: Add column to your data that multiplies each interest rate by
the UPB (unpaid principal balance). This will simplify the calculated
field you must build in the pivot and calculate your weighted average
correctly.

Credit Type Borr UPB IntRate UPB*IntR
Prime Kelly $20,000 6.875 $137,500
Prime Kline $68,000 7.5 $510,000
Prime Stephens $450,000 6.5 $2,925,000
Prime Jordan $784,000 6.125 $4,802,000
Subprime Smith $125,000 8 $1,000,000
Subprime Westerfield $200,000 7.5 $1,500,000
Subprime Ross $312,000 7 $2,184,000
Subprime Gerry $615,000 7.25 $4,458,750

STEP 2: Create a pivot using the wizard to capture the above 5
columns. Your initial pivot should look like this:

Data
Credit Type Sum of UPB
Prime $1,322,000
Subprime $1,252,000
Grand Total $2,574,000

STEP 3: Right-click data > 'Formulas' > 'Calculated Field' and enter
'WtdAvg IntRate' as the name of your field. Your formula should read:

=SUM('UPB*IntR')/SUM(UPB)

Results:

Data
Credit Type Sum of UPB Sum of WtdAvg IntRate
Prime $1,322,000 6.335
Subprime $1,252,000 7.303
Grand Total $2,574,000 6.805

Note: Make sure your results match the format type of the data. For
example, if your interest rate shows % in the data, format the
resulting WtdAvg IntRate as %.


-Grace