RSI formula in Excel: Wilder's RSI(14) step by step

The RSI formula

RS = average gain ÷ average loss

RSI = 100 − 100 ÷ (1 + RS)

J. Welles Wilder introduced the indicator in 1978 with a 14-day period, which is still the default. The RSI runs from 0 to 100. If the average gain equals the average loss, RS is 1 and the RSI is 50. If the stock only rose, the average loss is 0 and the RSI is 100.

Step 1: split each change into a gain and a loss

Put the closing prices in column A, oldest at the top. For each day after the first, the gain is the rise and the loss is the fall, both as positive numbers:

B3: =MAX(A3-A2,0) and C3: =MAX(A2-A3,0)

On any day, one of the two is zero. Copy both down. 22 closes give 21 changes.

Worked example: RSI(14) on 22 daily closes

The closes in column A are inputs. Rows 3 to 15 only have gains and losses, the averages and the RSI start in row 16. The prices are made up.

Worked example: RSI(14) on 22 daily closes

	A	B	C	D	E	F
1	Close	Gain	Loss	Avg gain	Avg loss	RSI (14)
2	44.00
3	44.35	0.35	0.00
4	44.10	0.00	0.25
5	44.62	0.52	0.00
6	45.05	0.43	0.00
7	44.80	0.00	0.25
8	45.30	0.50	0.00
9	45.71	0.41	0.00
10	45.42	0.00	0.29
11	45.95	0.53	0.00
12	46.40	0.45	0.00
13	46.12	0.00	0.28
14	46.55	0.43	0.00
15	46.30	0.00	0.25
16	46.85	0.55	0.00	0.2979	0.0943	76.0
17	47.20	0.35	0.00	0.3016	0.0876	77.5
18	46.70	0.00	0.50	0.2800	0.1170	70.5
19	46.25	0.00	0.45	0.2600	0.1408	64.9
20	46.60	0.35	0.00	0.2665	0.1307	67.1
21	47.10	0.50	0.00	0.2831	0.1214	70.0
22	47.45	0.35	0.00	0.2879	0.1127	71.9
23	47.05	0.00	0.40	0.2674	0.1332	66.7
24
25	Last day with simple 14-day averages instead (for comparison)					61.8

Educational, not investment advice. An indicator describes past prices, it does not predict the next move.

Step 2: the first average is a simple average

The first RSI needs 14 changes, so 15 closes. In row 16, the first average gain is =AVERAGE(B3:B16) = 0.2979 and the first average loss =AVERAGE(C3:C16) = 0.0943. RS = 0.2979 ÷ 0.0943 = 3.16, and the RSI is =100-100/(1+D16/E16) = 76.0. The days before row 16 have no RSI. Leave them empty rather than averaging fewer than 14 values.

Step 3: Wilder's smoothing for every later day

From the second RSI on, do not take a new 14-day average. Wilder's smoothing carries the previous average forward and gives today's value a weight of 1/14:

D17: =(D16*13+B17)/14 and E17: =(E16*13+C17)/14

In row 17 the average gain becomes (0.2979 × 13 + 0.35) ÷ 14 = 0.3016 and the RSI 77.5. Copy the three formulas down. This is an exponential moving average with a smoothing factor of 1/14, the same as a 27-day EMA in the usual 2 ÷ (n + 1) convention, so the RSI reacts more slowly than a 14-day EMA would.

Why your RSI may not match your chart

Two things make spreadsheet RSI values differ from a charting platform:

• The averaging method. Some tools use a simple 14-day average every day (sometimes called Cutler's RSI). On the last day of the example that gives 61.8 instead of 66.7, a big difference from the same prices.
• The starting point. Because Wilder's average never fully forgets old values, the RSI depends a little on where the data starts. The effect fades quickly: after about 100 days the weight of the starting values is below 0.1%. Use a long history before comparing values with another tool.

Also check the prices themselves: adjusted and unadjusted closes differ after dividends and splits.

Reading the RSI

Wilder called readings above 70 overbought and below 30 oversold. In the example the RSI is above 70 on several days during a steady climb. That only describes the recent moves as one-sided. Strong trends can keep the RSI above 70 for weeks, so it is not a sell signal on its own. Traders also watch the 50 line and divergences, when the price makes a new high but the RSI does not. Combine it with other indicators, such as MACD or Bollinger Bands, and test any rule before you trust it.

Handling a period without losses

If prices rose on all 14 days, the average loss is 0 and D16/E16 returns #DIV/0!. Catch it: =IF(E16=0,100,100-100/(1+D16/E16)). The RSI is 100 by definition in that case.

Common mistakes

• Averaging the changes instead of the gains. In the change column, losses cancel gains. Average gains and losses in separate columns.
• Storing losses as negative numbers. RS then turns negative and the RSI goes above 100. Use MAX(A2-A3,0) so losses are positive.
• Using a rolling simple average every day. That is a different indicator. Wilder's RSI uses a simple average only once, then smooths.
• Starting with 14 closes. 14 closes give only 13 changes. The first RSI needs 15 closes.
• Writing 100/(1+RS). That is 100 minus the RSI. The formula is 100-100/(1+RS).
• No guard against zero losses. After 14 up days the average loss is 0. Wrap the RSI in an IF that returns 100.