How to Calculate the PSAR Indicator using Excel

Last Updated on November 28, 2022 by Mark Ursell

This Article and PSAR Calculation has been superseded by a new version. The new article is better and easier to follow. Check out the new article: How to Calculate the PSAR Indicator – New Version

The PSAR Indicator is a technical indicator that can be used to trade the financial markets. This article shows how the PSAR is constructed and how it can be calculated using Excel.

The PSAR is great for trading volatile swinging markets. The PSAR only moves in the direction of the trend.

The PSAR Indicator

The Parabolic SAR technical indicator consists of a series of dots, alternately above and below the price. In an uptrend, the dots are below the price and in a downtrend, the dots are above.

As the Parabolic part of the name suggests, the indicator is trying to find those incredible parabolic trends that move so fast and accelerate so quickly that big profits can be made.

How is the PSAR Constructed?

The PSAR was created by J. Welles Wilder and it is fascinating to look into the calculations that make up the indicator.  The indicator firstly identifies the market trend and then moves in that direction.  It does this by calculating the recent highest high or lowest low and then checks whether the most recent bar has exceeded this price.

When the market is trending strongly, the indicator moves faster to track the move.  It does this by using an acceleration factor that increases every time a new high or new low is made. The image shows how the rate of acceleration of the PSAR changes during a typical trend.

Using Excel to Calculate the PSAR

The PSAR can be calculated using Microsoft Excel or any similar spreadsheet software.  The indicator is constructed using the standard mathematical functions and the logical functions IF and AND.

Video: How to Calculate the PSAR Indicator using Excel

I have recorded a video to accompany this article. It is easier to understand the logic of the calculations if you watch the video in combination with this article.

Step 1 – Start with the Historical Price Data

Begin with a spreadsheet containing the historical price data that you want to use.  Insert this in columns A-F in the order: Date; Time; Open; High; Low, Close.

Step 2 – Add our Columns for the Indicator

Columns G-L will be used to calculate the indicator.  Insert the following titles: PSAR; Extreme Point; EP-PSAR; Acceleration Factor; EP-PSAR * Acc Fac; Uptrend-Downtrend.

Below the Acceleration Factor title insert 0.02 and 0.2 as shown in the screenshot below.

Step 3 – Enter our Initial Values

We will enter the initial values in row 4.  These values are:

G4 =E4              -The current low
H4 =D4              -The current high
I4  =H4-G4         -EP minus PSAR
J4  =J2                -Initial acceleration factor
K4 =J4*I4           -EP-PSAR multiplied by Acc Factor
I4  bull               -bull signifying an uptrend

Step 4 – Enter the Calculations

Now we need to enter the calculations for the indicator.  If you are not used to IF and AND statements these formulas might seem complicated. However, in reality they are quite straight forward.  It might help to watch the video to understand these formulas better.

G5 =IF(AND(L4=”bull”,G4+K4>E5),H4,IF(AND(L4=”bear”,G4+K4<D5),H4,G4+K4))
H5 =IF(AND(L5=”bull”,D5>H4),D5,IF(AND(L5=”bull”,D5<=H4),H4,IF(AND(L5=”bear”,E5<H4),E5,IF(AND(
I5   =H5-G5
J5  =IF(L5=L4,IF(J4=$J$3,$J$3,IF(AND(L5=”bull”,H5>H4),J4+$J$2,IF(AND(L5=”bull”,H5<=H4),J4,IF(AND(L5=”bear”,H5<H4),J4+$J$2,IF(AND(L5=”bear”,H5>=H4),J4,””))))),$J$2)
K5 =J5*I5
L5 =IF(G5<D5,”bull”,IF(G5>E5,”bear”,””))

 Step 5 – Copy the formulas to the cells below

The final thing to do is copy the formulas down to the cells below.  To do this, select cells G5:L5. Then double-click on the square at the bottom right corner of the highlighted cells.

Related Links

If you are interested in using Excel to backtest trading strategies my Ebook course: How to Backtest a Trading Strategy using Excel is now available in the Amazon Kindle Bookstore.

You may also be interested in the following articles related to trading using Excel:

Calculating the RSI Indicator
Calculating the SuperTrend Indicator
How to Trade and Calculate the Awesome Oscillator