0
$\begingroup$

I have a data set consisting of time series information. The dimensions of the data have 39 rows and 38 columns. The data is comprised of loan repayments up until the present date, so each column represents the month of the loan disbursement and each row represents the amount of that loan that was repaid in each consecutive month after disbursement. Because the data goes up to the present day, I there are a lot of NA's present for the more recent loans. Specifically the NA's are present in a systematic way along the diagonal. For example, here is a reduced illustration of how the data appears:

 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 am using vector autoregression to forecast future loan repayment amounts for each month in the future.

My problem:

I can't include both the endogenous and exogenous variables to forecast the NA in the endogenous variable

Here's a sample in R of four columns showing what the real data looks like to illustrate the problem:

samp <- structure(c(2.6, 2.2, 2.3, 2.1, 2.2, 2.3, 2.2, 2.3, 2.3, 2.3, 
2.2, 2.3, 2.1, 1.9, 2.1, 2.1, 2.1, 2.2, 2.2, 2, 2.3, 2.1, 2, 
2, 1.9, 1.7, 1.9, 1.9, 2, 1.9, 1.9, 2, 1.9, 2, 1.8, 1.7, 1, 0.6, 
0.4, 2.7, 2.4, 2.1, 2.5, 2.3, 2.4, 2.3, 2.2, 2.4, 2.1, 2.2, 2.3, 
1.9, 2.2, 2, 2.3, 2.2, 2, 2.1, 2, 2, 2, 1.8, 2.1, 1.6, 1.9, 1.8, 
1.9, 1.9, 2, 1.9, 1.9, 1.9, 1.7, 1.8, 1.7, 1.1, 0.8, 0.4, 3, 
2.3, 2.3, 2.2, 2.4, 2.3, 2.2, 2.2, 2, 2.2, 2.2, 2.2, 2, 2.1, 
2.2, 1.9, 1.9, 2, 2.1, 2, 1.8, 1.9, 2, 1.6, 1.9, 1.8, 1.9, 1.7, 
2, 1.8, 1.8, 1.9, 1.7, 1.8, 1.8, 1.2, 1, 0.7, NA, 2.8, 2.3, 2.3, 
2.4, 2.5, 2.2, 2.4, 2.2, 2.2, 2.3, 2.2, 2.3, 2, 2.1, 2, 2, 2, 
1.8, 2, 1.9, 1.9, 1.9, 1.7, 1.9, 1.8, 1.9, 1.8, 1.9, 1.8, 1.8, 
1.8, 1.7, 1.8, 1.8, 1.6, 1.5, 0.9, NA, NA), .Dim = c(39L, 4L), .Dimnames = list(
    NULL, c("201412", "201501", "201502", "201503")))

Just say I want to forecast the NA in the final row for the column 201502 and when forecasting I would like to use the endogenous variable (so the information already present in the column 201502 and at least one exogenous variable (in this case the preceding month 201501). Using the vars package in R the VAR function doesn't accept NA values. This means that in order to use the endogenous and exogenous variable, I need to exclude the final row and build the model using rows 1:38 to predict the final NA in row 39 for column 201502. This is problematic for two reasons, 1) I think the information present for row 39 in the exogenous variable would be useful when making the prediction for row 39 in 201502 so I would like to use it. and 2) For the real date, the more recent columns have a lot of NA's, for example the final column only has information for three months (so three data points and 36 NA's) so of course here there is no where near enough data to build a reasonable model.

To overcome this, I've been building the model using exogenous information only, for example, in the data example given here, using the information in columns 201412 and 201501 to predict the final NA for column 201502 However, in this case I'm not using any of the information in the endogenous variable which is obviously very useful for forecasting, at least for the cases where enough data is available.

Is it possible to consistently use the endogenous and exogenous information for all months (so all row 1:39) in spite of the presence of NA's in the endogenous variable??

Any information or pointers in the right direction would be greatly appreciated :) thanks!

$\endgroup$
1
$\begingroup$

I feel we need to understand the nature of those missing values and then try to come up with a solution.

In a VAR setup all variables are theoretically related in some way. How are all your loans related? Do they pertain to the same customer? If so, you might do better by summarising that information computing a new variable related with the puntuality or efficiency that the customer pays its loans. Say, for instance, taking the average (across all customers' loans) ratio of effective payment to the due payment by each month. Then you could average across all loans for each month and this new variable should not contain NAs.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.