Volatility

[N Prakash]

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

 

[Scott Huish]

Not sure what volatility is but perhaps:

=(A2/(DATE(YEAR(TODAY())+1,1,1)-DATE(YEAR(TODAY()),1,1)))*B2

Book1
	A	B	C	D
1	Percent	Days	Current Percent
2	25%	252	17.26%
Sheet1

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

=(A2/(NETWORKDAYS(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31))))*B2

Book1
	A	B	C	D
1	Percent	Days	Current Percent
2	25%	252	24.23%
Sheet1

 

[N Prakash]

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]

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]

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]

This appears to be the link the op is talking about (it is in PDF format), question 4.

www.iibf.org.in/portal/documents/caiib_sample_questions.pdf

I think I found the answer here:

www.cannonassets.co.za/RN CAM-KCM 03-04.pdf

There is a complicated equation at the bottom of page 9, but the last sentence on that page says this:

Thus, annual volatility is equal to roughly 15.8 times daily volatility.

Based on that, dividing your 25% by 15.8 gives you 1.58%

 

[N Prakash]

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]

Sorry, I don't understand the equation that is on that page, but if the 15.8 is acceptable then =A1/15.8

 

[Chitosunday]

=.25/SQRT(252)