Formula Help (2000)

[jlkirk]

An easy one: I have a column of 10 numbers, all positive. These correspond to annual returns on stocks. What is the formula for figuring out the average percentage return per year, without entering the cost of the security as the initial negative cash outlay? Again, all of the returns are positive, and I do n ot want to enter the initial cast of the security.

[sdckapr]

I don't think I understand completely.
If this column is the "annual returns on stocks" and you want the "average percentage return per year"

Isn't it just the average (of this column) formated to percent or the average (of this column)*100 if you want the "percent" number?

If you are trying to "weight" this is some way you need to tell us more information on how you want to weight it or if this is a percent change from something we need more info on what you are after.

Steve

[chipshot]

I would use =FVSCHEDULE(1,A1:A10)^(1/10)-1. This would work fine with negative returns as well.

[jlkirk]

OK-let's try an example (Chip, I couldn't get it to work using your suggestion): Let's try using the IRR function, with an initial investment of 10,000 (negative, of course), with 9 yearly cash flows of 25. When I try and use the IRR formula, I get a "Div/0" message.
What gives, anybody know?

[sdckapr]

From Help
<blockquote><hr>IRR(values,guess)

Values is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.

[jlkirk]

The first value is a negative and the remainder are positive.

[HansV]

With an initial investment of $10,000, the internal rate of return of 9 cash flows of $25 is too negative. Try cash flows of $1,000 or $2,000 or so.

[jlkirk]

Hans,
While I agree, I can only work with what I have. Shouldn't excel still return a value, even though it is "too negative"?
Thanks,
Jeff

[chipshot]

Are your 10 numbers percentage returns or dollar returns? If they are percentage returns, my formula should work. However, I should have added that I think the FVSCHEDULE function is part of the Analysis ToolPak. You may need to go to ToolsAdd-Ins and check the box that says Analysis ToolPak.

If your numbers are dollar returns, then your example implies that you invested 10,000, received 25 dollars per year for 9 years and then nothing else. That would be a dreadful return. I assume that you still own the investment at the end of 9 years. In that case, your 9th year cashflow should be something like 10,025.

[jlkirk]

They are dollar returns, but let me try another way: let's assume the first year the value of the security is 10,000, at the end of the second, the value is 10,100, the third, 10,200, increasing by 100 each year for the nine years. What would be my average return per year, and how would I calculate it?
Sorry for this being so confusing.
Jeff

[HansV]

To compute IRR is not like solving a simple equation. It involves iterating. The algorithm Excel uses isn't accurate enough for extreme situations, but you can help it by providing a guess as second argument. Try a negative guess, for example

=IRR(A1:A10,-60%)

But I'll leave you in the capable hands of <!profile=chipshot>chipshot<!/profile>, who knows much more about financial matters than I do.

[sdckapr]

Isn't your average return $100 / year? You made $100 each year. Or is the $100 not a return?

If your values are in A1:A10 you could calculate this with the array (confirm with ctrl-shift-enter):
=AVERAGE(A2:A10-A1:A9)

Steve

[chipshot]

In this example, the return in the first year is (10100-10000)/10000=1%. In the second year, the return is (10200-10100)/10100=0.99%. You could calc the average of those percentage returns using the FVSCHEDULE function I cited above.

In your original post, you said you wanted the average percentage return. In this case, you wouldn't care about the intermediate points. Just take the final value of your investment (say 12000 at the end of 9 years) and calc (12000/10000)^(1/9)-1=2.05%

On the other hand, if you have a 10000 investment that pays you a dividend of 100 dollars every year, then you need to use the IRR formula. Your input into the IRR formula would be a range that has -10000 at the beginning, 8 years of +100, and then a ninth value of 100+value of investment. Since 100 is 1% of 10000, I would put in a guess of .01 as the second argument of the IRR function.

I hope this helps. If not, perhaps you could supply us with an actual example of what data you have.

[sdckapr]

If I use this array (ctrl-shift-enter) to get the average percent change
=AVERAGE((E2:E10-E1:E9)/E1:E9)

I get 0.962% which is the average of the 9 individual values: 1%, 0.99, 0.98, ... 0.93

If I use =FVSCHEDULE(1,A1:A10)^(1/10)-1 as you list, I get 10446.05.

Steve

[chipshot]

The correct usage of the function would be the array formula:

=FVSCHEDULE(1,(A2:A10-A1:A9)/A1:A9)^(1/9)-1

If i_1 is the rate of return in period 1 [i.e. (A2-A1)/A1)], then FVSCHEDULE does (1+i_1)*(1+i_2)*(1+i_3)*....*(1+i_9). Taking the 9th root and subtracting 1 gives you an average compounded rate of return, i*, such that (1+i*)^9 = the product of the 9 distinct (1+i_x) terms. Steve's answer is .962131%. My answer is .962129% Obviously in this example, the answers are quite close but this is usually not the case.

I use FVSCHEDULE when I am presented with a series of percentage returns. In this case where we are presented with a series of dollar amounts, you could think of this as

=PRODUCT(A2:A10/A1:A9)^(1/9)-1 which reduces to =(A10/A1)^(1/9)-1