Hodrick-Prescott (HP) filtering instructions for Excel by use of by the Excel HP filter add-in

Installation of the Hodrick-Prescott (HP) filter add-in into Excel on your computer

You must have access to a computer with Excel.

Create a folder for your stuff related to your macro course and give it a name, say “MACRO”.

Download the HP filter add-in for Excel. Save the file in your “MACRO” folder.

Now you have the filter on your computer, but still need to activate it in the relevant Excel file (sheet). It is done as follows:

• Open the Excel workbook the contains the data you want to filter. This could be an Excel data file for one of the exercises provided to accompany the book.
• Click the "File" tab (upper left corner).
• In the drop down menu click "Options" (lowest), and in the new box appearing click "Add-Ins" (in menu to the left, second lowest).
• A new box appears. In the “Manage” dropdown menu of this box (at the very bottom of it), choose "Excel Add-Ins" and then click "Go".
• A new box comes up. If this already offers “Hodrick-Prescott Filter (HP-Filter)” among the different add-ins, you can simply select this and then click "OK" (if the Excel workbook you are in lies in the folder in which you have the filter this should be so). If not click "Browse" and find and open the folder where you have put the filter (MACRO), select “Hodrick-Prescott Filter (HP-Filter)” and then click "OK".

Now you should be able to use the HP Filter in the workbook you are in. We next give instructions on how to use the add in.

It is our experience that sometimes when reopening a sheet after some time, the filter does not work. Try then to go through the bullet pointed steps once again.

HP Filtering

In your Excel file you have the appropriate data (a time series) placed in a column – not in a row!

Say the data contain a time series for GDP in volume.

You will then probably first want to transform it into natural logs in a new column (you know how to do that in Excel) before finding the trend values.

Be sure to have the HP-filter connected to your sheet as explained above.

Let’s say that the series you want to filter, i.e., to find a trend for, is In(GDP) on quarterly basis and that this series lies in the cells D4 to D112. Beside these cells you select an area (a column) of the same size as the data series, say E4 to E112. This is where your filtered series will go. You select it, by putting the cursor in the top cell (E4), click the mouse and, without un-clicking, move the cursor down to the bottom cell (E112) and then un-click.

Now, having selected your column for the filtered data, you go to the formula field (the fx field at the top of the sheet) and enter:

=HP(

After the parenthesis you select the series you want to filter. Now you will have in the formula field:

=HP(D4:D112

You put a semicolon (;), you type the value of the smoothing parameter usually referred to as lambda (for quarterly data, this parameter is usually set to 1600, and for annual data to 100), and finally you put an end-bracket so that now you have in the formula field:

=HP(D4:D112;1600)

It is now tempting to hit "Enter", but do not do that! Instead, press "Ctrl+Shift+Enter" and the filtered series should show up in the selected area, here E4 to E112.

This filtered series gives the trend of the actual series. You can plot it in a diagram along with the actual series and inspect the figure to see if the trend looks reasonable.

You can then compute the deviations from trend to find the cyclical component. For instance, if the series you have filtered is ln(GDP), you can compute the "output gap" simply by subtracting the trend series from the actual series. If you have 'actual ln(GDP)' minus 'trend ln(GDP)' you will probably want to multiply by 100 to have the output gap in percent.