New Loan Ladder Model

Click here to Download the spreadsheet in Microsoft Excel format. (Uses data current as of 5/10/11)

What You’ll See
There are 4 Books to this spreadsheet. Synthesis, New Model (Perfect Reps), New Model (Hyp Reps), and Current Model.

Synthesis shows the probability of repayment from previous analysis  (see “Analysis 2″). This is provided from the Data for the Loan Levels of 500L, 1000L, 1500L, and 2000L. For levels in between (750L, 1250L, 1750L) the average was taken of the higher and lower loan levels. Also shown is the Probability of no repayment, and the probability of no repayment for the first, second, and third subsequent repetition. For subsequent repetitions, the number next to “Rate” is the fraction of repayment. Currently it is 0.2, which means the first repetition probability of no repayment [P(nopayr1)] is 0.2 * P(nopay). For the second repetition, it is 0.2*P(nopayr1), and so on. Also on the Synthesis book is results from different “Rate” values. Currently it is set to 0.2, which is profitable on all models. A rate of 0.25 is not profitable with just one repetition, but is with two and three repetitions. The numbers are extrapolated, and you can experiment and see in the subsequent sheets how various rates affect profitability of the model.

The next sheet is “New Model (Perfect Reps),” which shows the basic lengthened ladder, as well as the new ladder with one, and two repetitions. Perfect Reps stands for Perfect Repetitions, in which, for subsequent repetitions, the probability of no repayment is zero.

The Following sheet (Hyp, or Hypothetical Repetitions), shows the same ladder but instead of the probability of no repayment being zero, it follows the formula in the Synthesis sheet. It also shows a potential 3 repetition Loan Program.

Lastly, the Current Loan Model, with its ladder of 500L increments and no repetitions.

The Conditions for modeling all of these scenarios are a hypothetical sample of 100 clients, which can be easily modified in your own experimentation. These 100 Clients are filtered through the loan ladder based on the probability of repayment, the cost of loans and the earnings are recorded (30% Interest rate assumed), and the end result shows total cost, total earnings, and the profitability.

Results
The extended Loan ladder, without repetitions, is not profitable given current repayment rates. We would be projected to lose 12% of principal–better than how we are actually performing, but still a loss over time. the “3 Repetitions” (2 subsequent to initial) is much more profitable than the “2 repetitions” loan. This, however, assumes perfect repetitions, or that all clients who can repay a loan can repay the loan again. A much more realistic model was required to show some clients not paying back on repetitions–at lower rates than the initial loan.

With Hypothetical Repetitions, (and a “Rate” Value of 0.2, from the Synthesis page), all levels with repetitions are profitable, but more repetitions increases the profit.

The current loan ladder, under the same modeling conditions, is not profitable, the model showing a loss of about 9%.

Conclusion
The “Rate” Value, and the related Table in the Synthesis sheet, shows that for repetitions to be effective, repayment rates have to be kept high, or non-repayment rates kept low. A system of incentives could help achieve this. Furthermore, the more repetitions means the higher the profit margins. With greater profits, we can more easily cushion the blow of high-value, high-risk clients when they occur, and still operate at a profit and provide this valuable service to our clients, as well as proving to our donors that we are a worthwhile investment.