SQL indexed LTV calculation

Question

I've been given the following data for the two below tables and need to calculate an indexed LTV for each account as at December 2016, 2017 & 2018, and not sure how to carry it out. The solution needs to be a SQL script. Any ideas much appreciated!

Index table:

• IndexArea – region that the house price index relates to
• IndexMonth – month (yyyymm format) that the house price index relates to
• Movement – an index to represent the house price at the index month.

Data table:

• Mortgageno – unique identifier for a mortgage account
• PropertyArea – region that the house is in
• ValuationDate – date of the valuation of the house
• PropertyValuation – valuation of house as at ValuationDate
• BalanceDecember2016 – outstanding value of the mortgage/loan at December 2016
• BalanceDecember2017 – outstanding value of the mortgage/loan at December 2017
• BalanceDecember2018 – outstanding value of the mortgage/loan at December 2018

question from:https://stackoverflow.com/questions/65859622/sql-indexed-ltv-calculation

Answer 1

Here's an idea, albeit simplistic, of how you could calculate it. I assume that the index data is available for every month and every area of interest, so no "fuzzy match" is required. Example is TSQL / MS SQL Server.

So, using the same model as your two key tables, set up some example data...

CREATE TABLE #DataTable
(MortgageNo VARCHAR(15), 
PropertyArea VARCHAR(50), 
ValuationDate DATETIME, 
PropertyValuation DECIMAL(12, 2), 
BalanceDecember2016 DECIMAL(12, 2), 
BalanceDecember2017 DECIMAL(12, 2), 
BalanceDecember2018 DECIMAL(12, 2))

INSERT INTO #DataTable
VALUES
('MORTGAGE001', 'KENT', '01-Jan-2015', 300000, 250000, 230000, 220000),
('MORTGAGE002', 'LONDON', '01-Jan-2016', 500000, 450000, 430000, 420000)

CREATE TABLE #IndexTable
(IndexArea VARCHAR(50),
IndexMonth CHAR(6),
Movement DECIMAL(7, 4))

INSERT INTO #IndexTable
VALUES
('KENT', '201501', 1.05), -- 105%
('KENT', '201612', 1.10), -- 110%
('KENT', '201712', 1.15),
('KENT', '201812', 1.10),
('LONDON', '201601', 1.15),
('LONDON', '201612', 1.15),
('LONDON', '201712', 1.25),
('LONDON', '201812', 1.30)

Now, pivot the data so instead of 3 columns for the 3 difference balance dates, we have 3 rows with a balance date and balance in each.

CREATE TABLE #BalanceDates (BalanceDate DATE)
INSERT INTO #BalanceDates 
VALUES ('2016-12-01'),('2017-12-01'),('2018-12-01')

SELECT 
    MortgageNo,
    PropertyArea,
    ValuationDate,
    PropertyValuation,
    balanceDates.BalanceDate,
    CASE balanceDates.BalanceDate 
        WHEN '2016-12-01' THEN BalanceDecember2016
        WHEN '2017-12-01' THEN BalanceDecember2017
        WHEN '2018-12-01' THEN BalanceDecember2018
    END AS Balance
INTO
    #BalancePivoted
FROM 
    #DataTable mortgageData, 
    #BalanceDates balanceDates

Now, the calculation. Two joins to the index data - one on the original property valuation date, to get the index level at the property valuation date. The second, to get the index level at the balance date. Calculate the percentage difference of the house price index at valuation compared to balance date, and adjust the property valuation:

balance.PropertyValuation * (indexBalance.Movement / indexAtValuation.Movement)

So for example, if index at valuation was 1 (100%), and index at balance date is 1.1 (110%), then the original valuation of 100,000 is adjusted to 110,000.

Then you can use the adjusted valuation with the balance to calculate the Loan to Value ratio, and hopefully that qualifies as an indexed LTV figure.

balance.Balance / (balance.PropertyValuation * (1.0 + indexBalance.Movement - indexAtValuation.Movement)) AS IndexedLTV

SELECT 
    balance.*,
    indexAtValuation.Movement AS IndexAtValuationDate,
    indexBalance.Movement AS IndexAtBalanceDate,
    indexBalance.Movement - indexAtValuation.Movement AS IndexPercentChange,
    balance.PropertyValuation * (indexBalance.Movement / indexAtValuation.Movement) AS AdjustedValuation,
    balance.Balance / (balance.PropertyValuation * (1.0 + indexBalance.Movement - indexAtValuation.Movement)) AS IndexedLTV
FROM 
    #BalancePivoted balance
    JOIN
    #IndexTable indexAtValuation
    ON
    DATEADD(MONTH, DATEDIFF(MONTH, 0, balance.ValuationDate), 0) = CONVERT(date, indexAtValuation.IndexMonth + '01')
    AND
    balance.PropertyArea = indexAtValuation.IndexArea
    JOIN
    #IndexTable indexBalance
    ON
    balance.BalanceDate = CONVERT(date, indexBalance.IndexMonth + '01')
    AND
    balance.PropertyArea = indexBalance.IndexArea