private void RunLGDJob(List <LGDAccountData> subcontract, List <PdMappings> pdMapping, List <SicrInputs> sicrInput, List <StageClassification> stageClassification, List <LifetimeEad> lifetimeEAD, List <LifetimeCollateral> lifetimeCollateralBest, List <LifetimeCollateral> lifetimeCollateralOptimistic, List <LifetimeCollateral> lifetimeCollateralDownturn, List <LifeTimeObject> lifetimePdBest, List <LifeTimeObject> lifetimePdOptimistic, List <LifeTimeObject> lifetimePdDownturn, List <LifeTimeObject> redefaultPdBest, List <LifeTimeObject> redefaultPdOptimistic, List <LifeTimeObject> redefaultPdDownturn)
{
Log4Net.Log.Info($"2");
var subcontractIds = subcontract.Select(o => o.CONTRACT_NO).ToList();
Log4Net.Log.Info($"3");
var subpdMapping = pdMapping.Where(o => subcontractIds.Contains(o.ContractId)).ToList();
Log4Net.Log.Info($"4");
var subsicrInput = sicrInput.Where(o => subcontractIds.Contains(o.ContractId)).ToList();
Log4Net.Log.Info($"5");
var substageClassification = stageClassification.Where(o => subcontractIds.Contains(o.ContractId)).ToList();
Log4Net.Log.Info($"6");
var sublifetimeEAD = lifetimeEAD.Where(o => subcontractIds.Contains(o.ContractId)).ToList();
Log4Net.Log.Info($"7");
var sublifetimeCollateralBest = lifetimeCollateralBest.Where(o => subcontractIds.Contains(o.ContractId)).ToList();
Log4Net.Log.Info($"8");
var sublifetimeCollateralOptimistic = lifetimeCollateralOptimistic.Where(o => subcontractIds.Contains(o.ContractId)).ToList();
Log4Net.Log.Info($"9");
var sublifetimeCollateralDownturn = lifetimeCollateralDownturn.Where(o => subcontractIds.Contains(o.ContractId)).ToList();
Log4Net.Log.Info($"10");
var pdGroups = subpdMapping.Select(o => o.PdGroup).ToList();
Log4Net.Log.Info($"11");
var sublifetimePdBest = lifetimePdBest.Where(o => pdGroups.Contains(o.PdGroup)).ToList();
Log4Net.Log.Info($"12");
var sublifetimePdOptimistic = lifetimePdOptimistic.Where(o => pdGroups.Contains(o.PdGroup)).ToList();
Log4Net.Log.Info($"13");
var sublifetimePdDownturn = lifetimePdDownturn.Where(o => pdGroups.Contains(o.PdGroup)).ToList();
Log4Net.Log.Info($"14");
var subredefaultPdBest = redefaultPdBest.Where(o => pdGroups.Contains(o.PdGroup)).ToList();
Log4Net.Log.Info($"15");
var subredefaultPdOptimistic = redefaultPdOptimistic.Where(o => pdGroups.Contains(o.PdGroup)).ToList();
Log4Net.Log.Info($"16");
var subredefaultPdDownturn = redefaultPdDownturn.Where(o => pdGroups.Contains(o.PdGroup)).ToList();
Log4Net.Log.Info($"17");
var _lifetimeLGD = new List <LifetimeLgd>();
Log4Net.Log.Info($"Getting LGD_COntract for Task");
foreach (var row in subcontract)
{
try
{
//Console.WriteLine($"Got LGD_COntract -{row.CONTRACT_NO}");
string contractId = row.CONTRACT_NO;
double costOfRecovery = row.COST_OF_RECOVERY;
double guarantorPd = row.GUARANTOR_PD;
double guarantorLgd = row.GUARANTOR_LGD;
double guaranteeValue = row.GUARANTEE_VALUE;
double guaranteeLevel = row.GUARANTEE_LEVEL;
//xxxxxxxxxxxxxxxxxxxxxxxxxxx
//try { loanStage= stageClassification.FirstOrDefault(x => x.ContractId == contractId).Stage; } catch { };
int loanStage = 1;
try
{
loanStage = substageClassification.FirstOrDefault(x => x.ContractId == contractId).Stage;
}
catch (Exception ex)
{
if (contractId.Contains(ECLStringConstants.i.ExpiredContractsPrefix))
{
loanStage = 3;
}
Log4Net.Log.Error(ex);
}
var pdMappingRow = subpdMapping.FirstOrDefault(x => x.ContractId == contractId);
//xxxxxxxxxxxxxxxxxxxxxxxxxxxx
string pdGroup = pdMappingRow.PdGroup ?? "";
string segment = pdMappingRow.Segment ?? "";
if (segment == "" && contractId.StartsWith(ECLStringConstants.i.ExpiredContractsPrefix))
{
try { segment = contractId.Split('|')[1]; } catch { }
}
string productType = pdMappingRow.ProductType ?? "";
//xxxxxxxxxxxxxxxxxxxxxxxxxxxx
var sicrInputRow = subsicrInput.FirstOrDefault(x => x.ContractId == contractId);
//if (sicrInputRow == null)
//{
// sicrInputRow = sicrInput.FirstOrDefault();
//}
double redefaultLifetimePd = sicrInputRow.RedefaultLifetimePd;
long daysPastDue = sicrInputRow.DaysPastDue;
//XXXXXXXXXXXXXXXX
//var best_downTurn_Assumption = lgdAssumptions.FirstOrDefault(o => o.Segment_Product_Type.ToLower().Contains($"{segment.ToLower()}{productType.ToLower()}".Replace(" ", "")));
var best_downTurn_Assumption = lgdAssumptions.FirstOrDefault(o => o.Segment_Product_Type.ToLower().Contains($"{segment.ToLower().Trim()}"));
if (best_downTurn_Assumption == null)
{
best_downTurn_Assumption = lgdAssumptions.FirstOrDefault();
}
double cureRates = best_downTurn_Assumption.Cure_Rate;
long lgdAssumptionColumn = Math.Max(daysPastDue - stage2to3Forward, 0);
double unsecuredRecoveriesBest = 0;
double unsecuredRecoveriesDownturn = 0;
if (lgdAssumptionColumn == 0)
{
unsecuredRecoveriesBest = best_downTurn_Assumption.Days_0;
unsecuredRecoveriesDownturn = best_downTurn_Assumption.Downturn_Days_0;
}
if (lgdAssumptionColumn == 90)
{
unsecuredRecoveriesBest = best_downTurn_Assumption.Days_90;
unsecuredRecoveriesDownturn = best_downTurn_Assumption.Downturn_Days_90;
}
if (lgdAssumptionColumn == 180)
{
unsecuredRecoveriesBest = best_downTurn_Assumption.Days_180;
unsecuredRecoveriesDownturn = best_downTurn_Assumption.Downturn_Days_180;
}
if (lgdAssumptionColumn == 270)
{
unsecuredRecoveriesBest = best_downTurn_Assumption.Days_270;
}
if (lgdAssumptionColumn == 360)
{
unsecuredRecoveriesBest = best_downTurn_Assumption.Days_360;
unsecuredRecoveriesDownturn = best_downTurn_Assumption.Downturn_Days_360;
}
var month = 0;
double month1pdValueBest = ComputeLifetimeRedefaultPdValuePerMonth(sublifetimePdBest, pdGroup, 1); // Excel INDEX(PD_BE,$C8, 2)
double month1pdValueOptimistic = ComputeLifetimeRedefaultPdValuePerMonth(sublifetimePdOptimistic, pdGroup, 1);
double month1pdValueDownturn = ComputeLifetimeRedefaultPdValuePerMonth(sublifetimePdDownturn, pdGroup, 1);
var eadForContractMaxMonth = sublifetimeEAD.Where(o => o.ContractId == contractId).Max(p => p.ProjectionMonth);
//while (0 == 0)
//{
for (int i = 0; i <= eadForContractMaxMonth; i++)
{
var monthLifetimeEADObj = GetLifetimeEADPerMonth(sublifetimeEAD, contractId, month); //Excel lifetimeEAD!F
var monthLifetimeEAD = 0.0;
if (monthLifetimeEADObj != null)
{
monthLifetimeEAD = monthLifetimeEADObj.ProjectionValue;
}
var cIndex = GetCreditIndexPerMonth(creditIndex, month);
double monthCreditIndexBest = cIndex.BestEstimate; // Excel $O$3
double monthCreditIndexOptimistic = cIndex.Optimistic; //GetCreditIndexPerMonth(creditIndex, month, ECL_Scenario.Optimistic);
double monthCreditIndexDownturn = cIndex.Downturn; // GetCreditIndexPerMonth(creditIndex, month, ECL_Scenario.Downturn);
double sumLifetimePdsBest = ComputeLifetimeRedefaultPdValuePerMonth(sublifetimePdBest, pdGroup, month); //Excel Sum(OFFSET(PD_BE, $C8-1, 1, 1, O$7))
double sumLifetimePdsOptimistic = ComputeLifetimeRedefaultPdValuePerMonth(sublifetimePdOptimistic, pdGroup, month); //Excel Sum(OFFSET(PD_BE, $C8-1, 1, 1, O$7))
double sumLifetimePdsDownturn = ComputeLifetimeRedefaultPdValuePerMonth(sublifetimePdDownturn, pdGroup, month); //Excel Sum(OFFSET(PD_BE, $C8-1, 1, 1, O$7))
double sumRedefaultPdsBest = ComputeLifetimeRedefaultPdValuePerMonth(subredefaultPdBest, pdGroup, month); //Excel SUM(OFFSET(RD_PD_BE, $C8-1, 1, 1, O$7)))
double sumRedefaultPdsOptimistic = ComputeLifetimeRedefaultPdValuePerMonth(subredefaultPdOptimistic, pdGroup, month);
double sumRedefaultPdsDownturn = ComputeLifetimeRedefaultPdValuePerMonth(subredefaultPdDownturn, pdGroup, month);
double lifetimeCollateralValueBest = ComputeLifetimeCollateralValuePerMonth(sublifetimeCollateralBest, contractId, month); // Excel 'Lifetime Collateral (BE)'!E4
double lifetimeCollateralValueOptimistic = ComputeLifetimeCollateralValuePerMonth(sublifetimeCollateralOptimistic, contractId, month); // Excel 'Lifetime Collateral (BE)'!E4
double lifetimeCollateralValueDownturn = ComputeLifetimeCollateralValuePerMonth(sublifetimeCollateralDownturn, contractId, month); // Excel 'Lifetime Collateral (BE)'!E4
double resultUsingMonth1pdValueBest = month1pdValueBest == 1.0 ? 1.0 : 0.0; // IF(INDEX(PD_BE,$C8, 2) = 1,1,0),
double resultUsingMonth1pdValueOptimistic = month1pdValueOptimistic == 1.0 ? 1.0 : 0.0;
double resultUsingMonth1pdValueDownturn = month1pdValueDownturn == 1.0 ? 1.0 : 0.0;
double redefaultCalculationBest = (redefaultLifetimePd - sumRedefaultPdsBest) / (1 - sumLifetimePdsBest);
double redefaultCalculationOptimistic = (redefaultLifetimePd - sumRedefaultPdsOptimistic) / (1 - sumLifetimePdsOptimistic);
double redefaultCalculationDownturn = (redefaultLifetimePd - sumRedefaultPdsDownturn) / (1 - sumLifetimePdsDownturn);
double maxRedefaultPdValueBest = Math.Max(redefaultCalculationBest, 0.0);
double maxRedefaultPdValueOptimistic = Math.Max(redefaultCalculationOptimistic, 0.0);
double maxRedefaultPdValueDownturn = Math.Max(redefaultCalculationDownturn, 0.0);
double ifSumLifetimePdBest = sumLifetimePdsBest == 1.0 ? resultUsingMonth1pdValueBest : maxRedefaultPdValueBest;
double ifSumLifetimePdOptimistic = sumLifetimePdsOptimistic == 1.0 ? resultUsingMonth1pdValueOptimistic : maxRedefaultPdValueOptimistic;
double ifSumLifetimePdDownturn = sumLifetimePdsDownturn == 1.0 ? resultUsingMonth1pdValueDownturn : maxRedefaultPdValueDownturn;
double checkForMonth0Best = month == 0.0 ? redefaultLifetimePd : ifSumLifetimePdBest;
double checkForMonth0Optimistic = month == 0.0 ? redefaultLifetimePd : ifSumLifetimePdOptimistic;
double checkForMonth0Downturn = month == 0.0 ? redefaultLifetimePd : ifSumLifetimePdDownturn;
double checkForStage1Best = loanStage != 1.0 ? cureRates * checkForMonth0Best : 0.0;
double checkForStage1Optimistic = loanStage != 1.0 ? cureRates * checkForMonth0Optimistic : 0.0;
double checkForStage1Downturn = loanStage != 1.0 ? cureRates * checkForMonth0Downturn : 0.0;
double maxCurerateResultBest = Math.Max((1.0 - cureRates) + checkForStage1Best, 0.0);
double maxCurerateResultOptimistic = Math.Max((1.0 - cureRates) + checkForStage1Optimistic, 0.0);
double maxCurerateResultDownturn = Math.Max((1.0 - cureRates) + checkForStage1Downturn, 0.0);
double minMaxCureRateResultBest = Math.Min(maxCurerateResultBest, 1.0);
double minMaxCureRateResultOptimistic = Math.Min(maxCurerateResultOptimistic, 1.0);
double minMaxCureRateResultDownturn = Math.Min(maxCurerateResultDownturn, 1.0);
///
double lifetimeCollateralForMonthCorBest = lifetimeCollateralValueBest * (1 - costOfRecovery);
double lifetimeCollateralForMonthCorOptimistic = lifetimeCollateralValueOptimistic * (1 - costOfRecovery);
double lifetimeCollateralForMonthCorDownturn = lifetimeCollateralValueDownturn * (1 - costOfRecovery);
double min_gvalue_glevel = Math.Min(guaranteeValue, guaranteeLevel * monthLifetimeEAD);
double gLgd_gPd = (1 - guarantorLgd * guarantorPd);
double multiplerMinCollBest = (gLgd_gPd * min_gvalue_glevel) + lifetimeCollateralForMonthCorBest;
double multiplerMinCollOptimistic = (gLgd_gPd * min_gvalue_glevel) + lifetimeCollateralForMonthCorOptimistic;
double multiplerMinCollDownturn = (gLgd_gPd * min_gvalue_glevel) + lifetimeCollateralForMonthCorDownturn;
///
//xxxxxxxxxxxxxxxxxxxxxxxx
double creditIndexHurdle = 0;
try { creditIndexHurdle = Convert.ToDouble(impairmentAssumptions.FirstOrDefault(x => x.Key == ImpairmentRowKeys.CreditIndexThreshold).Value); } catch { }
//try { creditIndexHurdle = Convert.ToDouble(impairmentAssumptions.FirstOrDefault(x => x.Key == ImpairmentRowKeys.CreditIndexThreshold).Value); } catch { };
double ifCreditIndexHurdleBest = 0;
if (monthCreditIndexBest > creditIndexHurdle)
{
ifCreditIndexHurdleBest = ((1 - unsecuredRecoveriesDownturn) * multiplerMinCollBest) + (unsecuredRecoveriesDownturn * monthLifetimeEAD);
}
else
{
ifCreditIndexHurdleBest = ((1 - unsecuredRecoveriesBest) * multiplerMinCollBest) + (unsecuredRecoveriesBest * monthLifetimeEAD);
}
double ifCreditIndexHurdleOptimistic = 0;
if (monthCreditIndexOptimistic > creditIndexHurdle)
{
ifCreditIndexHurdleOptimistic = ((1 - unsecuredRecoveriesDownturn) * multiplerMinCollOptimistic) + (unsecuredRecoveriesDownturn * monthLifetimeEAD);
}
else
{
ifCreditIndexHurdleOptimistic = ((1 - unsecuredRecoveriesBest) * multiplerMinCollOptimistic) + (unsecuredRecoveriesBest * monthLifetimeEAD);
}
double ifCreditIndexHurdleDownturn = 0;
if (monthCreditIndexDownturn > creditIndexHurdle)
{
ifCreditIndexHurdleDownturn = ((1 - unsecuredRecoveriesDownturn) * multiplerMinCollDownturn) + (unsecuredRecoveriesDownturn * monthLifetimeEAD);
}
else
{
ifCreditIndexHurdleDownturn = ((1 - unsecuredRecoveriesBest) * multiplerMinCollDownturn) + (unsecuredRecoveriesBest * monthLifetimeEAD);
}
double maxCreditIndexHurdleBest = Math.Max(1 - (ifCreditIndexHurdleBest) / monthLifetimeEAD, 0);
double maxCreditIndexHurdleOptimistic = Math.Max(1.0 - (ifCreditIndexHurdleOptimistic) / monthLifetimeEAD, 0);
double maxCreditIndexHurdleDownturn = Math.Max(1.0 - (ifCreditIndexHurdleDownturn) / monthLifetimeEAD, 0);
double minMaxCreditIndexHurdleBest = Math.Min(maxCreditIndexHurdleBest, 1.0);
double minMaxCreditIndexHurdleOptimistic = Math.Min(maxCreditIndexHurdleOptimistic, 1.0);
double minMaxCreditIndexHurdleDownturn = Math.Min(maxCreditIndexHurdleDownturn, 1.0);
double lifetimeLgdValueBest = monthLifetimeEAD == 0 ? 0 : minMaxCureRateResultBest * minMaxCreditIndexHurdleBest;
double lifetimeLgdValueOptimistic = monthLifetimeEAD == 0 ? 0 : minMaxCureRateResultOptimistic * minMaxCreditIndexHurdleOptimistic;
double lifetimeLgdValueDownturn = monthLifetimeEAD == 0 ? 0 : minMaxCureRateResultDownturn * minMaxCreditIndexHurdleDownturn;
var newRowBest = new LifetimeLgd();
newRowBest.ContractId = contractId;
newRowBest.PdIndex = pdGroup;
newRowBest.LgdIndex = segment + "_" + productType;
newRowBest.RedefaultLifetimePD = redefaultLifetimePd;
newRowBest.CureRate = cureRates;
newRowBest.UrBest = unsecuredRecoveriesBest;
newRowBest.URDownturn = unsecuredRecoveriesDownturn;
newRowBest.Cor = costOfRecovery;
newRowBest.GPd = guarantorPd;
newRowBest.GuarantorLgd = guarantorLgd;
newRowBest.GuaranteeValue = guaranteeValue;
newRowBest.GuaranteeLevel = guaranteeLevel;
newRowBest.Stage = loanStage;
newRowBest.Month = month;
newRowBest.Ecl_Scenerio = ECL_Scenario.Best;
newRowBest.Value = lifetimeLgdValueBest;
newRowBest.LifetimeEad = monthLifetimeEAD;
_lifetimeLGD.Add(newRowBest);
var newRowOptimistic = new LifetimeLgd();
newRowOptimistic.ContractId = contractId;
newRowOptimistic.PdIndex = pdGroup;
newRowOptimistic.LgdIndex = segment + "_" + productType;
newRowOptimistic.RedefaultLifetimePD = redefaultLifetimePd;
newRowOptimistic.CureRate = cureRates;
newRowOptimistic.UrBest = unsecuredRecoveriesBest;
newRowOptimistic.URDownturn = unsecuredRecoveriesDownturn;
newRowOptimistic.Cor = costOfRecovery;
newRowOptimistic.GPd = guarantorPd;
newRowOptimistic.GuarantorLgd = guarantorLgd;
newRowOptimistic.GuaranteeValue = guaranteeValue;
newRowOptimistic.GuaranteeLevel = guaranteeLevel;
newRowOptimistic.Stage = loanStage;
newRowOptimistic.Month = month;
newRowOptimistic.Ecl_Scenerio = ECL_Scenario.Optimistic;
newRowOptimistic.Value = lifetimeLgdValueOptimistic;
newRowOptimistic.LifetimeEad = monthLifetimeEAD;
_lifetimeLGD.Add(newRowOptimistic);
var newRowDownturn = new LifetimeLgd();
newRowDownturn.ContractId = contractId;
newRowDownturn.PdIndex = pdGroup;
newRowDownturn.LgdIndex = segment + "_" + productType;
newRowDownturn.RedefaultLifetimePD = redefaultLifetimePd;
newRowDownturn.CureRate = cureRates;
newRowDownturn.UrBest = unsecuredRecoveriesBest;
newRowDownturn.URDownturn = unsecuredRecoveriesDownturn;
newRowDownturn.Cor = costOfRecovery;
newRowDownturn.GPd = guarantorPd;
newRowDownturn.GuarantorLgd = guarantorLgd;
newRowDownturn.GuaranteeValue = guaranteeValue;
newRowDownturn.GuaranteeLevel = guaranteeLevel;
newRowDownturn.Stage = loanStage;
newRowDownturn.Month = month;
newRowDownturn.Ecl_Scenerio = ECL_Scenario.Downturn;
newRowDownturn.Value = lifetimeLgdValueDownturn;
newRowDownturn.LifetimeEad = monthLifetimeEAD;
if (newRowBest.Value <= 0 && newRowOptimistic.Value <= 0 && newRowDownturn.Value <= 0)
{
//break;
}
//Console.WriteLine($"{month} - {newRowDownturn.Value}");
_lifetimeLGD.Add(newRowDownturn);
month++;
}
//}
}
catch (Exception ex)
{
Log4Net.Log.Error(ex);
}
}
lock (lifetimeLGD)
lifetimeLGD.AddRange(_lifetimeLGD);
}
private double ComputeMonthlyEclValue(List <LifeTimeObject> lifetimePds, List <LifetimeEad> lifetimeEads, LifetimeLgd row, string contractId, string pdGroup, int month)
{
double lgdValue = row.Value;
double pdValue = GetLifetimePdValueFromTable(lifetimePds, pdGroup, month);
double eadValue = GetLifetimeEadValueFromTable(lifetimeEads, contractId, month);
//if(contractId.Contains("10123603239201"))
//{
// var cc = 0;
//}
if (pdGroup == ECLStringConstants.i.ExpiredContractsPrefix)
{
pdValue = 1;
}
double monthlyEclValue = pdValue * lgdValue * eadValue;
return(monthlyEclValue);
}