Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    2 Star Lounger
    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.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula Help (2000)

    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?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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.

  6. #6
    Bronze Lounger
    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.

  7. #7
    Plutonium Lounger
    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.

  8. #8
    Bronze Lounger
    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

  9. #9
    2 Star Lounger
    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 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.

  10. #10
    Bronze Lounger
    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

  11. #11
    Plutonium Lounger
    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.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter):
    =AVERAGE(A2:A10-A1:A9)

    Steve

  13. #13
    2 Star Lounger
    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 (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.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    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 (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

  15. #15
    2 Star Lounger
    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: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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •