Weighted Average Calculation

damen

Hi,

I'm working with a dataset and am wanting to make sure I am going to get a weighted average of our mortgage rates.

We have loans that range from 100k-400k and each loan has an associated mortgage rate. As an example, we have two loans that go into the same security that are 5% and 5.5%

Loan A is worth 200k and loan B is worth 100k. I need to make sure when I am grouping them into their pools, I am not getting an average of the mortgage rate (5.25%) and am instead getting a weighted average (5.165%)

Any suggestions?

I am working with a MagicETL as well.

Loan A 200k 5% poolC

Loan B 100k 5.5% poolC

marcel_luthi

For this you'll need to get the Value of the rate rather than the %, and then calculate the new percentage as the ratio between the two.

At the row level you'll need to have a value that is the Worth of the Loan times the Mortgage Rate of it. In your example for Loan A = 10K and for Loan B = 5.5K. Instead of averaging the % (which is almost never a good idea) you'll work with these values. (10 + 5.5)/(200 + 100) = 5.167% which is what you want. In a single beast mode this can be written as:

SUM(`Worth` * `Mortgage Rate`)/SUM(`Worth`)

Which will work on the aggregation level used for your data.

marcel_luthi

For this you'll need to get the Value of the rate rather than the %, and then calculate the new percentage as the ratio between the two.

At the row level you'll need to have a value that is the Worth of the Loan times the Mortgage Rate of it. In your example for Loan A = 10K and for Loan B = 5.5K. Instead of averaging the % (which is almost never a good idea) you'll work with these values. (10 + 5.5)/(200 + 100) = 5.167% which is what you want. In a single beast mode this can be written as:

SUM(`Worth` * `Mortgage Rate`)/SUM(`Worth`)

Which will work on the aggregation level used for your data.

damen

@marcel_luthi

Awesome! Thank you.