PDA

View Full Version : [SOLVED] Computing IRR & NPV with dynamic range



dmw
06-09-2015, 10:23 AM
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!

13642


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
06-09-2015, 11:27 AM
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
06-09-2015, 11:49 AM
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.