Results 1 to 15 of 19
Thread: Formula Help (2000)

20031104, 15:49 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Formula Help (2000)
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.

20031104, 16:02 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (2000)
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

20031104, 16:22 #3
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (2000)
I would use =FVSCHEDULE(1,A1:A10)^(1/10)1. This would work fine with negative returns as well.

20031105, 13:33 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Formula Help (2000)
OKlet'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?

20031105, 14:31 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (2000)
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.

20031105, 14:32 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Formula Help (2000)
The first value is a negative and the remainder are positive.

20031105, 14:45 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Help (2000)
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.

20031105, 14:47 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Formula Help (2000)
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

20031105, 14:53 #9
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (2000)
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 ToolsAddIns 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.

20031105, 15:03 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Formula Help (2000)
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

20031105, 15:06 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula Help (2000)
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.

20031105, 15:40 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (2000)
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 ctrlshiftenter):
=AVERAGE(A2:A10A1:A9)
Steve

20031105, 16:01 #13
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (2000)
In this example, the return in the first year is (1010010000)/10000=1%. In the second year, the return is (1020010100)/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.

20031105, 17:00 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula Help (2000)
If I use this array (ctrlshiftenter) to get the average percent change
=AVERAGE((E2:E10E1: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

20031105, 18:52 #15
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Help (2000)
The correct usage of the function would be the array formula:
=FVSCHEDULE(1,(A2:A10A1:A9)/A1:A9)^(1/9)1
If i_1 is the rate of return in period 1 [i.e. (A2A1)/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