I'm trying to create an XIRR function that will calculate the return based on an unique investment names and corresponding cash flows between certain dates. For example, I want to calculate the IRR for Investment A between 01/16/2018 to 5/20/2018 with the following cash flows:
Investments Dates Amounts
A 01/15/18 ($55)
B 01/18/18 ($20)
B 01/19/18 $9
B 04/06/18 $6
A 04/08/18 $24
A 05/20/18 $40
B 05/21/18 $7
A 05/23/18 ($5)
The challenge I am having is skipping past the Investment B cash flows and only calculating Investment A. This is a simplified version of what I’m trying to do. The full version has about 50 investment names spread over about 1000 rows. Any suggestions would help. I’ve tried using fncs INDEX, MATCH, OFFSET, COUNTIF.
7 freelancer menawar dengan rata-rata $22 untuk pekerjaan ini
I am a professional Financial Analyst and Excel expert and can easily help you in creating that functions with different parameters and criteria. I will be happy to discuss in detail.
Being a n investment banker, making presentation and financial models are central to our professional life. Therefore, I am advance user of MS Excel and Ms Power point. For more details kindly contact me
This task can be accomplished in a single cell with an array formula. I have created a formula in your sample sheet that works (I verified the result manually)