Predict next month's loan balance from historical data

Question

I work for a bank. Every month I get a list of 10,000 customers of the bank with the outstanding loan balance for each customer. I add them up and report the total outstanding balance every month. I have data for past 24 months.

I am asked to give a rough prediction of the next month's total outstanding balance. I am not a statistician, hence need some help. So far ,I have below ideas, can someone comment on pro/cons of each?

1. Compute the average monthly % growth over last 6 months on Total balance; Multiply current balance by % growth to predict next month balance

2. Do the same at an individual customer level, that gives us 10,000 predicted balances for next month. Add them up to get total balance

3. Are there any time series techniques I can use here?

4. Can I find the answer by some simulation techniques?

Answer 1

You have quick and easy but rough predictions:

• The "meteo method": next month will be the same as last month. No as bad as you may think, and you can even replace last month by an average of the last 6 month.

• Linear regression: the meteo method will fail if there is a constant growth. Make a linear regression in function of time $x(t) = a \cdot t + b$. Your forecast will be $x(today+1)$.

• Exponential regression: use a regression on a growth in percentage (exponential growth). You can find the best $a$, $b$ fitting $x(t) = a \exp(b \cdot t)$, or the best $c$, $d$ to fit $log(x(t)) = c \cdot t + d$. These are the same formula in disguise and you can use a linear regression for the last one (fit $y(t) = log(x(t))$ instead of $x(t)$).

  Note that this a "scientific" version of you method 1.

• Exponential smoothing: It is an average giving more weight to the last values. There is a technical trick that makes computation specially easy if you have to forecast every month. In reference with the wikipedia page, take $\alpha = 1/6 = 0.1667$ for a 6 month history.

  In the future, you will ba able to add a trend (Double exponential smoothing) and a seasonality (Holt-Winter model).

I would avoid method 2, consisting in adding forecast of individual account. This method works when every thing is linear so that errors cancel each others. When the the process is not linear, you'll have a systematic bias which will add together.

I also mention that your main challenge will more probably be the seasonality than the main trend.