Annual return formula

  • Thread starter Thread starter spaceage
  • Start date Start date
S

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
 
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.
 
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 .
 
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
 
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
 
Sub fixmynums()
On Error Resume Next
For Each C In Selection 'Range("a1:a25")
C.NumberFormat = "General"
C.Value = CDbl(C)
Next
End Sub
 
is it possible my numbers as stored as text.
Click to expand...

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.
 
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.
 
Back
Top