-2

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.

Hearno
  • 27
  • 6
  • You are going to need to post a usable sample subset that can easily be copy pasted into a spreadsheet in order for someone to help you. It will need to be your first attempt at it and how it is going wrong. You also might show what you'd expect the value to be if it did work. – Brian Z Aug 26 '21 at 16:57
  • If you want to use the ten year treasury constant maturity, you can download the most recent daily data from FRED. They have an API (for free) which you can use to get all sorts of information, including the ten year treasury return. But if you have an add-in that will update the TNX, why not use that? – Ron Rosenfeld Aug 27 '21 at 01:36
  • Please provide enough code so others can better understand or reproduce the problem. – Community Aug 27 '21 at 17:39
  • Not sure why additional code required - thought this was straightforward Q - entitled to my opinion and interestingly the last time I posted this comment it was mysteriously removed. Ta – JB-007 Sep 03 '21 at 19:18

1 Answers1

0

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.

JB-007
  • 2,156
  • 1
  • 6
  • 22