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
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