Volatility is very common to the investors of the share market. Itâ€™s a way to get an idea to invest in a new company. It defines the risk of any stock or security. The higher volatility increases the risk of investment and vice-versa. In this article, we will discuss the volatility and how to calculate this in Excel with proper explanations.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**What Is Volatility?**

Volatility is the measure of the fluctuations of return of any stock or security over a time period is called volatility. Volatility is calculated based on the standard deviation or variance of returns over a time period. In the financial market, when the stock index increases or decreases more than 1% over a limited time that is called a volatile market. Simply, volatility is the measure of risk or uncertainty in the stock market.

__Types of Volatility:__

Volatility is classified into two types. Those are discussed in the below section.

**Historical Volatility:**

This calculates volatility based on past information. It predicts the future by analyzing past values. It does not consider future trends, market conditions, or needs. But It is not forward-looking. It is also called **historical volatility**.

**Implied Volatility:**

**The implied volatility** is forward-looking. It does not care the past performance and considers future expectations. It is also called projected volatility.

**2 Examples to Calculate Volatility in Excel**

We already discussed two types of volatility. We will explain the calculation of those two kinds of volatility in Excel with explanations.

**1. Calculation of Historical Volatility in Excel**

Here, we will explain the whole process to calculate the historical volatility. For this, we need to calculate the **Standard Deviation**. We can calculate the deviation subtraction or **LN function** of Excel. We will **collect stock data from Yahoo Finance**.Â Letâ€™s have a look at the below section for details.

**ðŸ“Œ ****Steps:**

- We collected the following sample data.

- We added a row on the right side.
- Use the following formula on
**Cell D6**.

`=C6/C5-1`

Here, we compared the price of the present day with the previous day and subtracted **1**. We can also use the following formula based on the **LN function**.

**)Â**

`=LN(C6/C5`

- We need to present the data in percentile form. Select
**Range D6:D19**. - Choose
**Percentage**from the**Number**group of the**HomeÂ**tab.

Now, increase or decrease the decimal points.

- After that, we will add two new columns to the dataset.

- Put the following formula on
**Cell F6**for standard deviation.

`=STDEV.S(D6:D19)Â `

- Finally, we will use the standard deviation to calculate volatility. We will apply the following formula to
**Cell G6**.

**Â**

`=$F$6*SQRT(252)`

We calculated the historical volatility. This volatility is also called **annualized volatility** as we used **252** in the equation. Here, the value of standard deviation is also called **daily volatility**. If we want to get monthly volatility, we need to use **22**. The formula will look like this:

**Â**

`=$F$6*SQRT(22)`

**2. Calculation of Implied Volatility**

To calculate implied volatility, we need to follow the **Black Scholes Model**. Using this model, we will calculate the call option price based on the assumed volatility. Further, we will modify the implied volatility until the call option price matches our expectations.

We will use the following based on the **Black Scholes Model**:

`V = SN (P1) â€“ N (P2) Ke^(-rt)`

Here,

**V**=Option Premium.**S**= Price of the stock.**K**= Strike Price.**r**= Risk-free Rate.**t**= Maturity Time.**e**=Exponential term.**P1**=Conditional Probability,**P2**= Probability of the option to expire on money.**N(P1), N(P2)**= Normal distribution of**P1**and**P2**respectively.

**ðŸ“Œ ****Steps:**

- First, we insert data on
**Underlaying Price**,**Strike Price**,**Volatility**,**Maturity Time**, and**Risk-Free-Rate**in the dataset for two cases.

The value of volatility is different in the two cases, and the rest are the same.

- Now calculate conditional probability,
**P1**using the following formula.

`=(LN(C5/C6)+(C8+0.5*C7^2)*C9)/(C7*SQRT(C9))`

- Now, we will use the
**SQRT function**to calculate probability,**P2**.

**Â**

`=C10-C7*SQRT(C9)`

- Now, we will calculate the normalized distribution on
**Cells C12**and**C13**using the following formulas.

**For Cell C12:**

`=NORM.S.DIST(C10,1)`

**For Cell C13:**

`=NORM.S.DIST(C11,1)`

We will use the **NORM.S.DIST** function to calculate the normal distribution in Excel.

- After that, we will calculate the
**Call option price**using the formula.

**Â**

`=C5*C12-C6*EXP(-C8*C9)*C13`

- Similarly, we perform calculations for
**Case 2**.

- Now, we add two new rows and set the
**Target Call Option Price**as**$65**.

- Use the following formula to get the implied volatility based on the
**Black Scholes Model**.

`=C7+(C16-C14)/(F14-C14)*(F7-C7)Â `

**Read More: ****How to Calculate Volatility for Black Scholes in Excel (2 Methods)**

**How to Calculate the Volatility of a Portfolio in Excel**

n this section, we will calculate portfolio volatility. For this, we will consider the closing stock price of **Microsoft** and **Tata Motors** for the last **10** days. Also, add that weight of **Microsoft **is **82%**, and **Tata Motors **is **18%**. Have a look at the below section.

**ðŸ“Œ ****Steps:**

- First, we insert data from
**Microsoft**and**Tata Motors**into the dataset.

- Then, we add two new columns in the dataset for calculating returns.
- We will apply the formula based on the
**LN function**to get a return.

**For Cell D6:**

`=LN(C6/C5)`

**For Cell F6:**

`=LN(E6/E5)`

- Now, we will calculate the weighted return. We will multiply the returns by their weight and sum them.

`=82%*D6+18%*F6Â `

- Then add a new row in the dataset for
**Portfolio Volatility**. Insert the following formula on**Cell D16**.

`=STDEV.S(G6:G14)`

Finally, we get the portfolio volatility. This is also called daily portfolio volatility. If we want monthly or annualized volatility, then we need to multiply by the square root of **22 **and **252** respectively.

**Conclusion**

In this article, we described the calculation of two types of volatility in Excel. We also discussed how to calculate portfolio volatility. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy**Â and give your suggestions in the comment box.