Thread: annual average growth (Excel 97 or 2000)

1. 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. 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. 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. 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
•