Volatility

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
404
Hi

I want to find out volatility in excel, can any body clarify me how to do it in Excel

A simple example is: volatility per anum is 25% and the no. of active days are 252, what is the volatility per day?

Regards,
Prakash
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not sure what volatility is but perhaps:

Code:
=(A2/(DATE(YEAR(TODAY())+1,1,1)-DATE(YEAR(TODAY()),1,1)))*B2
Book1
ABCD
1PercentDaysCurrent Percent
225%25217.26%
Sheet1


If you don't want to count weekends then perhaps (You will need the Analysis Toolpak add-in installed for this one):

Code:
=(A2/(NETWORKDAYS(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31))))*B2
Book1
ABCD
1PercentDaysCurrent Percent
225%25224.23%
Sheet1
 

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
404
Hi

Thanks for your reply. I want to find the volatility per day and result shoud be 1.58%.

Any help please.

Regards,
Prakash
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How do you get that answer?

In other words, if you were doing this without the benefit of Excel, how would you figure it out? Also, what's your definition of a year?

If you can tell us the manual way (by hand or calculator) of figuring it out, possibly we can translate that into an Excel formula.
 

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
404

ADVERTISEMENT

Hi

I downloaded a question with 4 options from a site and they have given the answer as 1.58%. I was trying to figure out how it has been arrived as it is very much applicable to my application. The question and the answer is furnished hereunder;

If the volatility per annum is 25% and the number of trading days per annum is 252, find the volatility per day.
a) 1.58%
b) 15.8%
c) 158%
d) 0.10


Regards,
Prakash
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

N Prakash

Active Member
Joined
Nov 7, 2003
Messages
404

ADVERTISEMENT

Hi Friend,

Thank you very much for the solution and very useful link. Can you give me the equivalent formula in Excel.

Regards,
Prakash.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Sorry, I don't understand the equation that is on that page, but if the 15.8 is acceptable then =A1/15.8
 

Watch MrExcel Video

Forum statistics

Threads
1,118,020
Messages
5,569,671
Members
412,286
Latest member
kychemist00
Top