Here’s How to Calculate Sharpe Ratio in Excel with Formula

In this article, I’m going to explain how to calculate one of the most famous ratios, among financial analysis, to measure risk in Microsoft Excel: the Sharpe Ratio.

According to Investopedia:

The Sharpe ratio tells us whether a portfolio’s returns are due to smart investment decisions or a result of excess risk. Although one portfolio or fund can reap higher returns than its peers, it is only a good investment if those higher returns do not come with too much additional risk. The greater a portfolio’s Sharpe ratio, the better its risk-adjusted performance has been. A negative Sharpe ratio indicates that a risk-less asset would perform better than the security being analyzed.”

The formula is the following:

1x1.trans Heres How to Calculate Sharpe Ratio in Excel with Formula

Where  Expected Return (P) is the expected return of the portfolio and “Standard Deviation (P)” is the standard deviation of the portfolio.

Calculation of Sharpe Ratio

Now let’s get hands-on work and calculate the Sharpe Ratio for a two – stocks portfolio in MS Excel.

For the analysis, we will suppose that our portfolio is composed 50% by Apple Inc. (AAPL) and 50% by Microsoft Corp. (MSFT).

1x1.trans Heres How to Calculate Sharpe Ratio in Excel with Formula

 Step 1:

First of all, we need to get the stocks’ daily prices in order to calculate the daily returns that we are going to use to calculate the variables that compose the Sharpe Ratio.

We can get the historical prices from many sources but the most famous ones are Yahoo! Finance or Google Finance:

1x1.trans Heres How to Calculate Sharpe Ratio in Excel with Formula

1x1.trans Heres How to Calculate Sharpe Ratio in Excel with Formula

Step 2:

Input the daily prices into an Excel worksheet and calculate daily returns for the period being analyzed (I will use a 3-year time horizon).

A daily return can be calculated with the formula below:

Daily Return = (Price 1 – Price 0) / Price 0

Daily Return = (Price 1 / Price 0) -1

Where Price 1 is the following day close price and Price 0 is the previous day close price.

 

1x1.trans Heres How to Calculate Sharpe Ratio in Excel with Formula

Step 3:

Once we have all the daily returns we are able to calculate the expected return for each of the stocks. To do this, we have to calculate the average of the daily returns of the period being analyzed and then annualize it.

We will use Excel’s average formula: =AVERAGE (Range of Returns1) and then we annualize it by multiplying by 252 (tradable days).

Then, we have to do the same calculations for the second stock:

=AVERAGE (Range of Returns2) and then we annualize it by multiplying by 252.

Step 4:

Following the Sharpe Ratio formula, other variable we need to calculate is the Standard Deviation of the portfolio.

Let’s remember the Variance formula for a two-stocks portfolio:

VAR (P) = Weight1^2 * STDEV1^2 + Weight2^2 * STDEV^2 +2 * Weight1 * Weight2 * STDV1 * STDV2 *CORR(1,2)

Where COV(1,2) = STDEV1 *STDEV2 * CORR1,2

So, VAR (P) = Weight1^2 * STDEV1^2 + Weight2^2 * STDEV^2 + 2 * Weight1 * Weight2 * COV1,2

Then, the standard deviation of the portfolio is the squared root of the Variance.

STDEV (P) = (Weight1^2 * STDEV1^2 + Weight2^2 * STDEV^2 + 2 * Weight1 * Weight2 * COV1,2)^(1/2)

One of the inputs we can easily calculate in Excel is the Standard Deviation of the returns of each stock:

For the first stock = STDEV1 (Range of Returns1)

For the second stock = STDEV2 (Range of Returns2).

Then, we need to calculate the Covariance of the two stocks based on the returns.

Note: According to Investopedia, the covariance is a measure of the degree to which returns on two risky assets move in tandem. A positive covariance means that asset returns move together. A negative covariance means returns move inversely.

To do this we use the covariance function provided by Excel:  =COVAR (Range of Returns 1; Range of Returns2)

Once we have calculated the Standard Deviation and the Covariance of the stocks, we can put all the variables together and get the Standard Deviation of the Portfolio.

The standard deviation calculated is in a daily basis and we have to get it annualized so we have to multiply the result by the squared root of 252 (average number of tradable days per year).

Step 5:

We are one step to finish the calculation! We just need to get the Risk Free Rate and put all the variables together. The United States Treasury Bonds are known as risk free because they always pay. For this analysis, a 5-year bond will be considered with an annual rate of 1.72%.

The numbers for the example are the following:

Annualized Expected Return MSFT= 0.07%*252 = 18.46%

Annualized Expected Return AAPL= 0.08%*252 = 19.29%

Portfolio’s Annualized Expeted Return= 0.5*18.46% + 0.5*19.29% = 18.87%

Std Dev MSFT= 1.49%

Std Dev AAPL= 1.77%

Covariance (MSFT, AAPL)= 0,0001

Portfolio’s Std Dev (Daily) = (0.5^2*1.49%^2+0.5^2*1.77%^2+2*0,5*0,5*0,0001)^(1/2) = 0.02%

Portfolio’s Std Dev (Annualized) = (0.02%*252)^(1/2)= 20.71%

Risk Free Rate= 1.72%

Putting all the variables together into the Sharpe Ratio formula we get the following:

1x1.trans Heres How to Calculate Sharpe Ratio in Excel with Formula

1x1.trans Heres How to Calculate Sharpe Ratio in Excel with Formula

 

Sharpe Ratio (P) = (18.87% – 1.72%) / 20.71% = 0.83

About the Author: Fernando Ventureira is a Financial Analyst at iBillionaire Inc.

Special Bonus on BIWS Programs from FinanceWalk

Here’s an offer on Breaking Into Wall Street (BIWS) programs:

All FinanceWalk readers will get FREE $97 Bonus – FinanceWalk’s Equity Research Program.

If you want to build a long-term career in Financial Modeling, Investment Banking, and Private Equity, I’m confident these are the only courses you’ll need. ( Because Brian (BIWS) has created world-class online financial modeling training programs that will be with you FOREVER).

If you purchase BIWS courses through FinanceWalk links, I’ll give you a FREE Bonus of FinanceWalk’s online Equity Research Program ($97 Value).

I see FinanceWalk’s Equity Research Program as a pretty perfect compliment to BIWS courses – BIWS helps you build financial modeling and investment banking skills and then I will help you build equity research and report writing skills.

To get the FREE $97 Bonus, please purchase from the following links.

Excel and Financial Modeling Fundamentals – Free $97 Bonus

Advanced Financial Modeling – Free $97 Bonus

Bank & Financial Institution Modeling – Free $97 Bonus

Oil & Gas Modeling – Free $97 Bonus

Real Estate & REIT Modeling – Free $97 Bonus

Investment Banking Networking Toolkit – Free $97 Bonus

Investment Banking Interview Guide – Free $97 Bonus

IB Networking Toolkit + IB Interview Guide $144 ( Save $50)

BIWS Platinum – Save $532 ( Save 35%) + Free $97 Bonus : The most comprehensive IB package on the market today. Includes the NEW and Improved BIWS Premium Financial Modeling Course (RRP: $497) PLUS access to the above 6 complementary courses designed to make you into an even more well-rounded IB Professional:

BIWS Premium – Excel & Modeling Fundamentals and Advanced Financial Modeling programs together – save $97 ( Save 20% ) + Free $97 Bonus

Industry Specific Course Bundle – get Bank Modeling + Oil and Gas Modeling + Real Estate Modeling for just $497 and save $244 + Free $97 Bonus

In order to get your FREE Bonus, you must:

  1. Purchase the course through FinanceWalk links.
  2. Send me an email along-with your full name and best email address to avadhut@financewalk.com so I can send you the course login details.

Click Here to Check All BIWS Programs – Free $97 Bonus

How to Do DCF Valuation Using Sensitivity Analysis in Excel

Discounted Cash flow is probably the commonest ways of valuation of a company. This method involves amongst other things analyzing the impact of factors like cost of equity or change in risk free rate on the price of a company’s share.

It is but obvious that any company operates in a dynamic environment and hence for investors it is imperative that the model so built gives the investor a range of price movement so that they are prepared with respect to the possible price fluctuations that they might have to encounter if they decide to stay invested in the company.

Investors are able to gauge the sensitivity of price to various inputs using a technique called “sensitivity analysis”.

Sensitivity analysis is especially useful in cases where investors are evaluating between proposals of the same industry or in cases where proposals are from multiple industries but driven by similar factors

What Is Sensitivity Analysis?

As the words suggest, in sensitivity analysis, we try and ascertain the impact of change in outcome for changes in inputs. In other words it is also a function of the effect of various inputs to the final outcome and also the impact that each input has.

The most common tool available for us to do sensitivity analysis is Microsoft Excel.

So How Do We Do It?

In Excel, sensitivity analysis comes under “What-if” analysis functions. The following are used most often

(1) Data Table

1. One Variable Data Table

2. Two Variable Data Table

(2) Goal Seek

Data Tables

1. One Variable Data Table

Let us assume that we would like to know the impact that any change in cost of equity on the discounting factor which would be used to calculate discounting factor.  Our model currently shows that the discounting factor at 15.1% cost of equity is approximately 0.97.

Now if we need to analyze the impact of change in cost of equity on discounting factor, then we could do the following:

1x1.trans How to Do DCF Valuation Using Sensitivity Analysis in Excel
Steps:

a)  Type the list of values that you want to evaluate in the input cell either down one column or across one row.

b) Leave a few empty rows and columns on either side of the values.

c) If the data table is column-oriented (your variable values are in a column), type the formula in the cell in a manner similar to how we have done in cell P9.

d) Select the range of cells that contains the formulas and values that you want to substitute. For us this range is O9:P22.

e) The results i.e. incremental changes in discounting factor would automatically appear in cells P9:P22.

2. Two Variable Data Table

Let us take another example wherein we need to analyze the impact of change in not only cost of equity but also risk free rate on the Value per Share.

If we need to factor in both these variables then we would be using a two variable data table for sensitivity analysis in Excel.

1x1.trans How to Do DCF Valuation Using Sensitivity Analysis in Excel

The following are the additional steps that we need to do to include the two variables:

a) Since the data table has both columns and rows, hence the formula cell shifts exactly above the column variable and right beside the row variable, which for us is cell E18.

b) Select the range of cells that contains the formulas and values that you want to substitute. For us this range is E18:L24.

c) On the Data tab, click What-If Analysis, followed by“Data Table”. Type the cell reference

  • For the “Columninput” cell box, for us its O13.
  • For the Rows input cell box, for us its O20.

d) The results i.e. the possible variations in would automatically appear in cells E19:L25.

Goal Seek

This function is used to find the missing input for the desired result.

Let’s take the example of the model that we have used for Data Tables. Here let’s say we know the cost of equity. However we are not sure what would be the market risk premium. In this scenario “Goal Seek” is an excellent function for sensitivity analysis in Excel.

The methodology of using “Goal Seek” is as follows.1x1.trans How to Do DCF Valuation Using Sensitivity Analysis in Excel

a)      On the Data tab, click What-If Analysis and then click “Goal Seek”.

b)      In the Set cell box, enter O20, the cell with the formula you want
in our case it’s the average cost of equity.

c)       In the To value box, type the target value i.e. 15.1%.

d)      In the By changing cell box, enter O14, the reference to the cell that contains the value that you want to adjust.

e)      Click OK and the result would come up as 12.3% after rounding off.

 

I have also created a video to help you understand how to use sensitivity analysis for DCF valuation.

 

 

Thus to conclude, we can state that Excel allows us to use various tools to make our lives easier.

In financial modeling one of the key components to efficiently perform requirements related to interpreting sensitivity is by using “What-If Analysis” with various steps as mentioned above.

Using Data tables and goal seek function we can save ourselves from a lot of time and error wastage which may occur in case you decide to do the calculations by hand.

In all probability, learning effective use of sensitivity analysis in Excel would make us better prepared to understand the impact of inputs on the value of investments and hence keep us better prepared for fluctuations once we commit our investments.

Special Bonus on BIWS Programs from FinanceWalk

Here’s an offer on Breaking Into Wall Street (BIWS) programs:

All FinanceWalk readers will get FREE $97 Bonus – FinanceWalk’s Equity Research Program.

If you want to build a long-term career in Financial Modeling, Investment Banking, and Private Equity, I’m confident these are the only courses you’ll need. ( Because Brian (BIWS) has created world-class online financial modeling training programs that will be with you FOREVER).

If you purchase BIWS courses through FinanceWalk links, I’ll give you a FREE Bonus of FinanceWalk’s online Equity Research Program ($97 Value).

I see FinanceWalk’s Equity Research Program as a pretty perfect compliment to BIWS courses – BIWS helps you build financial modeling and investment banking skills and then I will help you build equity research and report writing skills.

To get the FREE $97 Bonus, please purchase from the following links.

Excel and Financial Modeling Fundamentals – Free $97 Bonus

Advanced Financial Modeling – Free $97 Bonus

Bank & Financial Institution Modeling – Free $97 Bonus

Oil & Gas Modeling – Free $97 Bonus

Real Estate & REIT Modeling – Free $97 Bonus

Investment Banking Networking Toolkit – Free $97 Bonus

Investment Banking Interview Guide – Free $97 Bonus

IB Networking Toolkit + IB Interview Guide $144 ( Save $50)

BIWS Platinum – Save $532 ( Save 35%) + Free $97 Bonus : The most comprehensive IB package on the market today. Includes the NEW and Improved BIWS Premium Financial Modeling Course (RRP: $497) PLUS access to the above 6 complementary courses designed to make you into an even more well-rounded IB Professional:

BIWS Premium – Excel & Modeling Fundamentals and Advanced Financial Modeling programs together – save $97 ( Save 20% ) + Free $97 Bonus

Industry Specific Course Bundle – get Bank Modeling + Oil and Gas Modeling + Real Estate Modeling for just $497 and save $244 + Free $97 Bonus

In order to get your FREE Bonus, you must:

  1. Purchase the course through FinanceWalk links.
  2. Send me an email along-with your full name and best email address to avadhut@financewalk.com so I can send you the course login details.

Click Here to Check All BIWS Programs – Free $97 Bonus

How to Freeze Panes and Zoom in Excel

Picture5.png

  In this post, we will see how to freeze panes and how to zoom in and out of data in excel. Freezing Panes is especially important when you have really huge data. Like shown in the figure below, where the data size is large and you need to scroll down, in such case you […] Read more »

How to Hide, Unhide and Group in Excel

Navigation

In this post, we will see very important features- hide, unhide and group in excel. Hide and Group in excel feature is useful when you have really huge data that spans over too many rows and columns, and you want to do away with some rows and columns and want to access only few of them. […] Read more »