=INDEX($C$1:$K$1000,MATCH(

You can create a dynamic named range using the above formula, then use that in your XIRR formula.

Solved

Posted on 2007-08-01

I have a pivot table that produces cash flows for various categories of investments (with date in each column heading) and I want to produce an IRR of the "Grand Total" (i.e. bottom) row of the pivot table. How do I refer the XIRR formula to the bottom row of the pivot table, no matter how many rows the table has? My attempts so far all leave a reference to a defined row, which is useless when the number of rows changes.

Thanks

Thanks

6 Comments

=INDEX($C$1:$K$1000,MATCH(

You can create a dynamic named range using the above formula, then use that in your XIRR formula.

i.e., =GETPIVOTDATA("Pivot Column",$A$5) where Pivot Column is the grand total column you want to return and $A$5 is the pivot table reference

Brad answered your question. That said, I urge you to be very careful in using IRR. IRR

is, simply put, not reliable, and prioritizing investment decisions on an IRR basis can

lead to bad decision-making.

1) Depending on the nature of the cash flows, there can actually be more than one IRR

2) Some scenarios can have very high IRR but actually have negative NPV using any

rational discount rate.

Illustrating both... Consider the following cash flows:

Today, -1,600

1 year from now, 10,000

2 yrs from now, -10,000

This stream actually has two IRRs: 25%, and 400%, both of which sure look attractive. Yet,

any reasonable discount yields a negative NPV.

Bottom line: trust NPV, and eschew IRR.

Regards,

Patrick

Thank you for your comments. I fear that I over-simplified the problem, so here goes again.

The pivot table is produced from a data spreadsheet listing every cash flow item with date, amount, company to which it relates (there are many), sector and a couple of other cayegories that are irrelevant for this question.

The pivot table itself has one column for each date on which a cash flow occurred and a grand total column at the right-hand end and a row for each company with a grand total row at the bottom.

So to produce an IRR (I am fully aware of the technical limitations of this) using XIRR I need the following data :

(i) the row containing the total cashflows (i.e. the whole of the bottom row of the pivot table except the final column, which is the horizontal sum of these amounts. The number of columns changes depending on the pivot table selections.;

(ii) the dates corresponding to each cash flow, which are contained in the top row of the table (again excluding the far right column) (the row does not change but, as in (i) above, the number of columns depends on the pivot table selctions); and

(iii) a guestimate of the solution, which is simple to include in the XIRR formula.

So what I cannot work out how to do is how to refer the XIRR formula to :

(i) a range which is the bottom row of the pivot table (variable row number) through to the far right of the table except the absolute last item (a variable number of columns); and

(ii) a range which is the top row of the pivot table (row number fixed but variable number of columns) again through to the far right of the table except the absolute last item.

I tried recording a macro to automate the creation of named ranges and I can easily include the navigation commands but Visual Basic converts these into references to specific ranges, rather than running the macro anew and re=specifying the appropriate ranges each time.

Any tips more than gratefully received.

Dave

Dynamic named ranges can return variable numbers of columns as well. I omitted the starting point for the dynamic named range. I've included it in the formulas below.

Grand Total row data starting in column B, but excluding last column

=INDEX($B$1:$B$1000,MATCH(

Title row data, starting in column B, but excluding last column. Assumed to lie in row 4.

=$B$4:INDEX($B$4:$IV$4,1,C

Sample workbook showing dynamic named ranges in action with a PivotTable: https://filedb.experts-exc

Brad

Title | # Comments | Views | Activity |
---|---|---|---|

Lock cells across workbook after data entry | 10 | 50 | |

VBA Excel Gantt | 1 | 28 | |

Copy Value of cell in formula | 1 | 25 | |

MS Excel Cell questions | 5 | 40 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**21** Experts available now in Live!