I have a series of cash flows and investment NAVs at each cash flow date. Does anyone know an easy way to create a “rolling” IRR at each date using the XIRR function in Excel? For example: Date CF NAV “Rolling” IRR 1/1/11 -100 100 0% 1/31/11 0 102 27.2% 2/15/11 -100 205 48.5% 2/28/11 -100 304 22.5% I cannot figure out a way automate this function in Excel using XIRR. Thanks.
I dont think you worded your question well.
thanks, you’re a lot of help
AM is right… I sorta know what you’re asking, but you need to define the problem better. How are you defining “Rolling IRR?” Does the window stay a constant length throughout, or does it shrink by one each period? Are the cash flows in addition to capital growth in NAV changes or are they part of NAV changes? It’s hard to help until you define the problem better.
I have a dataset of portfolio transactions going back nearly 20 years with three columns: Column A = date Column B = cash flow (+/-) Column C = Portfolio NAV immediately following the cash flow, this would be the sum of the portfolio NAV immediately prior to the cash flow + the cash flow. Additionally this dataset contains the portfolio’s month-end NAV, in which case Column A = month-end date, Column B = 0, Column C = NAV The NAV (column C) includes all capital growth in between dates. For example, if the portfoio NAV was 100 on 1/31 and $10 was invested 2/1, the CF would be -10 and the NAV would be 110 (assuming no capital growth between 1/31 and 2/1) and would look like: A B C 1/31 0 100 02/11 -10 110 I would like to add a fourth column (D) to this dataset containing a calculation of the portfolio’s IRR since inception at each date in the spreadsheet (“rolling IRR”). The main reason I’m having trouble using the XIRR function is because I have a series of cash flows and dates (A & B), but I also need to include the portfolio NAV as the terminal value in the IRR calculation, but this data is located in a separate column © from the cash flow series. I hope this better explains the problem.
Can you post a workbook somewhere with a sample of your dataset and expected “rolling IRR” calcs? If so, I’ll take a look and try to help.
I believe this will do what you’re talking about. Contrib NAV 1/1/2011 -10 10 1/1/2012 0 15 6/30/2012 -1 17.5 1/1/2013 0 21.875 I just made up some data. I created another column (D) that was NAVt-NAVt-1 for t>1. And then a column (E) that was Contrib plus the new one. All you then need is XIRR((E$2:Et,A$2:At) for every t>1 in column F. If you want you can add one to F to reflect that you get your principal back.
It sounds like IRR is not the right indicator. You sound like you are trying to get a time weighted return. Are the Cash Flows in the same units as the NAV? If not, we need to know how many units of NAV you have at each time. So column A is date Column B is CF Column C is (Nav+CF) Now you have to figure out what you really want to try to measure: Time weighted return would imply this: Column D = (Column C) - CF = NAV before CF Column E = %Change in column D since the last entry. Column F = (1+(column E)) Column G = (Product of all cells in Column F up to this point in time) - 1 Column H = (Column G)^(1/(# of years since start)) True IRR since inception would best be done with : OK… I see your problem… you need an array of cells that has all the cashflows from 1 to (n-1) and has the NAV value in cell N. I’ve looked for a cell reference function that would allow you to concatenate a vector of cells from 1 to N-1 with an additional cell at the end, but can’t find it. You could try to write a VBA macro that does the following. It takes dates, an array of CFS and NAVS, and a guess like XIRR. Inside the macro it creates an array MYDATA equal to the array of CFS. Then it takes MYDATA(Length(NAVS)) = NAVS(Length(NAVS)). Then call XIRR with Dates, MYDATA, and the guess. Either that, or you have to find a function that allows you to concatenate row [B$2:B$(n-1)] and cell C$(n), where n is the number of periods to date. (Note that my n might be off by one, depending on whether you’ve made room for column headers and such).