CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
UserSN
UserSN

Reputation: 1013

SQL Server 2012: Weighted Average Calculation

I'm trying to calculate the weighted average maturity of some data in my table.

SaleEventID  LID(PK)    CurrentUPB         Interest Rate    RemainingMonths
1            1          $100,000.00         6.100%           11.00
1            2          $67,000.00          6.200%           360.00
1            3          $1,400,000.00       6.300%           240.00
1            4          $500,000.00         7.000%           100.00
2            5          $1,400,000.00       7.100%           240.00
2            6          $500,000.00         7.000%           100.00

So the formula i'm trying to accomplish is (WAM):
1) Multiply CurrentUPB * RemainingMonths for LID=1
2) Do that for each row that matches WHERE SaleEventID=1
3) SUM the above calculation = $411,220,000.00 = A
4) SUM all the CurrentUPB WHERE SaleEventID=1 which equals $2,067,000.00 =B
5) Then Divide A/B = $198.95 which is my WAM

I need to consider that in my table I will have many Loans and that each will not be attributed the same SaleEventID value (Which is not the Primary Key)

My query so far:

SELECT l.*, A / B FROM AS WAM
FROM ( SELECT LSX_DC_Loans l
        (SELECT CurrentUPB * RemainingMonths FROM l WHERE LID = 1
         ) AS A
        (SELECT SUM (CurrentUPB) CurrentUPB FROM LSX_DC_Loans
        WHERE SaleEventID = 1
        ) AS B
FROM l
) l

I'm having trouble figuring out how to do steps 2 & 4. Any help, examples highly appreciated.

Upvotes: 1

Views: 838

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

To calculate WAM and WAIR is pretty simple. Think sumproduct() in Excel

Declare @YourTable table (SaleEventID  int,LID int,CurrentUPB money,[Interest Rate] money,RemainingMonths money)
Insert Into @YourTable values
(1,1,100000.00,6.100, 11.00),
(1,2,67000.00,6.200, 360.00),
(1,3,1400000.00,6.300, 240.00),
(1,4,500000.00,7.000, 100.00),
(2,5,1400000.00,7.100, 240.00),
(2,6,500000.00,7.000,100.00)


Select SaleEventID
      ,UPB  = sum(CurrentUPB)
      ,WAM  = sum(CurrentUPB*RemainingMonths)/sum(CurrentUPB)
      ,WAIR = sum(CurrentUPB*[Interest Rate] )/sum(CurrentUPB)
 From  @YourTable
 Where SaleEventID = @Event
 Group By SaleEventID

Returns

SaleEventID UPB         WAM         WAIR
1           2067000.00  198.9453    6.4564
2           1900000.00  203.1578    7.0736

Upvotes: 4

DVT
DVT

Reputation: 3127

If I understand your question correctly, here is the answer

SELECT
    l.*
    , l1.A / l2.B AS WAM
FROM
    LXC_DC_Loans l
    CROSS APPLY (SELECT SUM(CurrentUPB * RemainingMonths) AS A FROM LSX_DC_Loans WHERE SaleEventID=l.SaleEventID) l1
    CROSS APPLY (SELECT SUM(CurrentUPB) AS B A FROM LSX_DC_Loans WHERE SaleEventID=l.SaleEventID) l2

Upvotes: 0

Related Questions