Weighted Average Calculation

damen
damen Contributor
in Magic ETL

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

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Answers

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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
    damen Contributor

    @marcel_luthi

    Awesome! Thank you.

    If this helps, feel free to agree, accept or awesome it!

Categories