calculating 3 Year return with monthly returns

[RajiSingh]

Hi Everyone,

First off I wanted to thank everyone for the forum... This place is awesome.. I am learning a lot thanks to all you guys!!!

I have monthly returns and wanted to calculate a 3 & 5 Year annualized return.

Could anyone help me?

Please see attached


31-Jan-14 0.71
28-Feb-14 4.32
31-Mar-14 1.63
30-Apr-14 0.89
31-May-14 (0.03)
30-Jun-14 0.20
31-Jul-14 (0.94)
31-Aug-14 1.18
30-Sep-14 (0.62)
31-Oct-14 0.18
30-Nov-14 2.10
31-Dec-14 0.33
31-Jan-15 6.50
28-Feb-15 4.43
31-Mar-15 (0.57)
30-Apr-15 (1.20)
31-May-15 2.36
30-Jun-15 (2.19)
31-Jul-15 3.82
31-Aug-15 (3.59)
30-Sep-15 (4.13)
31-Oct-15 5.91
30-Nov-15 1.51
31-Dec-15 0.14
31-Jan-16 (5.87)
29-Feb-16 (2.29)
31-Mar-16 2.79
30-Apr-16 0.54
31-May-16 3.62
30-Jun-16 (2.07)
31-Jul-16 3.75
31-Aug-16 1.84
30-Sep-16 1.34
31-Oct-16 1.05
30-Nov-16 4.01
31-Dec-16 2.25
31-Jan-17 0.01
28-Feb-17 2.58
31-Mar-17 1.71
30-Apr-17 4.73
31-May-17 (0.93)
30-Jun-17 (3.87)
31-Jul-17 (0.66)
31-Aug-17 (0.53)
30-Sep-17 3.79
31-Oct-17 5.35
30-Nov-17 2.15
31-Dec-17 (0.38)
31-Jan-18 3.11
28-Feb-18 (0.59)
31-Mar-18 (1.09)
30-Apr-18 1.65
31-May-18 1.02
30-Jun-18 0.20
31-Jul-18 1.94
31-Aug-18 (0.24)
30-Sep-18 (0.20)
31-Oct-18 (6.63)
30-Nov-18 1.46
31-Dec-18 (3.58)




[TABLE="width: 252"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

I have monthly returns and wanted to calculate a 3 & 5 Year annualized return.

You have 60 months (5 years) of data. I presume that the returns are percentages written as decimal numbers. (Bad practice, IMHO.) For example, 0.71 is to be interpreted as 0.71%.

In that case, the 5-year annualized return is (normally-entered):

=PRODUCT(INDEX(1+B2:B61%,0,1))^(1/5) - 1

which __I__ would format as Percentage. You might write =(PRODUCT...-1)*100, formatted as Number. (Sigh.)

FYI, the use of INDEX(...,0,1) is a trick to avoid array-entering the formula. The use of "%" is shorthand for "/100".

I'm not sure what you mean by "3-year annualize return".

Brokers usually mean "the most-recent 3 years". In that case, I would write (normally-entered):

=PRODUCT(INDEX(1+B26:B61%,0,1))^(1/3) - 1