cnrlzen
asked on
Reverse RSI Calculation
I lack the math/excel skills to do this formula in reverse. I googled how to calculate it normally, but now want to calculate it in reverse. It's a stock calculation called RSI.
How it works Normally:
As a stock price changes (say daily) it divides the average gain of the past 3 days by the INT average loss of the past 3 days, then converts the number into a format that must be between 0 - 100. For the first 3 rows the average gain formula is simply (in cell F5) =AVERAGE(D3:D5). But for the subsequent rows it is (in cell F6) =(2*F5+D6)/3. same is done for average loss in column G. Finally, RSI formula in cell I5 is =100-(100/(H5+1)) (where H5 is avg gain/avg loss).
See example file:
RSICalc.xlsx
So this works as I need it, but what I want to do is know what price needs to occur the next day for me to get an RSI of a certain amount. E.g. if I enter 90, what would the price need to be?
How it works Normally:
As a stock price changes (say daily) it divides the average gain of the past 3 days by the INT average loss of the past 3 days, then converts the number into a format that must be between 0 - 100. For the first 3 rows the average gain formula is simply (in cell F5) =AVERAGE(D3:D5). But for the subsequent rows it is (in cell F6) =(2*F5+D6)/3. same is done for average loss in column G. Finally, RSI formula in cell I5 is =100-(100/(H5+1)) (where H5 is avg gain/avg loss).
See example file:
RSICalc.xlsx
So this works as I need it, but what I want to do is know what price needs to occur the next day for me to get an RSI of a certain amount. E.g. if I enter 90, what would the price need to be?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is great, a huge help to me, thanks very much for your time.
My pleasure. hope it does what you need.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Though both solutions worked, not having the extra columns is a better solution if someone else were searching around. But I gave Neil more points because he answered first. Apologies if this is not the etiquette, I greatly appreciate both your time and my spreadsheet is working wonderfully.
It reverse calculates from the actual RSI in the sample rows, except in Row 16 (April 7) where I have plugged in a desired RSI of 40.
This gives you the "required close" to achieve that RSI. However, because the RS is the result of dividing average gain by average loss, there are actually always two possible answers, in theory.
So in the model you need to decide if the RSI results from a loss or a gain. If you type a negative number in column C the calculation will assume a loss. See screenshot attached where I have typed -1 in column C and a desired RSI of 40.
This produces a close of 132.01, and a negative change on the day.
However, if you type a positive value in column C, you will get a different close. Potentially, also, you will get a reverse-calculated change that is negative instead of positive, which suggests to me there is only one "right" answer in terms of positive/negative change.