A stock trader will generally have access to daily, weekly, monthly, or quarterly price data for a stock or a stock portfolio. Using this data he can calculate corresponding returns from the stock (daily, weekly, monthly, quarterly returns). He can use this data to calculate the standard deviation of the stock returns. The standard deviation so calculated will also be the standard deviation for that period. For example, using daily returns, we will calculate the standard deviation of daily returns. However, when we talk about volatility, we are most likely talking about annual standard deviation. Therefore, we will have to annualize the standard deviation calculated using the periodic data.
The annualized standard deviation of daily returns is calculated as follows:
Annualized Standard Deviation = Standard Deviation of Daily Returns * Square Root (250)
Here, we assumed that there were 250 trading days in the year. Depending on weekends and public holidays, this number will vary between 250 and 260.
So, if standard deviation of daily returns were 2%, the annualized volatility will be = 2%*Sqrt(250) = 31.6%
Similarly, we can calculate the annualized standard deviation using any periodic data.
For weekly returns, Annualized Standard Deviation = Standard Deviation of Weekly Returns * Sqrt(52).
For monthly returns, Annualized Standard Deviation = Standard Deviation of Monthly Returns * Sqrt(12).
For quarterly returns, Annualized Standard Deviation = Standard Deviation of Quarterly Returns * Sqrt(4).
Also read this article about how to calculate volatility in excel.
- #1
Hi, I'm trying to calculate monthly returns from daily returns using geometric mean. I would like to be able to check the date array in column A of the attached document and locate the corresponding return array if the month and the year of the dates in question are equal to the month's returns I'm trying to calculate. My guess is I would need to use an offset function where
the reference point changes every month to the first of the month in question and then calculate the height of the offset range based on how many return data points I have for that month. Not exactly sure how to build this formula out though. This is the formula I have to replace: I have uploaded a sample file here if you want to play around with
it: //file.io/jTEaYH Essentially, the formulas in E3 and E4 deal with the date filtering fine but they aren't calculating geometric means, it's just a normal sum. I want to incorporate the date filtering within the formula in G3 (=PRODUCT((1+B4:B23)^(1/n))-1). Also, the hard part is to
make it so that the offset function recognizes what is the first day of every month, makes that cell the reference cell, then sees what is the last date of the month in question (even if the current month isn't finished) and then creates the appropriate range of returns between the first and last day of the month in question. THanks a ton for any help! Or if you can think of any other way of doing this, it would be super appreciated.
B4:B23 would need to be replaced by the moving offset function for every month in question.
=PRODUCT((1+B4:B23)^(1/n))-1
04-21-2016, 08:09 AM #1
Registered User
How to convert daily to monthly returns?
Hi.
Does anyone know an easy way to convert my daily returns to monthly returns? Is it possible to just cumulate them? The originally row is LONG! :D Daily returns until today.
See excel sheet with date and daily returns.Thank you so much!
04-21-2016, 08:13 AM #2
Re: How to convert daily to monthly returns?
What is expected result - SUM of row 2 for a given month?
04-21-2016, 08:14 AM #3
Registered User
Re: How to convert daily to monthly returns?
Yes, I think so!
04-21-2016, 08:19 AM #4
Re: How to convert daily to monthly returns?
=SUMPRODUCT(--(MONTH($G$1:$DB$1)=4),$G$2:$DB$2)
will give SUM for April (Month=4)
04-21-2016, 10:51 AM #5
Registered User
Re: How to convert daily to monthly returns?
Hi JohnTopley.
I don't think that will work. Because I need a formula that I can draw sideways that can calculate the montly return each month until it reach 2016 in the originally spreadsheet.
Or did I misunderstand your formula?
04-21-2016, 11:04 AM #6
Re: How to convert daily to monthly returns?
Please add examples to your posted file.
04-21-2016, 04:52 PM #7
Registered User
Re: How to convert daily to monthly returns?
Hi again.
Thank you so much for help.
Take a look at the excel sheet. You will see in the bold column my calculation and how I want it to look like.
Take the precentage return each month. And not every day.04-21-2016, 04:56 PM #8
Registered User
Re: How to convert daily to monthly returns?
Updated montly example calculations!!!
See excel sheet. This is how it should look like after using the formula.
04-21-2016, 05:44 PM #9
Re: How to convert daily to monthly returns?
Here is an array formula* that will yield the expected results: *...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Below is a copy of your file with the formula applied to row 5 so that you can compare them to the ones in row 4.
Let me know if you have any questions.Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
04-21-2016, 05:51 PM #10
Re: How to convert daily to monthly returns?
Here's a non array formula:
It needs to be entered in column Y and dragged/copied right. Anything before that you'll need to calculate manually. It assumes that there will be less than 24 working days per month and that within a 24 working day period there would not be more than 1 month end.Please add reputation by clicking on the * if I have helped.
Please mark the thread SOLVED if your issue has been resolved.
Thanks, Glenn.04-22-2016, 05:22 AM #11
Registered User
Re: How to convert daily to monthly returns?
Hmm. First it looked like that formula worked fine. However, as I said earlier the originally row is up to february 2016.
If I drag the formula to a longer period it does not seem to work anymore?See excel sheet.
Or am I doing something wrong here? :/
04-22-2016, 05:41 AM #12
Re: How to convert daily to monthly returns?
It assumes that there will be less than 24 working days per month and that within a 24 working day period there would not be more than 1 month end.
You have more than 24 days in September 2000.04-22-2016, 05:48 AM #13
Registered User
Re: How to convert daily to monthly returns?
Aha, okey. The amount of days will change from month to month so then it will not work.
Im so sorry. But thanks anyways04-22-2016, 06:09 AM #14
Re: How to convert daily to monthly returns?
Ignore my last post (as it referred to wrong formula) : change formula to
=IF(MONTH(G1)=MONTH(H1),"",(G3-INDEX($F3:$ZZ3,,MATCH(MONTH(G1),MONTH($F1:$ZZ1),0)-1))/INDEX($F3:$ZZ3,,MATCH(MONTH(G1),MONTH($F1:$ZZ1),0)-1))
Need to extended the range
Enter with Ctrl+Shift+enter
04-22-2016, 07:29 AM #15
Re: How to convert daily to monthly returns?
Or try this in G4:
=IF(MONTH(G1)=MONTH(H1),"",G3/INDEX($F3:$EZ3,MATCH(10^307,$F$4:F4))-1)
Normal Enter, copy across