• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VBA to Filter data set and calculate individual investment xirr

Dcross

New Member
Hello,

I have come across a formula that will filter a dataset to find individual cash flows and associated dates to calculate XIRR for an individual Investment. Here is the formula

“Filterxirr(Investment = A8)*(CashFlow),(Investment = A8)*(Date),Guess )

Does anyone know how to create a custom function in VBA that will filter a dataset for specific investments and associated dates and values to calculate XIRR?

Please let me know. It would be a great help.
 
@vletm here is a sample worksheet. I am trying to make a VBA function to filter the data set and come up with individual xirr for each line item. thanks.
 

Attachments

  • VBA Test worksheet.xlsm
    28.7 KB · Views: 3
Dcross
Are You sure or have You tested that Filtering data effects to XIRR?
... XIRR could use 'filtered' data from other range
Something like - now, Your data is in A... G-columns >> Your 'XIRR' data could be eg AA...AG-columns
 
@vletm I am trying to use the existing data set to filter the dates and cash flows and then calculate IRR with the filtered ranges. Please refer to the first formula that i am trying to replicate.
 
Dcross
Did You missed my question?
... with this You could do XIRR with individual investment
Note1: Data should be SORTED with investment!
Note2: Only investment -variable should change.
Note3: Data should have same layout ( eg start from row 8 )
Note4: Not tested with all [im]possible variations!
 

Attachments

  • VBA Test worksheet.xlsm
    30.9 KB · Views: 5
@vletm Is there a way to adjust the code to allow multiple criteria to be filtered then calculate xirr? Additionally, instead of filtering certain columns, can it filter the named ranges that are in the data set? Please see attached.
 

Attachments

  • VBA Test worksheet (3).xlsm
    30.4 KB · Views: 15
Dcross
Yes ... if You can solve each Your named ranges the 1st and the last row someway based individual investment.
My solution do not use 'filtering'.
 
Back
Top