Hodrick Prescott Filter in Excel

Hodrick Prescott (HP) filter is a method to decompose a time series into two components, a long-term trend and a residual. The residual is interpreted as a cyclical component. The formula is as follows:

Eq1

where y is the time series we are considering and g is the growth rate, while c is the cyclical part of the time series. The exact specification of the HP filter is:

Eq2

While it looks complicated the above equation has an intuitive interpretation. The objective of the minimization is to find a trend component g that does not deviate from observed time series y too much. This is the first part of the minimization function. The second part of the equation penalizes us for having an erratic long term growth component. The penalization is weighed by the lambda parameter which the user should specify. Typically in economic literature lambda of 129,600 is used for monthly time series, 1,600 for quarterly time series, and 6.25 for annual frequency.

To implement HP filter in excel we need to solve below system of equations:

Eq3

where I is in identity matrix while F is a Pentadiagonal matrix

Eq4

solving for g we get:

Eq5

For a small problem we can tackle it directly via built in excel functions. For example using 25 quarterly observations of Singapore’s seasonally adjusted GDP in range C6:C29 and a Lambda parameter of 1600 we can name the lambda range as “Lambda” and the GDP time series as “Y_t”

Eq6

In range I5:AF28 we can input matrix F and in range AI5:BF28 we can input the identity matrix I

Eq7

We can calculate Lambda*F matrix in range I33:AF56

Eq8

And we can finally calculate in range AI33:BF56 the matrix needed for inversion and name the range as LambdaF_plus_I

Eq9

To calculate the HP trend component we simply use MINVERSE function in excel to find the inverse of LambdaF+I matrix and MMULT function to multiply the inverse by observed GDP series. The cyclical component is simply y-g

Eq10

Above was obviously a toy example outlining basic calculations with some unnecessary steps included to help with the exposition. If we wish to implement HP filter in excel we will need to build a systems of equations solver in VBA. First thing to notice is that the matrices are sparse so we can implement more efficient system of equations solver instead of relying on computationally expensive matrix inversion. Our problem can be stated as a Pentadiagonal system of equations. Exposition below borrows heavily from Numerical Mathematics and Computing by E.Cheney and D. Kincaid.

A Pentadiagonal system of equations is a system of equations with following representation:

Eq11

The pseudo code to solve this system is given by E.Cheney and D. Kincaid. It is an excellent book and worth having nearby when implementing numerical methods.

In VBA the function looks like this:

Eq12

To use the function simply enter HP_Filter(YourDataRange, Lambda) and press Ctrl+Shift+Enter to input the array function. For example using Singapore’s seasonally adjusted GDP dating back to 1980 we first take the log of the time series. Then use our HP_Filter function with lambda of 1600 to estimate the trend. We then transform the trend back by taking the exponent. The result is plotted below.

Eq13

To check the cyclical component we simply subtract the growth from observed GDP (both in log terms) to see what the current output gap is.

Eq14

Finally as a check we run hpfilter in matlab to check our results.

Eq15

Our output for the same first 15 observations matches.

Eq16

Some useful resources:

  1. Wikipedia entry for HP Filter: https://en.wikipedia.org/wiki/Hodrick%E2%80%93Prescott_filter
  2. Hyeongwoo Kim note on HP filter: http://www.auburn.edu/~hzk0001/hpfilter.pdf
  3. Numerical Mathematics and Computing by E.Cheney and D. Kincaid: http://www.amazon.com/gp/product/1133103715/ref=pd_lpo_sbs_dp_ss_3?pf_rd_p=1944687542&pf_rd_s=lpo-top-stripe-1&pf_rd_t=201&pf_rd_i=0534389058&pf_rd_m=ATVPDKIKX0DER&pf_rd_r=1EEFGACEPSCX192V5M5M
Advertisements

4 thoughts on “Hodrick Prescott Filter in Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s