I am trying to figure out how to find the "risk free rate of return" that will update automatically with the Sharpe ratio of multiple stocks/portfolios on a daily basis in Excel or Google Sheets.
Sharpe ratio = (Mean portfolio return − Risk-free rate)/Standard deviation of portfolio return, or, S(x) = (rx - Rf) / StandDev(rx)
I am going to use the "Treasury Yield 10 Years" (TNX) while trying to find this rate. I haven't felt comfortable with anything that I have found yet. I had an add-in that will update the TNX automatically (and any other bond I would need). Any help would be appreciated.
Assuming you have values for S(x) (you mention you do), the mean (mu) and std dev (s), solving for the risk free rate (rf) yields the following equation:
rf = mu - S(x)*s
The greater the value of the Sharpe ratio, the more attractive the risk-adjusted rate of return, hence the larger the margin (S(x)*s) over the portfolio mean. The fact this varies in line with the standard deviation (s) is a simple matter of the 'risk vs. reward' principle/concept (negative Sharpe ratios simply imply the portfolio has underperformed the benchmark)...
Depending on the data in hand - you may/should be able to approximate S(x), s, and mu from using standard excel equations TNX download info - if not, consider benchmark data by constructing a replicated portfolio (Asset Liability Modelling theory - see here.