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.

## 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%**.

### 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%**.

### 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%**.

## 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.

**Download Practice Workbook**

You can download our practice workbook from here for free!

## 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

- How to Add Stock Data Type in Excel
- [Solved]: Data Types Stocks and Geography Missing Problem in Excel
- How to Calculate Beta in Excel
- How to Calculate CAPM Beta in Excel
- How to Download Historical Stock Data into Excel

**<< Go Back to Stocks In Excel| ****Excel for Finance**** | ****Learn Excel**

selamat malam, saya ingin bertanya terkait jensen alpha untuk kebutuhan skripsi, apa perbedaan dari portofolio return dan market return? lalu bagaimana cara menghitung market return? terimakasih, mohon bantuannya.

Hey, Ayu Indah! Thank you for your query.

Regarding your query, Market return is basically the return that is generated by a broad market index. It deals with the whole market for a particular time period and calculates the value of the market as a whole.

On the other hand, the Portfolio return basically indicates the return generated by a specific investment portfolio or fund.

To differentiate between these two returns, market return is the benchmark that is followed when calculating portfolio return. When calculating market return, no risk factor is taken into consideration. But, when calculating portfolio return, the risk factors are calculated and the market return is taken as a benchmark to determine the gains and losses of the investment portfolio.

After calculating both these returns, Alpha can be calculated. And, with positive alpha, it can be decided that there was outperformance to gain profits. And, negative alpha suggests that, with risk factors, the investment portfolio can result in losses.

To calculate market return, the whole market is taken into consideration and a particular time is considered. So, to get this, the beginning value of the market during the timeline is recorded and the ending value of the market during the timeline is also recorded. Following, the total dividends or net income is calculated during the timeline.

Then the market return is calculated as follows:

Total Market Return = [(Ending Value – Beginning Value) + Dividends]/ Beginning ValueTo get the value in percentage, multiply the previous result by 100.

So,

Market Return (in Percentage) = Total Market Return * 100Hope, you will now be able to differentiate between market return and portfolio return and you will be able to calculate market return. If you have any further queries, please feel free to ask. Thank you!

With Regards,

Md. Tanjim Reza Tanim