Hello again,
Well, it doesn't quite do that. You're talking compounding growth from 1985-1990? And you want to do that scenario for each state for every possible combination of years?
That would require a ton of iterations on each state. It's doable, and my CAGR example is how you do it, but I didn't set it up to easily do that. You'd need a pretty big table for each state (unless you did this with VBA) to accomplish this. I suppose I could do a UDF...
It does now. It's alive!
This is somewhere between the craziest and most interesting questions I've ever seen asked here, imo. Did someone in good faith ask you to do this by hand for every possible combination of years? That would have to take you at least a full working week, if not longer, to figure out.
You could do this with Worksheet Functions, by setting up an Amortization table like structure for every possible year-combination, but you'd end up with an unmanageable amount of data for each state, it would be unwieldy, at best.
So, it's UDF time. This has to be used on data that's cross-tabbed, you need years across the header row, and like you said, a column of states and the principal amounts in the columns that follow.
So, in a normal module (Alt-F11->Insert->Module) place the following UDF:
Code:
Public Function CAGRThresh( _
ByRef rngYears As Range, _
ByRef rngPrincipal As Range, _
ByVal curThresh As Currency) As String
Dim varYears() As Variant, varPrincipal() As Variant
Dim strRet() As String
Dim i As Long, j As Long, lngCount As Long
Dim lngUpper As Long
Dim curCAGR As Currency
Let varYears = rngYears.Value
Let varPrincipal = rngPrincipal.Value
Let lngUpper = UBound(varPrincipal, 2)
ReDim strRet(1 To (lngUpper ^ 2 * 0.5 + lngUpper * -0.5))
For i = LBound(varPrincipal, 2) To lngUpper - 1
For j = i + 1 To lngUpper
Let curCAGR = ((varPrincipal(1, j) / _
varPrincipal(1, i)) ^ (1 / (j - i))) - 1
If curCAGR >= curThresh Then
Let strRet(lngCount + 1) = varYears(1, i) & _
"-" & varYears(1, j) & ": " & _
Format$(curCAGR, "0.00%")
Let lngCount = lngCount + 1
End If
Next j
Next i
If lngCount > 0 Then
ReDim Preserve strRet(1 To lngCount)
Let CAGRThresh = Join$(strRet, ", ")
Else: Let CAGRThresh = "N/A"
End If
End Function
And yes, I did hurt my brain writing this, but a fascinating question... Even figuring out the maximum size of the return array was an interesting endeavour!
And use in a Worksheet as such:
=cagrthresh($B$1:$G$1,B2:G2,20%)
Where B1:G1 is your header row with dates, B2:G2 has the principal amounts per state and 20% is your threshold (change as necessary). If you use dollar signs (absolute referencing) on the header row (dates), you can now copy down. Your header row needs to be the exact same number of columns as your principal columns, or be assured this won't work, as written.
This returns a potentially long string of periods with CAGRs that meet or exceed your stated threshhold. The format is as follows:
Year X-Year Y: CAGR, Year X-Year Y:CAGR, etc...
E.g., here's one return on my tests:
1991-1993: 36.93%, 1992-1993: 50.00%
I couldn't think of a better summary format... And you can use Text-to-Columns to parse this, if you want (
: and
, being your delimeters).
It returns every single possible annual combination and the CAGR for every combination where the CAGR is equal to or greater than your threshold. It's pretty **** fast too, a nice benefit. There's a week's work done in about a millisecond.
If there are more questions or you're having trouble implementing this, let me/us know. I had better get an A for this one, one of the cooler algorithms I've written in a while.
