How to Calculate the RSI Indicator using Excel – Video

Chart showing RSI Indicator on IWM ETF

Article last Updated on

This article shows how to calculate the RSI indicator developed by J. Welles Wilder. It includes the Excel formulas and a video showing the steps. You can skip straight to the: RSI Calculation Video.

What Is The Relative Strength Indicator?

The RSI is a multi-purpose indicator – you can use it to avoid costly trading errors, spot hidden opportunities and find markets just before they skyrocket.

The RSI is widely used to:

Identify Reversals

How to Calculate the RSI - Reversal Example

The most popular way to use the RSI is to identify temporarily overbought and oversold levels.

Confirm The Current Trend

How to Calculate the RSI - Trend Confirmation

The RSI is also useful to identify powerful trends. It tells us the price has moved to a new trading range and gives us the opportunity to make big profits.

Identify Divergences

How to Calculate the RSI - Divergence Example

The RSI is also a good way to identify divergences; where price makes a new low and the RSI fails to make a new low.

Why Do You Need to Calculate the RSI Indicator?

If you are making trading decisions based on the RSI Indicator you should understand how it is calculated. Firstly because you are risking your own money based on a line on a chart and you should be professional in all your actions as a trader. Professionals make money, amateurs have fun and take shortcuts.

Secondly, because it will allow you to unlock the market’s secrets by analysis. You will no longer be reliant on gurus and trading tips. You can develop, test and optimize your own strategies.

Steps to Calculate the RSI

You calculate the RSI by taking the average of the most recent gains and dividing it by the average of the most recent losses.

Time needed: 5 minutes.

Create Two Columns To

  1. Create Two Columns To Show the Gains and Losses

    Create a column to record to gains (close price greater than the previous close) and another column to record losses (close price lower than the previous close).

  2. Create Two Columns To Calculate the Average Gains and Losses

    The next two columns calculate the gains and losses. In the top cell use the AVERAGE formula. In the next cell use the formula:

    (Current Value + (Previous Average * (Number of Periods – 1)) / Number of Periods

  3. Calculate the Relative Strength

    Divide the average gains by the average losses to get the relative strength.

  4. Calculate the RSI

    Take the Relative Strength and convert it into the RSI by using the following formula:
    100 – (100/(1 + Relative Strength))

DateCloseGainsLossesGains AveLosses AveRelative StrengthRSI
1414
17/12/20101243.91
20/12/20101247.083.170
21/12/20101254.67.520
22/12/20101258.844.240
23/12/20101256.7702.07
27/12/20101257.540.770
28/12/20101258.510.970
29/12/20101259.781.270
30/12/20101257.8801.9
31/12/20101257.6400.24
03/01/20111271.8714.230
04/01/20111270.201.67
05/01/20111276.566.360
06/01/20111273.8502.71
07/01/20111271.502.352.750.783.5277.89
10/01/20111269.7501.752.560.853.0075.03
11/01/20111274.484.7302.710.793.4377.44
12/01/20111285.9611.4803.340.734.5581.98
13/01/20111283.7602.23.100.843.7078.71

YouTube RSI Calculation Video

Formulas Used

Upward Movement O5 =IF(F5>F4,F5-F4,)
Downward Movement P5 =IF(F5<F4,F4-F5,)
Average Upward Movement Q18 =AVERAGE(O5:O18)
Average Downward Movement R18 =AVERAGE(P5:P18)
Relative Strength S18 =Q18/R18
RSI T18 =100-(100/(S18+1))

Next Step – Boost Your Winning Trades

Tradinformed Backtest Models give you control back over your trading. The models are built in Excel and give you the security to develop new strategies and optimize your existing ones.