Help with annualized return macro

[Analyst]

Hello,

I am trying to create a macro which calculates the annualized return from a series of monthly returns. The macro should work as a function which asks the user to select the data to annualize and then outputs the return in the selected cell.

I believe I am close with the code below, however; I receive the error "unable to set the ForumulaArray Property of the range class" in the last step.

Sub Monthly_Annualized_Returns()

UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=64)
Dim x As Integer
x = UBound(UserRange)


Range("a1").Select
Selection.FormulaArray = _
"=(PRODUCT(R[-x]C:R[-1]C/100+1)^(12/COUNT(R[-x]C:R[-1]C))-1)*100"

End Sub

 

[NARAYANK991]

Hi ,

I think there is more than one error in the code ; however , what I don't understand is why you need to use the actual calculation of the Annualized Return when Excel has the XIRR function to do all of it for you.

Try the following code :

Sub Monthly_Annualized_Returns()
    Dim UserRange As Range
    Dim x As Long
    Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
    With UserRange
        x = .Rows.Count
        y = .Columns.Count
       
        If y <> 2 Then Exit Sub
       
        Range("a1").Formula = "=XIRR(" & .Resize(x, 1).Address & "," & .Offset(, 1).Resize(x, 1).Address & ",0.1)"
    End With
End Sub

Narayan

 

[Analyst]

Thank you for the suggestion.

The IRR function works with cash flows whereas I am working with a series of monthly returns. The returns will always be in one column and the macro output should always appear in the cell below the column.

If possible, I would like to pin point my errors so I may build macros for other functions I commonly use. I am very new to coding and appreciate the help!

 

[Analyst]

To provide further info; when the formula is typed into Excel, it is in the following form: PRODUCT("array"/100+1)^(12/COUNT("Array")-1)*100
this is entered with ctrl + shift + enter. Recording those steps gives me the following:

Range("H32").Select
    Selection.FormulaArray = _
        "=(PRODUCT(R[-17]C:R[-1]C/100+1)^(12/COUNT(R[-17]C:R[-1]C))-1)*100"

 

[NARAYANK991]

Hi ,

Try this :

Sub Monthly_Annualized_Returns()
    Dim UserRange As Range
    Dim x As Long
    On Error Resume Next
    Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
    On Error GoTo 0
    If UserRange Is Nothing Then Exit Sub
    With UserRange
         x = .Rows.Count
         y = .Columns.Count
    
         If y <> 1 Then Exit Sub
    
         .Cells(x, 1).Offset(1).FormulaArray = "=(PRODUCT(" & .Address & "/100+1)^(12/" & x & ")-1)*100"
    End With
End Sub

Narayan

 

[Analyst]

Thank you. This is a novice question: it seems to run without error but the output does not display in the selected cell. How would I go about making that happen?

 

[NARAYANK991]

Hi ,

I am somewhat confused ; the code is supposed to prompt the user for a range which has the data ; the code uses the formula you gave to calculate the annualized return for that data , and enters the result in the cell below the data range.

See this file.

Narayan

 

[Analyst]

Thank you so much. I must have done something wrong when I ran it Yesterday. I am traveling without a computer now but will study and use extensively when I return. thanks again