Your help would be appreciated!I'm trying to calculate the net returns of a series loans, at different stages of completion. I've categorised the loans into open loans (repaying & late loans) and closed loans (repaid & default).For the Closed Loans, I have put the loans into a cashflow and used IRR methodology to approximate the net returns.For the Open Loans, I have put these loans into a similar cashflow, assuming they complete as scheduled and don't default.Default AssumptionI'm trying to come up with a sensible default assumption to discount the open loans, based on the default performance of the previous years.Tried Methodology:-Proposed Methodology enter image description here This methodology attempts to calculate the impact on net returns of defaulted loans across all years (2010-2016). We then take the average and compare against the actual default and discount the difference from the overall net returns. As the majority of loans are open on in the later years, particularly 2016, the impact of this discounting is great.Calculate the % of closed (repaid+ defaulted) loans. In 2010 allloans are closed so 100%. In 2016 only 7% of loans are closed. Usethis to calculate a weighting for averaging. Hint: the loans in 2010are more influencial in the average that 2016 because all loans areclosed.The overall net returns is calculated by a summation of:a. % of repaid loans x repaid loans net returns b. % of defaulted loans x  defaulted loans net returns (negative number)c. % of late loans x late loans net returns d. % of repaying loans x repaying loans net return Take the weighted average, using the figures calculated in 1of the (% of defaulted loans x defaulted loans net returns. Thissingle figure gives an effective default rate on the overall netreturns of the total portfolio. Use this new estimate to discount the open loans by difference between the new calculated average and the actual default.-I can't get my head around this calculation and whether I'm way off. Be good to get your thoughts. I've attached some data to play around with.Cheers, Iain
Find a Default Assumption for a series of Forecasted Loans
finance