Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    annual average growth (Excel 97 or 2000)

    Is there an Annual Average Growth formula in Excel. We haven't been able to find it.
    Thanks...

  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: annual average growth (Excel 97 or 2000)

    If you are basing it on data, you could calculate it with SLOPE or LINEST

    Steve

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: annual average growth (Excel 97 or 2000)

    On the other hand, If you only know the amount you started with, the amount you ended up with and how many years it took
    then the following home grown Function will give a good approximation...

    Function AvgYearlyReturn(BeginInvt As Single, EndInvt As Single, NumYears As Single) As String
    Dim AvgBal As Single
    Dim AvgIncrease As Single

    AvgBal = ((EndInvt - BeginInvt) / 2) + BeginInvt
    AvgIncrease = (EndInvt - BeginInvt) / NumYears
    AvgYearlyReturn = Format$(AvgIncrease / AvgBal, "##0.000%")

    End Function

    'You can call the function with this sub...
    'It uses $1000 as the beginning investment and $2500 as the Amount its worth after 5 1/2 years.

    Sub FigureIt()
    Dim MyReturn As String
    MyReturn = AvgYearlyReturn(1000, 2500, 5.5)
    MsgBox "Average return was " & MyReturn & " "
    End Sub
    '-------------------------------------------------------------

    Regards,

    Jim Cone
    San Francisco, CA

  4. #4
    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: annual average growth (Excel 97 or 2000)

    You could just use the formula (based on your calcs)

    = (e-[img]/forums/images/smilies/cool.gif[/img] / (e+[img]/forums/images/smilies/cool.gif[/img] *2 / y

    b= beg bal (1000)
    e= end balance (2500)
    y = years (5.5)

    without using a custom function

    If you are looking to solve for x in the equation:
    B * (1 + x) ^ y = E
    given b,e, and y then:
    x =EXP(LN(e/[img]/forums/images/smilies/cool.gif[/img]/y)-1


    Steve

Posting Permissions

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