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); }