Query speed optimization
You have a problem whenever you join using ASOFDATE , such as here:

on a.CUR_DATE = b.ASOFDATE


, because ASOFDATE is defined as dateadd( day, 1-day(AsOfDate), AsOfDate). To me this seems to mean “first of the month”, and hopefully there’s no time component involved.

So, I’d add a new column to the first CTE, called maybe AsOfDateOrig.

COLLATERAL_VALUES as (
select
    COLLATERAL_TYPE,
    dateadd( day, 1-day(AsOfDate), AsOfDate) as ASOFDATE,
    Value as INDEX
    ,AsOfDate as AsOfDateOrig
from LoanData.CollateralData
),


And then involve that in your join clause too.

on a.CUR_DATE = b.ASOFDATE
and b.AsOfDateOrig &gt;= a.CUR_DATE and b.AsOfDateOrig &lt; dateadd(month,1,a.CUR_DATE)


It might seem redundant to have both, but it allows the QO to use either method as the main Seek Predicate.

Now do the same for everywhere else you join using that. 

I’d consider doing the same for PRIOR_DATE too, basically avoiding joins that involve an expression unless you have the inverse of it too.

Finally, look at your indexing strategy.

You’ll need an Index on LoanData.CollateralData (COLLATERAL_TYPE, AsOfDate) INCLUDE (Value), for example, and similar indexes on the other tables.