Annual return formula

[spaceage]

3.51%
3.45%
6.60%
3.04%
3.88%
-7.79%
6.14%
9.08%
-3.63%
0.58%
-9.72%
3.55%

when i enter the formula =product(A1:A12+1)-1, the cell shows the error
#value.

where am I wrong

 

[JLatham]

The error is being generated in the PRODUCT(A1:A12+1) portion of your
formula. What exactly are you trying to do? are you trying to add 1 to each
of the 12 values and then get their product? Trying to convert negative
result to positive?
Might also provide us with the result that you expect given the values in
your example, that would help us see if our offered solution(s) actually work
properly for you.

 

[spaceage]

You are correct. I add 1 to each of the 12 values.Find the product after
adding and then from the product of this, I deduct one to get the return. The
return must be 18.19%.
[(1+0.0351)*(1+0.0345)*(1+0.066)*(1+0.0304)*(1+0.0388)*(1-0.0779).......................]-1 .

 

[David Biddulph]

You need to enter the formula as an array formula: Control Shift Enter.

 

[spaceage]

still does not work.
--
rgds

You need to enter the formula as an array formula: Control Shift Enter.

 

[David Biddulph]

It does for me, and returns the answer 18.20%.

When you did Control Shift Enter, did Excel put curly brackets { } around
the formula to show that it recognised it as an array formula?

 

[Rick Rothstein \(MVP - VB\)]

It works for me. Using the values you posted early and committing your
formula with Ctrl+Shift+Enter yields an answer of 0.181966527362382 which,
when formatted as a percentage to 2 decimal places, is 18.20% (not the
18.19% you indicated, but close enough I would say).

Rick

 

[spaceage]

is it possible my numbers as stored as text.
I already did text to column to convert text format into numbers.
or is it something related to add-ins

 

[Don Guillett]

Sub fixmynums()
On Error Resume Next
For Each C In Selection 'Range("a1:a25")
C.NumberFormat = "General"
C.Value = CDbl(C)
Next
End Sub

 

[joeu2004]

is it possible my numbers as stored as text.

First, PRODUCT(A1:A12+1) works for me even when A1:A12 are formatted
as text. I am using Office Excel 2003.

Second, you can verify if that is your problem either by trying to
format a cell -- the Format Number menu shows you the current format
-- or by entering the formula =TYPE(A1) into B1 and copy down through
B12. Type 1 is numeric; type 2 is text.

As everyone has said: the most likely problem is that you did not
enter the formula as an array formula in the first place. I wonder if
you are failing to make it an array formula subsequently. It is not
sufficient to select the cell and press ctrl-shift-Enter at this
point.

You must select the cell, press F2, then press ctrl-shift-Enter.
Afterwards, be sure there are curly braces around the formula when you
select the cell. Note that you cannot type the curly braces
yourself. You must use ctrl-shift-Enter.

 

[Fred Smith]

If you have monthly returns, the formula you are using will calculate the
annual return.

However, if your periods are something other than monthly, you will also
need to annualize your total return using the Rate function.

Regards,
Fred.

 

[msnews.microsoft.com]

Something like:
=PRODUCT(B4:B15+1)^(1/numyears)-1


ctrl-shift- enter.