In this Excel tutorial you will learn how to Calculate RSI Indicator in Excel spreadsheet.
The Relative Strength Index (RSI) is a popular momentum oscillator used to determine whether a stock or other financial instrument is overbought or oversold. The RSI is calculated using a formula that compares the magnitude of recent gains to recent losses, with values ranging from 0 to 100.
Here’s how to calculate the RSI indicator in Microsoft Excel:
- Prepare your data: Make sure your data is organized in a clear and consistent manner, with the closing price of the stock in a column.
- Calculate the average gain: To calculate the average gain, you can use the following formula: =SUM(IF(closing_price>LAG(closing_price),closing_price-LAG(closing_price),0),14)/14. Replace “closing_price” with the range of cells that contain the closing price data.
- Calculate the average loss: To calculate the average loss, use the following formula: =ABS(SUM(IF(closing_price<LAG(closing_price),LAG(closing_price)-closing_price,0),14))/14. Replace “closing_price” with the range of cells that contain the closing price data.
- Calculate the relative strength: To calculate the relative strength, divide the average gain by the average loss: =average gain/average loss.
- Calculate the RSI: To calculate the RSI, use the following formula: =100-100/(1+relative strength).
- Plot the RSI: To plot the RSI, you can create a line chart with the RSI values on the y-axis and the time period on the x-axis.
- Interpret the results: The RSI is used to determine whether a stock or other financial instrument is overbought or oversold. A reading above 70 is considered overbought, while a reading below 30 is considered oversold.
