Statistical Model

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

What You’ll See
This spreadsheet has four books.

The first, called “Analysis 2″ is a chart containing the distribution of certain events over our existing Loan Ladder. The events considered reflect the repayment progression ranging from “Paid on time” to “Paid >56 Days Late” to “Outstanding”.  This analysis includes All data from La Ceiba’s Operations through May 10th, 2011.

The second book is called “Dice”. This is a stopgap page to help produce results later on, since there was no excel formula known to perform the function needed. The probabilities calculated in “Analysis 2″ are reflected here in lists of 100, with each event repeated the number of times corresponding to their probability.

Third is the actual Statistical Model for our L500 Loan level, the introductory level, to be explained below. The fourth page is exactly the same, just with the next level up the ladder.

How It Works
The Statistical Model is designed to calculate, in a statistically significant way, the probability that La Ceiba will run a profit given a random sampling of between one and fifty clients.

Under “No. of Clients” (in Cell A3),  you can select how many clients you wish to sample. It is currently set to 3 Clients, but again can be any number between 1 and 50.  In the next column, it shows the repayment of the selected number of clients. This is performed by randomly selecting an event from the list of 100 in the “Dice” Book. Since the events are listed in statistically accurate quantities, the outcomes listed are probable given our current rates.

The next two columns show the finances associated with the selected number of clients. For example, 3 Clients receiving L500 require $79.61 USD.  at a given interest rate and period, it will produce the Expected return, the Actual return (dependent upon the randomly generated client events), the profit from the given scenario, and a Proportion showing the profit as a % of the Principal investment.

If All clients pay back, the proportion should equal the interest on the the Loan.

Results
I performed a study with 3 clients and their statistically probable repayment outcomes.  I selected 3 clients because it was my understanding that 3 new clients are permitted to be brought into La Ceiba per month. The statistical model shows that  if one of those three clients does not pay, La Ceiba does not make a profit. After 30 trials, the model produced a profitable scenario only 5 times. This model has roughly a 17% probability of profitability.

Conclusion
The study shows that, while this is our introductory loan level, it is very challenging given current repayment rates to operate at a profit. Some modification needs to be made to our client selection method, or to our other loan levels, to make our institution profitable.