Microsoft Excel comes with a lot of inbuilt functions that can be used in cell formulae and there are also a number of add-ins that provide specialised sets of functions to support statistical, numerical and financial analysis e.g. The Analysis ToolPak.

As an amateur investor, I am often interested in the Compound Annual Growth Rate (CAGR) calculation for comparing the smoothed rate of return of different investments. Surprisingly enough, Excel doesn’t have this in its kitbag, so I wrote my own. Below is my version of a user-defined function (UDF) that can be used in Excel.

``````Public Function CAGR( _
ByVal StartValue As Double, _
ByVal EndValue As Double, _
ByVal StartDate As Date, _
ByVal EndDate As Date) _
As Double

' Compute Compound Annual Growth Rate according to formula
' CAGR = (FV / PV ) ^ 1/n - 1 where n is number of years
' Developed by Warren Bain of Thought Croft Pty Ltd

CAGR = (EndValue / StartValue) _
^ (1 / ((EndDate - StartDate) / 365.25)) - 1
End Function
``````