If youâ€™re ever interested in calculating the highest possible return from a minimum amount of investment risk, youâ€™re in the right place. In this article, Iâ€™ll demonstrate to you **how to calculate Alpha **in **Excel.** This is the term that provides you with a quantitative measurement of the returns considering the risk. This is also known as **Jensenâ€™s Alpha**. Iâ€™ll show you how to calculate another type of **Alpha **that is **Cronbachâ€™s Alpha**. Iâ€™ll explain the steps in detail so that you may use them easily whenever you need them. Now to get an overview of what weâ€™re going to do here, you may check the following image.

**Table of Contents**hide

## Download Practice Workbook

You can download our practice workbook from here for free!

## What Is Alpha?

Before diving into the details, first, letâ€™s get introduced to the term **Alpha**. **Alpha **is an indicator to describe the possibility of getting the maximum possible return considering the risk associated. This is a measurement professionals use in their day to day life to get an idea of how well a portfolio should work taking the risk into consideration. This kind of **Alpha **is also known as **Jensenâ€™s Alpha**. You may use it in the stock market every now and then. The formula to calculate **Alpha **is as follows.

**Alpha = Portfolio Returns â€“ Expected Rate of Return**

where,

**Expected Rate of Return = Risk Free Rate + Beta * (Market Returns â€“ Risk Free Rate)**

There is another kind of **Alpha **known as **Cronbachâ€™s Alpha**. This is related to a parameter that measures internal consistency. That is, the close relationship between the different parts of a system can be measured using **Cronbachâ€™s Alpha**. You may use **Cronbachâ€™s Alpha **to measure the internal consistency of a questionnaire

## How to Calculate Alpha in Excel: 4 Suitable Examples

### 1. Calculate Cronbachâ€™s Alpha in Excel

**Cronbachâ€™s Alpha **is a quantitative measurement of how closely connected the components of a system are. For example, weâ€™ve 4 questions regarding our new product. Weâ€™ve conducted a survey with these questions and now want to analyze the degree of internal connections among these questions. To analyze this, weâ€™ve taken the responses of 10 persons to our questionnaires into consideration. Our dataset looks like this.

Now Iâ€™ll demonstrate to you how we can calculate **Cronbachâ€™s Alpha **for this dataset.

__Steps:__

- Go to
**Data >> Data Analysis >> Anova: Two Factor Without Replication**and click on**OK**.

As a result, the **Anova: Two Factor Without Replication **window will pop up.

- Type
**$C$5:$F$14**in the**Input Range:**to populate the dataset and**$B$16**in**Output Range:**to get the output and click on**OK**.

As a result, weâ€™ll get the details of **Anova: Two-Factor Without Replication **data starting from cell **B16**.

Weâ€™ll need to use the data in cell **E38** which is **MS **for **Rows **and in cell **E40 **which is **MS** **Error **to calculate **Cronbachâ€™s Alpha**.

- Type the following formula in cell
**B45**and hit**ENTER**to get**Cronbachâ€™s Alpha**. Iâ€™ve shown the formula in cell**C45**using the**FORMULATEXT**function so that you can understand it properly.

`=1-(E40/E38)`

So, weâ€™ve got **Cronbachâ€™s Alpha **value for our dataset. The value is approximately 0.89 which indicates a strong internal relationship within the questions of the questionnaires.

**Read More: ****How to Get Stock Prices in Excel (3 Easy Methods)**

### 2. Compute Jensenâ€™s Alpha Using Beta Calculation in Excel

**Jensenâ€™s Alpha **is a measurement of the most possible return considering the risk associated with it. It is also known as **Alpha**. We can calculate **Jensenâ€™s Alpha **for any portfolio. For example, we have the **Portfolio Returns **and **Market Returns **data for a whole year. We now want to calculate **Jensenâ€™s Alpha **for this set of data. First of all, take a look at our dataset.

Now, Iâ€™ll show the step-by-step procedures to calculate **Jensenâ€™s Alpha**.

__Steps:__

Weâ€™ve **Portfolio Returns **and **Market Returns **data. We need to calculate the average of these data. Weâ€™ll use the **AVERAGE function** to do so.

- Type the following formula in cell
**C17**and press**ENTER**to get the**Average Portfolio Returns**. Iâ€™ve shown the formula in cell**C18**to ease your understanding.

`=AVERAGE(C5:C16)`

- Similarly, type the following formula in cell
**D17**to get the**Average Market Returns**and press**ENTER**.

`=AVERAGE(D5:D16)`

Now, we need to** calculate the Beta value**. For this, we need to get the **Covariance **and **Variance**. Weâ€™ll use the **COVARIANCE **function and the **VAR function** to do so.

- Type the following formula in cell
**C21**and press**ENTER**to get the**Covariance**.

`=COVARIANCE.P(C5:C16,D5:D16)`

- Similarly, type the following formula in cell
**C22**, and hit**ENTER**to get the**Variance**.

`=VAR.P(C5:C16)`

**Beta **is the ratio of **Covariance **and **Variance**.

- Type the following formula in cell
**C23**and press**ENTER**to get the**Beta**value.

`=C21/C22`

Suppose the **Risk Free Rate **is **1.30%**. Now, we have to calculate the **Expected Rate of Return**. We know that the formula to calculate **Expected Rate of Return **is as follows.

**Expected Rate of Return = Risk Free Rate + Beta * (Market Returns â€“ Risk Free Rate)**

- Type the following formula in cell
**C27**and hit**ENTER**to get the**Expected Rate of Return**.

`=C26+C23*(D17-C26)`

Now, **Jensenâ€™s Alpha **which is also known as **Alpha **is the difference between **Average Portfolio Return **and **Expected Rate of Return**.

- Type the following formula in cell
**C28**and hit**ENTER**to get**Jensenâ€™s Alpha**.

`=C17-C27`

Hence, Jensenâ€™s** Alpha **is **3.40%**.

**Read More: ****How to Import Stock Prices into Excel from Yahoo Finance**

**Similar Readings**

**How to Import Stock Prices into Excel from Google Finance (3 Methods)****Add Stock Data Type in Excel (2 Effective Methods)****How to Track Stock Prices in Excel (2 Simple Methods)****Stock Return Frequency Distributions and Histograms in Excel****Get Stock Quote with Excel Add-in (With Easy Steps)**

### 3. Calculating Alpha Using CAPM Formula

We can calculate **Alpha** in Excel using the **CAPM** formula too. **CAPM **stands for Capital Asset Pricing Model. The formula to calculate **Alpha **is as follows.

**Alpha = Portfolio Returns â€“ Expected Rate of Return**

where,

**Expected Rate of Return = Risk Free Rate + Beta * (Market Returns â€“ Risk Free Rate)**

Now, our dataset includes **Portfolio Indicators **like **Returns of the Portfolio**, **Risk-Free Rate**, **Beta**, and **Market Return**. We can calculate **Alpha **using these parameters following the **CAPM **formula.

Now we need to calculate the **Expected Rate of Return**.

- Type the following formula in cell
**C11**and press**ENTER**to get the**Expected Rate of Return**.

`=C6+C7*(C8-C6)`

- After that, type the following formula in cell
**C12**and hit**ENTER**to get the value of**Alpha**.

`=C5-C11`

Hence, weâ€™ve got the **Alpha **value. From the figure, we can see that **Alpha **is **3.41%**.

**Read More: ****How Do You Automatically Update Stock Prices in Excel (3 Easy Methods)**

### 4. Calculating Alpha for Portfolio of Multiple Securities

Now, weâ€™ll calculate **Alpha** in Excel for another scenario. In this case, weâ€™ve **Portfolio Indicators **like **Market Return **and **Risk-Free Rate **data. Also, weâ€™re calculating **Alpha **for a portfolio of multiple securities. These securities include **NYSE**, **Nasdaq**, **BSE and CHX**. We have information like **Returns**, **Beta**, and **Weight in Portfolio**. We need to use these data to calculate the ultimate **Alpha**.

Our dataset looks like this.

We need to calculate the **Portfolio Returns**. This is the weighted summation of the **Returns**.

__Steps:__

- Type the following formula in cell
**C16**and hit**ENTER**to calculate the**Portfolio Returns**.

`=C10*E10+C11*E11+C12*E12+C13*E13`

Similarly, calculate **Portfolio Beta**.

- Type the following formula in cell
**C17**and hit**ENTER**to calculate the**Portfolio Beta**.

`=D10*E10+D11*E11+D12*E12+D13*E13`

Lastly, calculate the **Expected Rate of Return **in the same fashion.

- Type the following formula in cell
**C18**and hit**ENTER**.

`=C6+C17*(C5-C6)`

Weâ€™ll get the **Expected Rate of Return **in this way.

Finally, calculate **Alpha **by subtracting the **Expected Rate of Return **from the **Portfolio Returns**.

- Type the following formula in cell
**C19**and hit**ENTER**to do so.

`=C16-C18`

Weâ€™ve got the **Alpha **value by following this method. The value is **0.25%**.

**Read More: ****How to Get Current Stock Price of India in Excel**

## Takeaways from This Article

If youâ€™ve followed this article properly, youâ€™ll be able to:

- Calculate
**Cronbachâ€™s Alpha**for any questionnaires. - Compute
**Jensenâ€™s Alpha**for any portfolio.

## Things to Remember

While youâ€™re working on **Alpha**, you should be careful about some aspects.

- Use the
**COVARIANCE.P**and**VAR.P**functions properly. - Check the
**Anova**settings thoroughly.

## Conclusion

Iâ€™ve demonstrated how to calculate **Alpha **in Excel in this article. Iâ€™ve covered both **Cronbachâ€™s Alpha **and **Jensenâ€™s Alpha **in this article. Regarding **C Alpha**, you can use it in your own questionnaires now. Iâ€™ve shown possible real-life scenarios to do so. Again, weâ€™ve calculated **Alpha** for single security and for multiple securities too. If youâ€™ve followed this article thoroughly, I hope that you can use this knowledge to calculate **Alpha **by yourself. If you face any issues regarding that, please let us know in the comment section. Exceldemy will try to solve it for you. Have a good day!

## Related Articles

**Download Historical Stock Data into Excel (with Easy Steps)****How to Calculate Correlation between Two Stocks in Excel (3 Methods)****Stock return analysis using histograms & 4 skewness of histograms****Get Historical Data of NSE Stocks in Excel (2 Effective Ways)****How to Create Stock Comparison Chart in Excel (3 Easy Methods)**