looking to calculate an annualised return from 36 monthly returns and an annualised standard deviation
Your question is difficult to answer dispositively because you have not defined your terms and provided a numerical example.
What do you mean by "monthly return"? People use the term differently. How do you calculate "return from 36 monthly returns"? How do you calculate the std dev?
Ostensibly, if the cell R1 is the
average (TBD) of 36 monthly returns (TBD), the annualized average annualized return is =(1+R1)^12 - 1.
And if the cell S1 is the std dev (TBD) of 36 monthly returns, the annualized std dev is =S1*SQRT(12), using the "square root of time" rule.
But those formulas make a lot of assumptions about your terms that you did not explain.
Refer to the table at the end. Given only monthly returns in C3:C38:
1. The (compounded) average monthly return is =GEOMEAN(1+C3:C38)-1, which is
array-entered (press
ctrl+shift+Enter instead of just Enter).
2. The (compounded) annualized return is =GEOMEAN(1+C3:C38)^12 - 1, which is
array-entered.
3. The std dev of the monthly returns is =EXP(STDEV(LN(1+C3:C38)))-1, which is
array-entered.
But many people calculate simply =STDEV(C3:C38), which is wrong, IMHO.
4. The annualized std dev of the monthly returns is =EXP(STDEV(LN(1+C3:C38))*SQRT(12))-1, which is
array-entered.
But many people calculate simply =STDEV(C3:C38)*SQRT(12), which is wrong, IMHO.
To explain....
-----
When you say "return from 36 monthly returns", do you mean an
average monthly return, as I did above? Or do you mean a
total return over 36 months?
And if you mean an average monthly return, do you mean an arithmetic (
simple) return or a geometric (
compounded) return?
Consider the S&P 500 index. On 1 June 2017, it was 2423.41 (according to finance.yahoo.com). On 1 June 2020, it was 3190.14.
The 36-month
total return is 3190.14/2423.41 - 1 = 31.6385%.
(In this context, I am not including dividends and capital gains distributions in the "total return".)
If that is in cell R1, the annualized return is =(1+R1)^(12/36) - 1, or simply =(1+R1)^(1/3) - 1, which is 9.5959%
The annualized return based on the 36-month total return (as I have used it here) is the correct compounded annual return.
-----
If you have the S&P 500 indexes in B2:B38, the
compounded average monthly return is =(B38/B2)^(1/36) - 1 in R1, which is 0.7665%.
Alternatively, if you only have the monthly returns in C3:C38, the compounded average monthly return is =GEOMEAN(1+C3:C38)-1 in R1, which is
array-entered (press
ctrl+shift+Enter instead of just Enter) .
As noted above, the annualized return is =(1+R1)^12 - 1, which is 9.5959%, the same as the annualized total return.
-----
In contrast, the
simple average monthly return is =AVERAGE(C3:C38) in R1.
Many people use the same formula to annualize the simple monthly return, namely =(1+R1)^12 - 1, which is 11.1855%.
That is wrong, when compared to the annualized total return.
But =12*AVERAGE(C3:C38), which is 10.6500%, is also wrong when compared to the annualized total return.
Bottom line: Usually, we should annualize the
compounded average monthly return or the total return.
-----
Most people use =STDEV(C3:C38) in S1 to calculate the std dev of the monthly returns, which is 1.2526%.
(For some purposes, we might use STDEV.P instead.)
And as I noted above, the annualized std dev is =S1*SQRT(12), which is 4.3391%.
But the STDEV(P) function uses AVERAGE(C3:C38) internally. And as I explained above, that is the simple average monthly return, not the compounded average monthly return.
Consequently, this method of calculating the std dev is inconsistent with using the compounded average monthly return or total return.
-----
Instead, I believe it is more consistent to calculate the std dev of the monthly "log returns" in order to calculate the annualized std dev.
Given a monthly return in C3, the log return is =LN(1+C3).
The std dev of the log returns is =STDEV(LN(1+C3:C38)).
The annualized std dev of the log returns is =STDEV(LN(1+C3:C38))*SQRT(12).
And the annualized std dev of the (normal) returns is =EXP(STDEV(LN(1+C3:C38))*SQRT(12))-1, which is 38.0654% (!).
All of those formulas are
array-entered.
Note that the annualized std dev is significantly different. Yet, I believe that is the correct methodology, based on the underlying statistics from which the "square root of time" rule is derived.
IMHO, the real problem is: it is wrong to try to predict the std dev (a measure of volatility) for one frequency based on another.
For example, intraday prices might vary wildly, yet the closing price might be nearly the same as the previous day. Similarly with monthly v. annualized data.
That said, if we look at 20 years of monthly data, the annualized std dev based on monthly log returns is 16.3185%.
That is similar to the actual std dev based on annual log returns, namely 16.2197%.
-----
Aside....
Similarly, the average monthly log return is =AVERAGE(LN(1+C3:C38)).
The annualized log return is =12*AVERAGE(LN(1+C3:C38)).
And the annualized (normal) return is =EXP(12*AVERAGE(LN(1+C3:C38)))-1, which is 0.9559%.
All of those formulas are
array-entered.
Since that is the same as to the annualized total return, we can use the simpler formulas above in order to calculate the average monthly return and the annualized return.
-----
Rich (BB code):
Formulas:
C3: =B3/B2-1
F3: =B38/B2-1
F4: =(1+F3)^(1/36)-1
F5: =GEOMEAN(1+C3:C38)-1
F6: =AVERAGE(LN(1+C3:C38))
F7: =EXP(F6)-1
F8: =STDEV(LN(1+C3:C6))
F9: =EXP(F8)-1
F11: =AVERAGE(C3:C38)
F13: =STDEV(C3:C8)
G3: =(1+F3)^(1/3)-1
G4: =(1+F4)^12-1
G6: =EXP(12*F6)-1
G8: =EXP(F8*SQRT(12)-1)
G11: =(1+F11)^12-1
G12: =12*F11
G13: =F13*SQRT(12)
