Beispiel #1
0
        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);
        }