如何用Excel计算夏普比率 What Is The Sharpe Ratio? The Sharpe ratio is the financial ...  股票基金的比例怎么算

来源：雪球App，作者： 市川新田三丁目，（https://xueqiu.com/3776526626/114403887）

What Is The Sharpe Ratio?

The Sharpe ratio is the financial industry’s favorite measure of risk-adjusted returns. It tells investors whether they are being appropriately rewarded for the risks they’re assuming in their investments.

夏普比率是什么？

金融行业很喜欢用夏普比率来衡量经风险调整过的投资回报情况，即相对于所承担的投资风险投资者应得的相应回报率是多少。

The Sharpe ratio is calculated with the following formula:

夏普比率的计算公式如下：

夏普比率=（投资回报率-无风险收益率）/投资回报率的标准差

There are three components to the Sharpe Ratio calculation:

Investment return

Risk free rate of return

Investment standard deviation

通过公式可见，夏普比率有三个基本构成要素：

 投资回报率

 无风险收益率

投资回报率的标准差

Investment return and investment standard deviation are relatively straightforward and require no explanation.

投资回报率和投资回报率的标准差相对而言比较好理解，不需要过多的解释。

The risk free rate of return can be different depending on your use case. For most investors, a suitable risk free rate of return is the current yield on 10-year U.S. government bonds. That is the convention used in the Sharpe ratio spreadsheet available for download at the top of this article.

无风险收益率则根据情况会有所不同，对大多数投资者来讲，适合作为无风险收益率指标的是10年期美国国债的收益率。

The Sharpe ratio is rarely calculated by hand in practice. Instead, investors generally use a tool such as Microsoft Excel for the purpose of Sharpe ratio calculations.

夏普比率很少用手工的方法计算，投资者通常会借助Excel进行计算。

How To Manually Calculate Sharpe Ratios Using Yahoo! Finance

More specifically, this tutorial will show you how to calculate a 3-year Sharpe ratio for Apple, Inc (AAPL).

Step 1: Navigate to Yahoo! Finance.

Step 2: Type the ticker of your desired stock into the search bar at the top of the Yahoo! Finance bar. In our case, it’s Apple (AAPL), as shown below.

如何手工计算夏普比率

第一步：登录雅虎财经网站

第二步：在搜索栏输入想要计算的股票代码，比如苹果公司为AAPL，显示如下：

Step 3: Click on historical data, as shown below.

第三步：点击historical data历史数据下载

Step 4: Change the dates to the range of your choice, and then click “Apply.” After that, click “Download Data” (which is just below the Apply button).

第四步：打开历史数据下载栏后，可以对起始日期进行修改，见左侧红线标注，如不做修改，系统默认期限为截止到最新交易日的一年，本例中统计期限为2014年12月29日至2017年12月28日的三年，然后点击“Apply”应用，再点击“Download Data”数据下载功能键即开始下载数据

Step 5: The Excel document that will download as a result of your process so far will have six columns: Open, High, Low, Close, Adjusted Close (or “Adj Close” in the top row of the spreadsheet), and Volume. It’s Adjusted Close that we’re interested in, as this accounts for stock splits and dividend payments.

Using the adjusted close column, calculate the stock’s annualized returns during the time period under investigation. In the example shown below, this is done by dividing the current price by the oldest price and then raising this to the inverse power of the number of years during the sample. The equation is shown below.

第五步：下载的Excel数据有六类，分别为：开盘价，最高价，最低价，收盘价，调整后的收盘价及成交量。只留下调整后的收盘价，因为该收盘价已经根据股票的分红和拆分等情况做了加工。

然后根据调整后的收盘价，计算AAPL在本统计期间的年化回报率，公式见红线标注，方法为用最后一个数据除以第一个数据，然后用该统计期间的年数进行开方

Step 6: Next, a time series of daily returns needs to be calculated. This is done in column I in the spreadsheet shown below.

第六步：计算日均回报率，即环比涨跌幅度，方法为用后一个交易日除以前一个交易日调整后的收盘价再减去1

Step 7: Calculate the standard deviation of daily price returns using the STDEV.P() function, and then convert this number to an annualized figure by multiplying by the square root of 252. We use 252 instead of 365 (the number of days in a year) because there are approximately 252 trading days in an average calendar year. The formula to calculate the annualized standard deviation figure is shown below.

第七步：用Excel的STDEVP()函数计算日均回报率的标准差，然后乘上252的平方根，因为一个日历年度有365天但是交易天数一般为252，公式见红线标注。

Step 8: Use the annualized return and annualized standard deviation data to calculate a Sharpe ratio. An example of how to do this is shown below, using 2.4% as the risk free rate of return.

第八步：计算夏普比率需要无风险收益率，假设作为无风险收益率的10年期美国国债收益率为2.4%，公式如下：

The resulting number is the Sharpe ratio of the investment in question. In this case, Apple had a 3-year Sharpe ratio of 0.62 at the time of this writing.

计算结果显示，在从2014年12月29日至2017年12月28日的三年间，苹果公司股价的夏普比率为0.6222

按照同样的方法，计算特斯拉公司股票TSLA近一年的夏普比率，最新的10年期美国国债收益率为2018-9-27的3.06%

计算结果显示，在2017-9-28至2018-9-27的一年间特斯拉股票的夏普比率为-0.1436

同一期间微软公司MSFT股价的夏普比率为0.5675,

夏普比率反映了投资者每承担一个单位的风险，投资回报增长率超过无风险收益率的程度。

如果夏普比率为正值，说明在统计期间内投资回报的平均增长率超过了无风险利率。如果以同期10年期美国国债收益率作为无风险利率，说明投资股票的回报情况比投资美国国债要好。

夏普比率越大，说明每单位风险所获得的风险回报越高。夏普比率为负时，按大小排序没有意义。

需要注意一点：Excel计算标准差的函数有三个，分别为STDEVA函数，STDEV函数，STDEVP函数；

其中：

STDEV 假设其参数是总体中的样本，如果数据代表样本总体中的样本，应使用函数 STDEV 来计算标准偏差。

STDEVA 假设参数为总体的一个样本；

STDEVP 假设其参数为整个样本总体，如果数据代表全部样本总体，则应该使用函数 STDEVP 来计算标准偏差。