Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Percentage Increase in A Period

This is a discussion on Percentage Increase in A Period within the Excel Questions forums, part of the Question Forums category; Lol. No offense taken. I'm sure I wouldn't. I'm definitely a novice. Compound is what I would need....

  1. #11
    New Member
    Join Date
    May 2008
    Posts
    5

    Default Re: Percentage Increase in A Period

    Lol. No offense taken. I'm sure I wouldn't. I'm definitely a novice. Compound is what I would need.

  2. #12
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: Percentage Increase in A Period

    Homework, eh? Tsk, tsk.

    Okay, I'm sending you the example.

    I'll probably still whip up a VBA example tomorrow, because it's an interesting problem.

    But don't hand that in if your prof knows anything about VBA, it's probably going to be obvious that you didn't write it.

  3. #13
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: Percentage Increase in A Period

    Hello again,

    Quote Originally Posted by NateO View Post
    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.
    Last edited by NateO; Sep 6th, 2008 at 02:05 PM. Reason: Typo - fixed

Page 2 of 2 FirstFirst 12

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com