# forcasting future cash flow based upon previous data

Given this time series data:

tbl <- tibble(month = c('jan', 'feb', 'march', 'april'),
mob1 = c(20, 24, 27, 25),
mob2 = c(15, 17, 19, NA),
mob3 = c(11, 12, NA, NA),
mob4 = c(8, NA, NA, NA))


I would like to predict the values for each of the NA's. the forecast for each month should be based upon the available data for that specific month, and also based upon the patterns present in the other months. Is there an analytical technique that I could use to make these predictions? In reality my data is much larger than the example given.

EDIT: DESCRIPTION OF THE REAL DATA

The real data consists of 39 months of data (so 39 columns), for each month from mid 2013 to the present (55 + rows). The dataset is comprised of loan repayment data. Specifically, the percentage of a loan that gets repaid every month until the whole loan is repaid. Cases, (rows) consist of sum of the cost of all the loans that were dispersed in that month., then columns mob1...mob39 consist of the percentage of the amount dispersed in that month that gets repaid. For example, the percentage of all the loans that were dispersed in January 2014 could be 3.3% after one month, then 3.1% after two months, and so on until the end of the loan term, and in theory the percentages all add up to 100%. In reality the percentage that gets repaid each month gradually decreases, starting from about 3.5% in month1 to about 1.3% in month39 (on average) so there is no seasonality to the data data, but rather each case has a gradually decreasing percent of the total loan amount across the 39months.

My internet search shows the ARIMA model to be something I could possibly use (at least that is what keeps popping up!), however, my data doesn't have seasonality to it and thus ARIMA might be an overly complicated solution, and from what I understand the ARIMA model doesn't take advantage of the patterns present in different cases in order to predict the NA's in said case. What I mean by this is, for example, the I would like to forecast the NA months for February, using not only the existing data for February, but also the data from the preceding January time series, as both show similar patterns. I'm not sure if that is common practice but to be it seems intuitive to do so.

Any help that can be given would be greatly appreciated!

• How months do you have in reality? The answer will depend on that. – Skander H. Jul 24 '18 at 17:36
• @Alex I have 39 months. i've edited my answer to give a description of the real data, hopefully that makes the problem posed clearer. – steve zissou Jul 24 '18 at 19:17
• There's a lot of literature on loan prepayments. You may decide to ignore it, of course. Prepayments are always modeled as the prepayment rate, which is prepaid amount over scheduled principal ending balance. Depending on what kind of a loan you're working with there are different approaches, e.g. commercial real estate vs. auto loan, with or without prepayment protection, kind of protection, full prepayments vs partial, voluntary/involuntary, senior debt vs subordinate, fixed rate vs adjustable, draws, extensions, refinancing and the list goes on and on – Aksakal Jul 24 '18 at 19:18

Not a lot of data here, so you'll get a pretty generic answer.

If your data is a time series (related to itself over time) then there are methods for identifying trends and seasonality or cycles to help with prediction. Check out: https://otexts.org/fpp2/ for a bunch of techniques.

If your data isn't a time series (doesn't relate to itself and only to other variables), or if another variable can be used as an alias for time, then you can use a more simple technique such as simple linear regression.

• Thanks, that is a really nice book. I guess given my question, I deserved a generic answer, but what i really need is a pointer in the right direction regarding which analysis technique to use. Unfortunately I don't have the time to go through the book :) – steve zissou Jul 24 '18 at 17:06
• We don't have enough data to determine which direction you should go. – Adam Sampson Jul 24 '18 at 18:05
• Unfortunately there isn't a one-size-fits-all method for these sorts of thing. If there was we wouldn't get paid to do the analysis. – Adam Sampson Jul 24 '18 at 18:06
• thank you for your patience! I have edited my question with a description of the real data. If you have any thoughts that would be amazing – steve zissou Jul 24 '18 at 19:16

If you have a large amount of historical records, not just 4 months, but several months of history, you could use vector autoregression to model this data.

• Thanks! This seems a lot like the sort of thing I need. However, can a VAR model incorporate the information in other cases and well of the lagged information for that said case. for example, in a VAR model I can see that you could forecast a future month for the January case based upon lagged payments, (y - 1, y - 2), however could one also incorporate the information present in other cases, for example the information from February into the model for January? – steve zissou Jul 24 '18 at 19:44
• @stevezissou yes. VAR tries to model the dependencies of entire vectors. If we were modeling just one series at a time we would use AR or ARMA models. – Skander H. Jul 24 '18 at 20:02