Computing IRR & NPV with dynamic range

[dmw]

Hi,

I am trying to calculate the IRR and NPV in cells E3 and E4 respectively. I am having issues with calculating the IRR & NPV because the range is dynamic, meaning the row count changes for each data set I import.

Here's the IRR code I am working on. I thought it would be easy to do, turns out for me it's not. I am still learning and appreciate any help. I have attached a copy of what the spreadsheet looks like. Thank you!

VBAForum_IRR_NPV.xlsx

Sub XIRR()


' XIRR Macro
' Calculate Internal Rate of Return with dynamic Cells
Dim CF As Long
Dim DAY As Date
    CF = Range("B10", Range("B10").End(xlDown))
    DAY = Range("A10", Range("A10").End(xlDown))
    
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=XIRR(CF,DAY)"
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.00%"


End Sub

[Kenneth Hobs]

Welcome to the forum!

When debugging code and using formulas like that, I like to build the string for the formula and then check it against the manually build formula. MsgBox() and Debug.Print are tools that can help. The latter places the output into the Immediate window of the Visual Basic Editor (VBE).

Sub XIRR()
     ' XIRR Macro
   ' Calculate Internal Rate of Return with dynamic Cells
  Dim CF As Range, DAY As Range, s As String
  Set CF = Range("B10", Range("B10").End(xlDown))
  Set DAY = Range("A10", Range("A10").End(xlDown))
  
  s = "=XIRR(" & CF.Address & "," & DAY.Address & ")"
  MsgBox s
  Debug.Print s
   
   
  With Range("E3")
  ' =XIRR(B10:B36,A10:A36)
    .Formula = s
    .Style = "Percent"
    .NumberFormat = "0.00%"
  End With
End Sub

[dmw]

Thank you!

I ran the code and it works like a charm, just what I was looking for. Glad to see I was on the right path.