Annualizing investment returns from monthly return numbers

[robocarroll]

I have monthly investment return numbers (not monthly values) and need to know how to annualize them in Excel.

 

[Seti]

Assuming that your monthly returns are in A1:A12 for one years worth, you can try this array formula:

=PRODUCT(1+A1:A12)

You need to use Control-Shift Enter once you have completed the formula rather than just Enter and it should look like this:

{=PRODUCT(1+A1:A12)}

as Excel adds the curly braces to signify an array formula.

 

[Jay Petrulis]

From the Analysis Toolpak, use the FVSCHEDULE function

=FVSCHEDULE(1,Rng)^(MIN(1,12/COUNT(Rng)))-1

In addition, Seti's PRODUCT formula will give you the correct cumulative, unannualized result. To annualize it, do the following:

=PRODUCT(1+Rng)^(MIN(1,12/COUNT(Rng)))-1

(assumes 12 monthly periods per year, else change the 12 to the number per year)

This must be array-entered.

The convention is to only annualize if you have > than one year of data. If you want sub annual periods annualized, take out the MIN function call, leaving only the 12/COUNT(Rng) as the exponent

 

[robocarroll]

I tried the =product(1+a1:a12) and got a value that doesn't seem to make any sense.

Here's my monthly data:
1.53
7.26
-3.43
-2.24
0.99
-1.75
-2.98
4.68
3.04
4.25
1.60
-2.44

I put in the formula and it returned 58286.83?? Did I make a formula error? Thanks for your continued help.

 

[robocarroll]

I just tried

=FVSCHEDULE(1,Rng)^(MIN(1,12/COUNT(Rng)))-1

and got the #NAME error. Where am I going wrong?

 

[Jay Petrulis]

Your return data is not in mathematical percentage form, so you must convert it. You can do so in the formula.

=PRODUCT(1+A1:A12/100)

This needs to be array-entered and will give you the wealth relative. Subtract 1 from the result to give you the percentage. Adjust the formula to return an annualized result.

The FVSCHEDULE function is part of the Analysis Toolpak add-in, so that must be loaded before you can use it. Since your data is not in percentages, FVSCHEDULE will need to be array entered, so no need to bother with it, just use PRODUCT.

Also, I used a dummy name Rng. Unless you have definied your range of data as such, it may give you the error.

 

[robocarroll]

I am revealing my relative newby status with this question, but what did you mean by "array entered" data?

 

[Reid]

Jay meant entering the formula as an array formula (i.e. one that is applied to an array, or range, as opposed to a single cell at a time). In order to enter a formula as an array formula, instead of simply hitting ENTER, press CTRL+SHIFT+ENTER. Once the formula is entered as an array formula, you will see it bracketed by {curly parentheses} in the formula bar. You never type these curly parentheses. Excel enters them automatically. Hope this helps.

 

[Function]

Here is link to VBA code that turns this formula into a function

http://www.ozgrid.com/forum/showthread.php?p=506140#post506140

Now you can calculate the rate of return by just typing "=CROR(A1:A10)"