CAGR, why it's not in Excel

[krehkop]

I'm running Excel 2003 (Windows 2000). I've researched (online) the compound annual growth rate ("CAGR") and there appears to be a =CAGR() function in Excel, however, not in the version I have.

Does anyone know how I can have this function available in my version of excel?

Regards,
Kirk

 

[Oaktree]

See:

http://www.mrexcel.com/archive2/68400/79239.htm

 

[krehkop]

Thank you but I don't know how to put the code in a module. Are there modules within Excel or is that person talking about a VBA module? IF VBA, I don't get how that will work...

 

[Oaktree]

It may be easier to use the RATE function as in Andrew's suggestion... but, fairwinds was referring to VBA.

Press alt + F11 to bring up VBA, go to insert --> module, paste fairwinds' code, and close out of VBA.

Then, you'd call fairwinds' UDF with =CAGR(A1,A2,A3) where A1 is the starting value, A2 is the ending value, and A3 is the number of periods.

Note that, without application.volatile in the code, the CAGR UDF will not automatically recalculate when you change A1, A2, or A3. Note also that, depending on their security settings, adding macros to your project may cause issues if you distribute the workbook to other people (i.e. you may get phone calls about enabling macros, etc.)

 

[Milo_Minderbinder]

In case you're interested here is a different CAGR UDF where you select the range of cells and ignores blank cells in the range. This way you don't have to count periods and change it if you insert cells somewhere in the string.

Public Function CAGR(ByVal myRng As Range) As Double

Dim Fst As String
Dim lst As String
Dim pos As Single
Dim Periods As Integer

Application.Volatile
pos = InStr(myRng.Address(False, False), ":")
lst = Mid(myRng.Address(False, False), pos + 1, _
    Len(myRng.Address(False, False)))
Fst = Left(myRng.Address(False, False), pos - 1)

Periods = myRng.Count
For Each cl In myRng
If cl = "" Then
Periods = Periods - 1
End If
Next cl

CAGR = ((Range(lst) / Range(Fst)) ^ (1 / (Periods - 1))) - 1

End Function

 

[Datsmart]

Thank you but I don't know how to put the code in a module. Are there modules within Excel or is that person talking about a VBA module? IF VBA, I don't get how that will work...

Take a look at this Demo for VBA Overview:
http://www.datapigtechnologies.com/flashfiles/vbaoverview.html

 

[krehkop]

Thanks all!!! Kirk