#### How to Calculate RSI Indicator in Excel

In this Excel tutorial you will learn how to Calculate RSI Indicator in Excel spreadsheet.

The Relative Strength Index (RSI) is a popular momentum indicator in technical analysis that helps determine whether a stock is overbought or oversold. It ranges from 0 to 100, with values above 70 indicating an overbought condition and values below 30 suggesting an oversold state. You can calculate the RSI indicator in an Excel spreadsheet by following these steps.

## Step 1: Prepare Your Data

Make sure your data is well-organized, with Closing Prices for each trading day listed in one column. For example, you could have your dates in Column A and closing prices in Column B.

## Step 2: Calculate Daily Changes (Gains and Losses)

In a new column (e.g., Column C labeled “Change”), calculate the difference between each day’s closing price and the previous day’s closing price =B2 – B1

## Step 3: Calculate Average Gain and Average Loss

The standard RSI period is 14, so you need to calculate the average gain and average loss over the last 14 periods.

In a new column (e.g., Column D labeled “Gain”), enter the formula to record gains only: =IF(C2>0, C2, 0)

In another column (e.g., Column E labeled “Loss”), enter the formula for losses as positive values: =IF(C2<0, ABS(C2), 0)

Calculate Average Gain and Average Loss for the Initial Period:

- In Cell D15, calculate the Average Gain for the first 14 periods: =AVERAGE(D2:D15)
- In Cell E15, calculate the Average Loss for the first 14 periods: =AVERAGE(E2:E15)

### Continued Averages (Smoothing)

To smooth the averages for gains and losses over time, use the following formulas from Row 16 onwards:

- Average Gain: =((D16 * 13) + D15) / 14
- Average Loss: =((E16 * 13) + E15) / 14

This ensures that the averages are smoothed over time, giving more weight to recent periods.

## Step 4: Calculate Relative Strength (RS)

The Relative Strength (RS) is the ratio of the average gain to the average loss.

In a new column (e.g., Column F labeled “RS”): =IF(E16=0, 0, D16/E16)

This formula calculates the ratio of average gain to average loss. If the average loss is `0`, set the RS to `0` to avoid division by zero.

## Step 5: Calculate RSI

The RSI is derived from the relative strength using the formula: =100 – (100 / (1 + F16))

Place this formula in a new column (e.g., Column G labeled “RSI”) and drag it down to calculate the RSI for all periods.

## Step 6: Plot the RSI

To visualize the RSI, you can create a line chart:

- Highlight the column containing the RSI values (Column G) and the corresponding dates (Column A).
- Go to the Insert tab and choose Line Chart.
- Customize the chart as needed, setting the dates as the x-axis and RSI values as the y-axis.

## Step 7: Interpret the Results

Now that you have plotted the RSI:

An RSI above 70 indicates an overbought condition, suggesting the stock may be due for a correction or reversal. An RSI below 30 indicates an oversold condition, suggesting the stock might be undervalued or due for a bounce.

You can calculate and plot the RSI in Excel to help analyze stock trends and potential buy/sell opportunities. Adjust the period (14) as needed to fit your analysis.

## Leave a Reply