ABC credit union is a small financial services co-operative. Its members save with it and it
lends out these savings to other members. It aims to make enough income from loan
interest to run the organisation and pay its members a dividend on their savings.
Clearly, a key issue is ensuring that its loans are repaid in full. To that end, it is important
that it understands the risk factors that make a loan more likely to go into default. It has
supplied data on its previous loans, in the attached Excel file, and would like you to identify
the factors that increase the risk of a default. It is particularly interested in the impact of
the affordability of loan repayments on the likelihood of default
More specifically, you should:
1. Use logistic regression to identify the impact of affordability on the likelihood of
default (note that the credit union has not supplied information about members’
income directly, but instead has supplied a measure of deprivation of the local area
in which the member lives, that you must use as a proxy);
2. Use logistic regression more generally to identify the impact of each of the factors
included in the data on the likelihood of default;
3. Develop a model that will predict the likelihood of a default for specific members;
4. Use the model to give your best estimate of the likelihood of default for:
a. A 23 year old man borrowing £2,000 at 12.7% annual percentage rate (APR),
repaying monthly over 3 years, if the man has been a member for 2 years and
lives in a deprived area (say IMD = 50).
b. A 49 year old woman borrowing £500 at 26.8% APR, repaying weekly over a
year, if she has been a member for 10 years and lives in an affluent area (say
IMD = 6).
5. Write a report on what you have done, including recommendations on how the
credit union can reduce its level of bad debt. Include your spreadsheet with your
report and explain how it can be use it to predict bad debt in a particular case.
Notes on the data
These are real data, so you may expect to find some problems (missing or implausible
values, for example, or the data not in the format you need) and will need to decide how to
deal with this. You will have to make assumptions and your report should be clear what
The data may be interpreted as follows:
Loan Number Self-explanatory
Member Status A flag: A = active adult member; B = bad debtor; L = former
member who has now left; Z = dormant member (no
outstanding loan and no transactions on any account for at
least a year).
DOB Date of Birth.
DOJ Date the member joined the credit union..
Sex Male (M) or Female (F).
IMD Index of Multiple Deprivation: A measure of the level of
deprivation of a neighbourhood, higher numbers being
more deprived. This is a proxy for the likely affluence of
Date Loan Granted Self-explanatory.
Balance Pre Last Loan The balance of the member’s loan account before the last
loan was granted. This will be non-zero if the member was
granted another loan before the previous one was fully
repaid (a top up loan).
Last Loan Value The amount of the last loan.
Repayment Frequency The frequency of repayments: weekly (W), fortnightly (F)
or monthly (M).
Repayment Amount The amount repaid (including interest) at each repayment.
Repayment Term Number of scheduled repayments.
Days In Arrears The number of days since the last payment was due. Note
that loans that have been written off (member status ‘B’)
will not be shown as in arrears but are still bad debts.
Arrears Value The amount by which payments are overdue.
Balance The current loan balance.
Rate (%) The interest rate. Note this is given as simple interest (so
24% is equivalent to 2% a month, which is what the credit
union charges), whereas the interest charged is compound
(so 2% a month is 26.8% as an APR). Look up APR if you
are not sure what this means There is, of course, an exact
relationship between the loan amount, the loan duration,
the repayment frequency, the repayment amount and the
interest rate, so if any 4 of these are know the 5th can be
calculated. Usually, the loan amount, duration, interest
rate and repayment frequency are agreed between the
credit union and the member, and the repayment amount
calculated. Occasionally, the repayment amount may be
agreed and the duration calculated.
You will note that there is no indicator for a bad debt, so you will need to create one. A loan
of any member with Status ‘B’ has been written off by the credit union and is, therefore, a
bad debt. A loan one or more days in arrears is also technically in default but there are all
sorts of reasons why this can happen for perfectly innocent reasons. Usually, the credit
union does not consider loans to be in default until they are 90 days or more in arrears, so
these too can be treated as a bad debt.