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 Assumption
I'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 all loans are closed so 100%. In 2016 only 7% of loans are closed. Use this to calculate a weighting for averaging. Hint: the loans in 2010 are more influencial in the average that 2016 because all loans are closed.
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 1 of the (% of defaulted loans x defaulted loans net returns. This single figure gives an effective default rate on the overall net returns 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
