Compounding without values with a custom function

[rbrady]

Hello,

Is it possible to calculate the compound annual growth rate (CAGR) for a period with only the returns by percentage instead of the actual values?

As an example, I have attached a workbook with quarterly returns for a five-year period. The way I used to calculate the CAGR was to create arbitrary/made-up intermediate values and then use the RATE function. (I have looked at the XIRR function, but that does not appear to meet my needs because I only know the return by percent and not actual values.)

Would a custom/user-defined function (UDF) work for this? I imagine a small function that takes arguments for a PeriodEnd date and a PeriodLength. Then it loops to calculate an array full of the intermediate values. After the loop, the necessary values are brought together in the RATE function, which returns the final answer. Does this seem like the way to go or am I totally missing an easier method? (Any help with that would be greatly appreciated because I have never made a UDF or used VBA before!)

Thank you very much!
Ryan


PS
I ultimately plan to use whatever solution in Access so that returns-by-date are stored are and returns-for-a-period are calculated in a query, but, for testing this, Excel is not so different (right?) so hope this is the best place to post this. (Any Access thoughts are welcome!)

 

[shades]

Howdy. There probably is enough differences in the functions for this kind of thing, that it might be better to look directly in Access, not Excel. But that is only opinion.
________
Honda civic (sixth generation)

 

[rbrady]

Is it possible to calculate the compound annual growth rate (CAGR) for a period with only the returns by percentage instead of the actual values?

For anyone who is interested, I am going to try to use dollar values/prices instead of percentage values. It seems that percentages are usually the reported in end-of-period reports, but running performance is given in a price based on a hypothetical value invested since the fund started; that is, historical prices are found as prices and not percentages: Example (I'm sure this is super basic for anyone with a financial background, but that's not me!) Also, I came to the realization that a percentage has to be calculated from values anyway, so why go to the effort of fabricating my own values when some are already available?

 

[shades]

Thanks for follow up. One caution - sometimes you will find a difference between how one program performs calculation and another program. Thus, you could find slight variations int hye calculated percent vs. what was supplied. Then you have to determine which is more accurate.
________
Honey oil

 

[rbrady]

Back to the original question (using percentages)

For various reasons, using percentage values instead of price values will be better for me, so I am switching back to my original post about a UDF. I have written a function, but it errors as it takes-in the first argument. This is my first time working with VBA—can someone please offer me some help?

Aside getting the function to work, how can I set/declare Option Explicit On and Option Strict On? I thought the lines just went at the top, but I couldn’t get them to work.

I have attached the workbook with and without the VBA code. (Is that the best way to share files with VBA in the forum?) Also, here is the code I have written right here:

Thank you!

'Option Explicit On
'Option Strict On

Function PIRR(Symbol As String, PeriodLength As Integer, PeriodEndDate As Date)

'==============================================================================
' Function: PIRR (Percentage-based internal rate of return)
' Purpose: To Calculate the internal rate of return (IRR)/compound annual
'          growth rate (CAGR) of a fund when intermediate returns are only
'          known aspercentages as opposed to values. This function creates and
'          calculates on the intermediate values necessary for compounding.
' Arguments: - Symbol: The stock ticker symbol of the fund
'            - PeriodLength: The length of the period over which the return
'              will be calculated given in years
'            - PeriodEndDate: The last day of the period for which the return
'              is begin calculated. This will be given as the last day of the
'              month.
' Example: PIRR(MSFT, 3, DATE(2007,12,31))
' Requirements: - A named range, "Dates"
'               - A named range, "Returns"
'               - A named range, "Symbols"
' Author: RLB
' Last modified: May 2008
'==============================================================================

    
    ' Declare variables
    Dim InitialValue As Double                                                 ' An arbitrary values that serves as a starting point for RATE
    Dim FutureValue As Double                                                  ' The fabricated future value for use with RATE
    Dim PeriodStartDate As Date
    Dim WorkingDate As Date                                                    ' In the loop, this represents the date currently calculated upon
    
    ' Assign values to variables
    InitialValue = 100                                                         ' An arbitrary value
    FutureValue = InitialValue
    PeriodStartDate = DateSerial((Year(PeriodEndDate) - PeriodLength), Month(PeriodEndDate), Day(PeriodEndDate))
    WorkingDate = PeriodStartDate
    
    '--------------------------------------------------------------------------
    ' Adjust the future value with the performance of past months for each
    ' month until the period is fulfilled
    '--------------------------------------------------------------------------
    Do While WorkingDate <= PeriodEndDate
        
        WorkingReturn = Application.WorksheetFunction.SumProduct(--(Symbols = Symbol), --(Dates = WorkingDate), Returns)  ' Look-up the correct return value based on the unique combination of symbol and date. The plurals, "Symbols", and, "Dates", refer to the named cell ranges where the respective values will be found.
        FutureValue = FutureValue * (1 + (WorkingReturn / 100))                ' Adjust the value by the return (given as a percentage (e.g., A value of 25 is given, which means, "twenty-five percent.")
        WorkingDate = Application.WorksheetFunction.EoMonth(WorkingDate, 1)    ' Advance by one month for the next loop iteration
        
    Loop
    
    ' The final calculation using the RATE function
    PIRR = Application.WorksheetFunction.Rate(PeriodLength, 0, (-1 * InitialValue), FutureValue)  ' The pv argument (InitalValue here) needs to be negative (I don't understand why)

End Function

 

[rbrady]

Revised

I found some problems, but the “Show calculation steps > Evaluate” underlines the whole function (with the arguments) now. Does anyone have any ideas, please? The problems I fixed were:

• Declared the variable WorkingReturn
• Declared variables for the named ranges
• Changed the EoMonth function to increase by three months (for a quarter) instead of one month

The revised code is below:

Option Explicit

'==============================================================================
' Function: PIRR (Percentage-based internal rate of return)
' Purpose: To Calculate the internal rate of return (IRR)/compound annual
'          growth rate (CAGR) of a fund when intermediate returns are only
'          known aspercentages as opposed to values. This function creates and
'          calculates on the intermediate values necessary for compounding.
' Arguments: - Symbol: The stock ticker symbol of the fund
'            - PeriodLength: The length of the period over which the return
'              will be calculated given in years
'            - PeriodEndDate: The last day of the period for which the return
'              is begin calculated. This will be given as the last day of the
'              month.
' Example: PIRR(MSFT, 3, DATE(2007,12,31))
' Requirements: - A named range, "Dates"
'               - A named range, "Returns"
'               - A named range, "Symbols"
' Author: RLB
' Last modified: May 2008
'==============================================================================


Function PIRR(Symbol As String, PeriodLength As Integer, PeriodEndDate As Date)
    
    ' Declare variables
    Dim PeriodStartDate As Date
    Dim WorkingDate As Date                                                    ' In the loop, this represents the date currently calculated upon
    
    Dim InitialValue As Double                                                 ' An arbitrary values that serves as a starting point for RATE
    Dim FutureValue As Double                                                  ' The fabricated future value for use with RATE
    Dim WorkingReturn As Double
    
    Dim Dates As Range
    Dim Returns As Range
    Dim Symbols As Range
        
    
    ' Assign values to variables
    PeriodStartDate = DateSerial((Year(PeriodEndDate) - PeriodLength), Month(PeriodEndDate), Day(PeriodEndDate))
    WorkingDate = PeriodStartDate
    
    InitialValue = 100                                                         ' An arbitrary value
    FutureValue = InitialValue
    
    Dates = Worksheets("Data").Range("Dates")
    Returns = Worksheets("Data").Range("Returns")
    Symbols = Worksheets("Data").Range("Symbols")
    
    
    
    '--------------------------------------------------------------------------
    ' Adjust the future value with the performance of past months for each
    ' month until the period is fulfilled
    '--------------------------------------------------------------------------
    Do While WorkingDate <= PeriodEndDate
        
        WorkingReturn = Application.WorksheetFunction.SumProduct(--(Symbols = Symbol), --(Dates = WorkingDate), Returns)  ' Look-up the correct return value based on the unique combination of symbol and date. The plurals, "Symbols", and, "Dates", refer to the named cell ranges where the respective values will be found.
        FutureValue = FutureValue * (1 + (WorkingReturn / 100))                ' Adjust the value by the return (given as a percentage (e.g., A value of 25 is given, which means, "twenty-five percent.")
        WorkingDate = Application.WorksheetFunction.EoMonth(WorkingDate, 3)    ' Advance by one quarter (three months) for the next loop iteration
        
    Loop
    
    ' The final calculation using the RATE function
    PIRR = Application.WorksheetFunction.Rate(PeriodLength, 0, (-1 * InitialValue), FutureValue)  ' The pv argument (InitalValue here) needs to be negative (I don't understand why)

End Function

 

[rbrady]

Working code

I have come to a version of the function that works correctly. My previous post had all sorts of basic problems, but the most significant problem was the use of SUMPRODUCT via VBA. Because SUMPRODUCT is used in a non-standard way (to look-up and return a value), Application.WorksheetFunction.SumProduct() does not work. Instead, the method, Application.Evaluate(), is used, which evaluates a string just like GUI Excel would through the formula bar. [Thanks to a thread elsewhere that had this solution.] Critiques are welcome:

Option Explicit

'==============================================================================
' Function: PIRR (Percentage-based internal rate of return)
' Purpose: To Calculate the internal rate of return (IRR)/compound annual
'          growth rate (CAGR) of a fund when intermediate returns are only
'          known aspercentages as opposed to values. This function creates and
'          calculates on the intermediate values necessary for compounding.
' Arguments: - Symbol: The stock ticker symbol of the fund. If this is not a
'              cell reference, it must be enclosed with quotation marks.
'            - PeriodLength: The length of the period over which the return
'              will be calculated given in years
'            - PeriodEndDate: The last day of the period for which the return
'              is begin calculated. This will be given as the last day of the
'              month.
' Example: PIRR("MSFT", 3, DATE(2007,12,31))
' Requirements: - A named range, "Dates," which contains quarterly month-end
'                 dates
'               - A named range, "Returns"
'               - A named range, "Symbols"
' Author: RLB
' Last modified: May 2008
'==============================================================================


Public Function PIRR(Symbol As String, PeriodLength As Integer, PeriodEndDate As Date) As Variant
    
    ' Check input
    If Application.Evaluate("SUMPRODUCT(--(Symbols=" & Chr(34) & Symbol & Chr(34) & "))") = 0 Then
        PIRR = "#SYMBOL NOT FOUND!"
        Exit Function
    End If
    If Application.Evaluate("SUMPRODUCT(--(Dates=" & CLng(PeriodEndDate) & "))") = 0 Then
        PIRR = "#END DATE NOT FOUND!"
        Exit Function
    End If
   
   
    ' Declare variables
    Dim FutureValue As Double                                                  ' The fabricated future value for use with RATE
    Dim InitialValue As Double                                                 ' An arbitrary values that serves as a starting point for RATE
    Dim PeriodStartDate As Date
    Dim WorkingDate As Date                                                    ' In the loop, this represents the date currently calculated upon
    Dim WorkingReturn As Double
    
    
    ' Assign values to variables
    PeriodStartDate = DateSerial((Year(PeriodEndDate) - PeriodLength), Month(PeriodEndDate), Day(PeriodEndDate))
    If Application.Evaluate("SUMPRODUCT(--(Dates=" & CLng(PeriodStartDate) & "))") = 0 Then
        PIRR = "#START DATE NOT FOUND!"
        Exit Function
    End If
    WorkingDate = Application.WorksheetFunction.EoMonth(PeriodStartDate, 3)
    InitialValue = 100                                                         ' An arbitrary value
    FutureValue = InitialValue
    
    
    '--------------------------------------------------------------------------
    ' Adjust the future value with the performance of past months for each
    ' month until the period is fulfilled
    '--------------------------------------------------------------------------
    Do While WorkingDate <= PeriodEndDate
        
        WorkingReturn = Application.Evaluate("SUMPRODUCT(--(Symbols=" & Chr(34) & Symbol & Chr(34) & "),--(Dates=" & CLng(WorkingDate) & "),Returns)")  ' Look-up the correct return value based on the unique combination of symbol and date ("=SUMPRODUCT(--(Symbols=Symbol),--(Dates=WorkingDate),Returns)")
        FutureValue = FutureValue * (1 + (WorkingReturn / 100))                ' Adjust the value by the return (given as a percentage (e.g., A value of 25 is given, which means, "twenty-five percent.")
        WorkingDate = Application.WorksheetFunction.EoMonth(WorkingDate, 3)    ' Advance by one quarter (three months) for the next loop iteration
        
    Loop
    
    
    ' The final calculation using the RATE function
    PIRR = Application.WorksheetFunction.Rate(PeriodLength, 0, (-1 * InitialValue), FutureValue)  ' The pv argument (InitalValue here) needs to be negative (I don't understand why)

End Function

 

[rbrady]

Here is an update for anyone who is interested. There are parts that are specific to the named ranges I’ve defined, but some of the more general logic is touched-up.

Option Explicit

'===============================================================================
' Function: PIRR (Percentage-based internal rate of return)
' Purpose: To Calculate the internal rate of return (IRR)/compound annual
'          growth rate (CAGR) of a fund or index when intermediate returns are
'          only known as percentages as opposed to values. This function creates
'          and calculates on the intermediate values necessary for compounding.
' Arguments: - FundOrIndex: Whether the calculation will be made on a fund or
'              an index.
'            - Id: The ID number of the fund or index. If this is not a cell
'              reference, it must be enclosed with quotation marks.
'            - PeriodLength: The length of the period over which the return
'              will be calculated given in months. Use -1 to calculate the annual
'              return since inception; use -2 to calculate year-to-date
'            - PeriodEndDate: The last day of the period for which the return
'              is begin calculated. This will be given as the last day of the
'              month.
' Generic form: PIRR(FundOrIndex, Id, PeriodLength, PeriodEndDate)
' Example:      PIRR(fund, 47, 36, DATE(2007,12,31))
' Requirements: - Named ranges, "FundReturnDate," and "IndexReturnDate," which
'                 contain monthly month-end dates
'               - Named ranges, "FundReturn," and "IndexReturn"
'               - Named ranges, "FundId," and "IndexId"
' Author: RLB
' Last modified: October 2008
'===============================================================================


Public Function PIRR(FundOrIndex As String, _
                     Id As String, _
                     PeriodLength As Double, _
                     PeriodEndDate As Date) _
                As Variant
    
            
            
    Dim FutureValue As Double                                                   ' The fabricated future value for use with RATE
    Dim IdRange As String
    Dim InceptionDate As Variant
    Dim InceptionDateLocation As String                                         ' The cell address where the inception date is located; This could be used for funds and indices, but it is only used for indices as it is slightly indirect
    Dim InceptionLength As Date
    Dim InitialValue As Double                                                  ' An arbitrary values that serves as a starting point for RATE
    Dim PeriodStartDate As Date
    Dim ReturnDateRange As String
    Dim ReturnRange As String
    Dim WorkingDate As Date                                                     ' In the loop, this represents the date currently calculated upon
    Dim WorkingReturn As Double
    
    
    
    ' Assign appropriate (fund or index) variables -----------------------------
    Select Case FundOrIndex
        Case "fund"
            If Application.Evaluate("SUMPRODUCT(--(FundId=" & Id & "))") = 0 Then
                PIRR = "#FUND ID NOT FOUND!"
                Exit Function
            Else
                IdRange = "FundId"
                ReturnDateRange = "FundReturnDate"
                ReturnRange = "FundReturn"
            End If
        Case "index"
            If Application.Evaluate("SUMPRODUCT(--(IndexId=" & Id & "))") = 0 Then
                PIRR = "#INDEX ID NOT FOUND!"
                Exit Function
            Else
                IdRange = "IndexId"
                ReturnDateRange = "IndexReturnDate"
                ReturnRange = "IndexReturn"
            End If
        Case Else
            PIRR = "#SELECT FUND OR INDEX!"
            Exit Function
    End Select
    
    
    
    ' Check period end date
    If Application.Evaluate("SUMPRODUCT(--(" & IdRange & "=" & Id & "),--(" & ReturnDateRange & "=" & CLng(PeriodEndDate) & "))") = 0 Then
        If PeriodLength = 3 Then
            PIRR = "–"                                                         ' The previous quarterly periods are the only periods with a end date that is different than the explicitly set end date. That is, data might not be available for new funds
            Exit Function
        Else
            PIRR = "#END DATE NOT FOUND!"
            Exit Function
        End If
    End If
    
    PeriodStartDate = Application.WorksheetFunction.EoMonth(PeriodEndDate, -PeriodLength)
    PeriodStartDate = DateSerial(Year(PeriodStartDate), Month(PeriodStartDate), Day(PeriodStartDate))
       
    
    ' Inception date
    If FundOrIndex = "fund" Then
        'InceptionDate = Application.Evaluate("SUMPRODUCT(--(MarketValueByFundId=" & Id & "),InceptionDate)")
        InceptionDate = Application.Evaluate("OFFSET(INDEX(MarketValueByFundId,1,1),MATCH(" & Id & ", MarketValueByFundId,0)-1,4)")
        If InceptionDate > PeriodStartDate Then
            'PIRR = "#NOT INVESTED LONG ENOUGH!"
            PIRR = "–"                                                         ' Not the error message but a place-holder so the formula can be left in the cell
            Exit Function
        End If
    End If
 
    

    Select Case PeriodLength
    
        ' Since inception ------------------------------------------------------
        Case -1                                                                 ' A value of -1 for PeriodLength is the flag to calculate it since inception
            If FundOrIndex = "index" Then
                InceptionDateLocation = Application.Caller.Address
                InceptionDate = Worksheets("Performance").Range(InceptionDateLocation).Offset(0, 1)  ' The inception date resides in the cell one column to the right of the annualized-since-inception return
            End If
            InceptionLength = (PeriodEndDate - InceptionDate) / 365.25          ' Return InceptionLength in years and also account for leap years
            If InceptionLength < 1 Then                                         ' If the fund has not been invested for a whole year, then pretend it has been a whole year
                InceptionLength = 1
            End If
            PeriodLength = InceptionLength * 12                                 ' PeriodLength, which will be used by RATE(), is now the length of the entire investment period and back in units of months
            PeriodStartDate = InceptionDate
        
        ' Year-to-date ---------------------------------------------------------
        Case -2
            PeriodStartDate = DateSerial((Year(PeriodEndDate) - 1), 13, 0)      ' That last day (Decemeber 31) of the previous year
            PeriodLength = 12                                                   ' This is fixed like a 1 yr (12 mo) period

        ' Regular periods ------------------------------------------------------
        
    End Select



    ' Check period start date                                                   ' This part is after the "Since inception" part in case the inception date is used as a start date
    PeriodStartDate = Application.WorksheetFunction.EoMonth(PeriodStartDate, 1) ' The first return value will be at the end of the first month--this might be the first date the fund has a value, especially for sector totals
    If Application.Evaluate("SUMPRODUCT(--(" & IdRange & "=" & Id & "),--(" & ReturnDateRange & "=" & CLng(PeriodStartDate) & "))") = 0 Then
        Select Case FundOrIndex
            Case "fund"
                PIRR = "#START DATE NOT FOUND!"
            Case "index"
                PIRR = "–"                                                      ' If the start date is not found, then the index has not been around that long and that period can be dismissed
        End Select
        Exit Function
    End If
    
    
    
    '---------------------------------------------------------------------------
    ' Calculate the future value with the performance of past months for each
    ' month until the period is fulfilled
    '---------------------------------------------------------------------------
   
    WorkingDate = PeriodStartDate
    InitialValue = 100                                                          ' An arbitrary value
    FutureValue = InitialValue
    
    Do While WorkingDate <= PeriodEndDate

        If Application.Evaluate("SUMPRODUCT(--(" & IdRange & "=" & Id & "),--(" & ReturnDateRange & "=" & CLng(WorkingDate) & "))") = 0 Then   ' Make sure there is a record for that date' This is done explicitly and separate from the return because a DNE in the main SUMPRODUCT() will just return a zero, which is a valid return value
            PIRR = "#NO DATA " & CStr(WorkingDate) & "!"
            Exit Function
        Else
            WorkingReturn = Application.Evaluate("SUMPRODUCT(--(" & IdRange & "=" & Id & "),--(" & ReturnDateRange & "=" & CLng(WorkingDate) & ")," & ReturnRange & ")")  ' Look-up the correct return value based on the unique combination of Id and date ("=SUMPRODUCT(--(Symbols=Id),--(Dates=WorkingDate),Returns)")
            FutureValue = FutureValue * (1 + (WorkingReturn / 100))             ' Adjust the value by the return (given as a percentage (e.g., A value of 25 is given, which means, "twenty-five percent.")
            WorkingDate = Application.WorksheetFunction.EoMonth(WorkingDate, 1) ' Advance by one month for the next loop iteration
        End If

    Loop
    
    
    
    ' The final calculation using the RATE function ----------------------------
    If PeriodLength < 12 Then
        PeriodLength = 12
    End If
    PIRR = Application.WorksheetFunction.Rate((PeriodLength / 12), 0, (-1 * InitialValue), FutureValue) * 100 ' The pv argument (InitalValue here) needs to be negative (I don't understand why)
    If PIRR > -0.0000000001 And PIRR < 0.0000000001 Then                        ' If the theoretical result of RATE() is 0, RATE() will still produce a non-zero result ~1E-14 or 1E-15; using the Round expression preserved the negative sign (which we don't want because we want a true zero)
        PIRR = 0
    End If



End Function