SIP rolling XIRR calculator

[GShan]

Hi Excel Experts,

I am looking to calculate SIP rolling XIRR. Tried searching online but couldn't find an exact solution. Attached is a sample data set. Columns A, B and C are the source data. Column D has been calculated only to show how the portfolio value is being derived using existing columns (A:C). It's an intermediate column. Now my requirement is to be able to calculate XIRR for different rolling period like 3 years, 5 years etc.

Please let me know if you have any query about the requirement.

 

[Monty]

Hellp Shan

We can alculate the rolling XIRR for different periods like 3 years, 5 years, etc., you can use Excel formulas combined with the XIRR function and array formulas. Here's a step-by-step guide on how to do it:

1. Prepare your data: Make sure your data is organized properly in columns A, B, and C, with dates in column A, investment amounts in column B, and portfolio values in column C.

2. Calculate XIRR for rolling periods:
- Let's say you want to calculate XIRR for a rolling 3-year period.
- In another column (let's say column E), starting from the third row (since you need at least 3 data points), enter the following formula:

=IF(ROW()-2<=3,"",XIRR(INDEX(C:C,ROW()-2):INDEX(C:C,ROW()),INDEX(A:A,ROW()-2):INDEX(A:A,ROW())))

 

[GShan]

Hellp Shan

We can alculate the rolling XIRR for different periods like 3 years, 5 years, etc., you can use Excel formulas combined with the XIRR function and array formulas. Here's a step-by-step guide on how to do it:

1. Prepare your data: Make sure your data is organized properly in columns A, B, and C, with dates in column A, investment amounts in column B, and portfolio values in column C.

2. Calculate XIRR for rolling periods:
- Let's say you want to calculate XIRR for a rolling 3-year period.
- In another column (let's say column E), starting from the third row (since you need at least 3 data points), enter the following formula:

=IF(ROW()-2<=3,"",XIRR(INDEX(C:C,ROW()-2):INDEX(C:C,ROW()),INDEX(A:A,ROW()-2):INDEX(A:A,ROW())))

Thanks for your reply. However, my requirement is little more complex than that. If you'll check the attachment, I don't have the portfolio values as source data. It needs to be calculated using the NAV (Net Asset Value) mentioned in column B. Also, I want to keep the start and end date dynamic so that I can get the rolling SIP XIRR for all the possible periods. Hence, portfolio value will also be dynamic. I hope I could explain the complexity.

 

[joeu2004]

Column D has been calculated only to show how the portfolio value is being derived using existing columns (A:C). It's an intermediate column

Keep column D as an essential part of the solution. I would label it "End Bal".

See the attached Excel file for details.

Enter the following formula into E3, and copy down the column:

=IFERROR(IF(ROWS($C$3:C3)<$F$2, "",
XIRR(IF(ROW(OFFSET(C3, -$F$2, 0, $F$2+1))=ROW(OFFSET(C3,-$F$2,0)),OFFSET(D3,-$F$2,0),
IF(ROW(OFFSET(C3, -$F$2, 0, $F$2+1))=ROW(C3),-D3,OFFSET(C3, -$F$2, 0, $F$2+1))),
OFFSET(A3, -$F$2, 0, $F$2+1))), "#GUESS")

The formula must array-entered (press ctrl+shift+Enter) in some versions of Excel.

There might be a better implementation using some new Excel 365 features. But I'm old-school.

Explanation:

1. IFERROR(..., "#GUESS") is needed because XIRR is notorious for failing to find an IRR and returning #NUM (or #DIV/0, or a bogus IRR that might appear to be 0.00%). In such cases, we must provide a guess in the 3rd parameter.

2. IF(ROWS($C$3:C3)<$F$2, "" avoids calculating the IRR before we have the required number of rows (months) specified in F2.

3. OFFSET(C3, -$F$2, 0, $F$2+1) and OFFSET(A3, -$F$2, 0, $F$2+1) select the rolling range of cash flows and dates, measuring back from the currect row by the number of rows (months) specified in F2.

4. However, for a proper IRR calculation, the first cash flow must be the ending balance on the first date. That is selected by the expression
IF(ROW(OFFSET(C3, -$F$2, 0, $F$2+1))=ROW(OFFSET(C3,-$F$2,0)),OFFSET(D3,-$F$2,0)

5. And the last cash flow must be the ending balance on the last date, signed as an outflow (negative). That is selected by the expression
IF(ROW(OFFSET(C3, -$F$2, 0, $F$2+1))=ROW(C3),-D3