Calculating xirr with vlookup

Hello Friends,

Please refer to the attached Excel Sheet, where I am trying to calculate XIRR, but ending up with error.

In the workbook working sheets are as follows:
1. Revised Direct
2. Revised Regular
3. XIRR_Data

I want to do the following
# Calculate XIRR in "Revised Direct" referring to "XIRR_Data" where Column A of XIRR_Data is Direct
# Calculate XIRR in "Revised Regular" referring to "XIRR_Data" where Column A of XIRR_Data is Regular.

For Calculating XIRR,
- Current Valuation are available in Revised Direct & Revised Regular Respectively.
- and Dates & Amount of periodic investment are available in XIRR_Data.

Please help me to solve this issue

Thanks & Regard
Vikash Dalmia

Do you have an idea of what the correct results should be? I am no expert on financial calculations, but I don't think this really works like this in Excel. I recommend that you first spend some time with the XIRR() function help file to understand how Excel is processing this function and the data. https://support.office.com/en-us/art...b-a303ad9adc9d

The help file for the XIRR() function claims that Excel will not calculate the XIRR() unless there is at least one positive and one negative value in the cash flows (which makes sense, the XIRR() is the interest rate that causes the NPV to be 0, and one cannot get a summation to come to 0 adding only positive or negative values). It appears to me that all of your cash flows in XIRR_Data are negative, which would make it difficult to calculate an XIRR().

The way you have the algorithm set up, it feeds several 0 cash flows at date 0 (jan 0 1900), into the XIRR() function. When I opened your spreadsheet in LibreOffice Calc, it returned values for these functions rather than errors. I am not sure if the values returned by LO calc (typically in the 30% region) are reasonable for the calculation or not. However, as I understand the way the XIRR() function works in spreadsheets, having a reference date of jan 0 1900 does not seem likely to generate an accurate result. Excel returns an error because 0 is not a positive number.

I don't have much experience with the XIRR() function, but I don't see anything in the help file that suggests that XIRR() handles non-numeric entries -- which suggests to me that the XIRR() function is not going to work well with this kind of array function structure. I also don't do advanced array functions, so my approach to this problem would likely involve:

1) Filters or similar to actually extract the necessary data from the database.
2) You will need some kind of "initial investment" or "final buyout" entry somewhere in order to calculate the XIRR()
3) Then apply the XIRR() function to the extracted data.

Others here may have ideas for building this kind of array function.

Hi MrShorty,

Thanks for attending my post,

Even i don't have much experience on the XIRR function but i have referred some articles with XIRR before and Get to Know that XIRR function works positive and negative value,

The negative value is already mentioned in XIRR_Data sheet and the positive value required for calculation are is the current valuation column of Direct and Regular Sheet with dates as 31/05/2019

Hopefully there can be some way to get the result i am looking for.

I outlined a 3 part overview in my post -- are you familiar with those three tasks in Excel:

1) Filter step to extract data from the XIRR_Data database. Since I know I want to completely extract the data from the database, I choose to use and advanced filter: https://www.contextures.com/xladvfilter01.html
1a) For the criteria range, I enter "fund name" in J1 of XIRR_Data. In J2, I enter the desired fund name ("ADITYA BIRLA SUN LIFE SMALL CAP FUND").
1b) For the output range, I only want the "date of investments" and "amt (rs.)" fields returned, so I enter "date of investments" in J6 and "amt (rs.)" in K6.
1c) Select a cell in the main table -> Data -> Advanced Filter -> Select the "copy to another location" button -> check that the list range is correct -> criteria range is J1:J2 -> copy to range is J6:K6 -> OK
1d) Make sure the filtered results look correct.
2) Copy the final value for this fund from the pivot table in Revised Direct (or from Data Sheet) and paste into K16 (or whatever row number is just below the final output row). Enter 31 may 2019 into J16
3) Enter the XIRR() function someplace convenient. I would put it above the output in row 5, so J5 =XIRR(K7:K16,J7:J16). I get a result of ~12%.

Repeat that process for the other funds. It will be a little tedious, but otherwise should be fairly straightforward. Will that work for you?

Since you have several pivot tables in your file, I assume you are familiar with pivot tables. here's how I did it using a pivot table instead of advanced filter:

1) I need the final valuation with date in the database in XIRR_Data. I add a row for each fund that shows the final valuation on 31 may 2019 to this table.
2) Select the expanded XIRR_Data table -> insert Pivot Table in a new sheet.
3a) In the Report Filter field, I put "fund name"
3b) In the row labels field, I put Date of Investments
3c) In the Values field, I put Sum of amt (rs.)
4) In a convenient cell outside of the pivot table, I enter the XIRR() function =XIRR(B5:B25,A5:A25) (or whatever the ranges happen to be).
5) To get the XIRR() for a specific fund, select the dropdown by the fundname and choose the desired fundname from the filter list.

Hello Sir,

I am so glad to see your interest in my project, and the solutions you provided, but my data will b very dynamic and inconstant and such manual working will take long time to work with.

Therefore , I tried something which i found one step nearer to the result, and wanted to share with you for the expertise you have. Please find enclosed the Revised Sheet.

I did one manual copy paste in the whole working i.e. I copy paste the Current valuation from both the Regular & Direct sheet to XIRR_Data Sheet at the end which are highlighted in Red Font.

And then I put a array formula in column G of XIRR_Data Sheet, where i found the XIRR Value absolutely correct for "L&T INDIA VALUE FUND" and used vlookup to get the XIRR Value in Both Direct & Regular sheet respectively. and that is what i wanted for my final result.

But now the problem is, apart "L&T INDIA VALUE FUND" none of the funds XIRR Value is showing correct, and the most surprising thing i observe is, the formula i used is considering only the value of B2 of Column B for calculating XIRR.

As you copy any other fund name to B2 you will notice the particular fund will show correct result.

I thought by sharing you these details i can get the missing link or any error in the formula i used and my project can workout.

Thanks & Regards

Vikash Dalmia

Hi,

Array formula** in G2:

=XIRR(INDEX(D:D,N(IF(1,MODE.MULT(IF(B$2:B$249=F2,{1,1}*ROW(B$2:B$249)))))),INDEX(C:C,N(IF(1,MODE.MULT(IF(B$2:B$249=F2,{1,1}*ROW(B$2:B$249)))))))*100

and copied down.

If you are not using an English-language version of Excel, the separator within the array constant {1,1} may require amending.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Hello my Savior (MrShorty),

It worked like charm,

Just few Validation needed with it.

For Example:

In the Sheet , "AXIS FOCUSED 25 FUND" is available in both Regular & Direct Sheet, and amount and date are different, therefore I want respective XIRR Calucation validating Coulmn A of XIRR_Data

and lastly, for Zero Value result is showing #NUM!, instead i want it to show 0.

Thanks a ton for saving my life

Vikash Dalmia

If I understand correctly then the following modifications of XOR LX's array entered formula may help:
1. For direct investment:

Formula:

Please Login or Register  to view this content.

2. For regular investment:

Formula:

Please Login or Register  to view this content.

Remember that the formulas need to be activated as XOR LX directed in post #7.
Let us know if you have any questions.