# How can I transfer an ARMAX model in Excel in order to forecast future values?

I am currently trying to set up an Excel based tool, that alows to predict future values based on an ARMAX model, previously set up in SPSS.

The Excel tool contains the coeffienients, calculated by SPSS, the data used for building the model (dependent variable(y) & explanatory variables(x)) and the test period.

My goal is to predict future values y(t+1) simply by entering the values for x(t+1) in the Excel sheet.

This worked quite well for an AR(2) model, but I am struggling when it comes to a more complex model as ARMAX(3,2).

The function that worked well for AR(2) was based on:

I do not know how to set up a similar transfer function for an ARMAX(3,2) model. The problem how to include the MA part and the denominator part of the explanatory variables.

Do I need to add more information from the SPSS output (residuals) or can I calcluate the residuals based on the data in the Excel sheet as described above?

Thanks a lot for your support!

The model residuals alone won't help in your case. The error terms in the moving averaged portion are unobserved so you need some way of estimating them. It would be better if you could retrieve estimated error terms from the SPSS function directly rather than do it yourself. Nevertheless, I can tell you how to estimated the error terms in case you're interested.

To help understand the logic, start first with a normal Moving Average model, for example, an MA(2). The model can be written as $$y_t = \mu + \varepsilon_t + \theta_1 \varepsilon_{t-1} + \theta_2 \varepsilon_{t-2} \;\;\;\;\;\;\;\;\;\;\;\;\;\;\;(1)$$

where $\varepsilon_t \stackrel{iid}{\sim} N(0,\sigma)$.

Notice that equation $(1)$ can be re-written as $$\varepsilon_t = y_t-(\mu + \theta_1 \varepsilon_{t-1} + \theta_2 \varepsilon_{t-2})$$ Using the above equation, if you knew $\varepsilon_{0}$ and $\varepsilon_{-1}$ you could calculate $\varepsilon_1$, which would give you enough information to calculate $\varepsilon_2$, then $\varepsilon_3$, so and and so forth all the way up to $\varepsilon_t$. Then you would be able to calculate $$E[y_{t+1}|y_1,y_2,...,y_{t}] = \mu + \theta_1 \varepsilon_{t} + \theta_2 \varepsilon_{t-1}$$ $$VAR[y_{t+1}|y_1,y_2,...,y_{t}] = \sigma^2$$ which, in combination with the assumptions of the model, gives you enough information to create point forecasts and corresponding confidence intervals for $y_{t+1}$. This type of recursion is what's needed to calculate time series models with MA components.

In reality we will never know the values of $\varepsilon_{0}$ and $\varepsilon_{-1}$ (or any parameter value for that matter) with 100% certainty. So often, we just assume $$\varepsilon_{0}=\varepsilon_{-1}=0$$ which is the best assumption we can make since $E[\varepsilon_t]=0$.

This is easily extended to the MA(q) model where it is common to assume that $\varepsilon_0=\varepsilon_{-1}=...=\varepsilon_{-q+1}=0$

This logic is also easily transferred over to an ARMAX(p,q) model. You can write the model as

$$y_t = \mu + \phi_1 y_{t-1} + ...+ \phi_p y_{t-p} + \beta_1 x_{t} + ...+ \beta_k x_{t-k+1}+ \varepsilon_t + \theta_1 \varepsilon_{t-1}+ ... + \theta_q \varepsilon_{t-q}\;\;\;\;(2)$$ Let $m = \max(p+1,k)$. Using an unconditional likelihood approach (as opposed to an exact likelihood) the first $y_t$ which you are able to predict using past data is $y_m$. If you tried to plug in $y_{n}$ where $n<m$ on the left hand side, then at least one $Y$ or $X$ term on the right hand side would be indexed at time $t<1$ for which you have no data.

similar to the case with the MA(2) model, if you assume $$\varepsilon_{m-1}=\varepsilon_{m-2}=...=\varepsilon_{m-q}=0$$

then you can use equation $(2)$ to calculate $\varepsilon_{m}$ through $\varepsilon_{t}$ recursively and obtain the expected value and variance of $y_{t+1}$.

## Some Caveats to be Aware of

It is generally better to estimate and forecast a model using the same statistical package (i.e. do everything in either SPSS or Excel rather than estimate the model in one and forecast it in another). This is especially true in your example since you take the parameter estimates and simply substitute them for the true parameter values in Excel. In doing so you disregard the standard error of your parameter estimates which will cause you to underestimate the width of your confidence intervals.

There is also the issue of conditional vs exact likelihood which refers to different ways of estimating ARMA type models. In my example I took a conditional likelihood approach while most statistical packages use an exact likelihood. The exact likelihood is more complicated and usually involves transforming the model into a state-space form. Under this approach, something similar to a Kalman filter would be used to generate forecasts which may result in slightly different forecasts. It also makes the incorporation of parameter estimate standard errors that much more difficult (a reason why most people use built in functions to forecast ARMA models)

That said, if you have a reasonably large data set and your model is stationary and invertible, than the above issues would be minor and using the parameter estimates and forecasting methodology as I did above would probably be "close enough" to serve as a good approximation.

• btw, I have never used SPSS. I know that you can easily obtain forecasts and confidence intervals for ARIMAX with 4-5 lines of code in R using the Forecast package. Commented Jan 20, 2016 at 8:28