XIRR and Non Contiguous Values

[ichetan]

Hi:

This Thread answers my question http://www.mrexcel.com/forum/excel-questions/60920-xirr-non-contiguous-cash-flows.html But I am unable to Look at the Code http://www.mrexcel.com/forum/showthread.php?t=49781&highlight=arrayunion

Can somebody cut and paste it here ?

Thanks

 

[pgc01]

Hi
Welcome to the board

I guess you mean:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
     ' Code: Juan Pablo González
     ' Spec: Aladin Akyurek
     ' May 4, 2003
     ' Ref: [url=http://makeashorterlink.com/?P20022174]TinyURL.com - shorten that long URL into a tiny URL[/url]
     Dim TempUnion() As Variant
     Dim i As Long, Itm As Variant, Ctr As Long

     For i = LBound(Arg) To UBound(Arg)
         Arg(i) = Arg(i)
         If IsArray(Arg(i)) Then
             For Each Itm In Arg(i)
                 Ctr = Ctr + 1
                 ReDim Preserve TempUnion(1 To Ctr) As Variant
                 TempUnion(Ctr) = Itm
             Next Itm
         Else
             Ctr = Ctr + 1
             ReDim Preserve TempUnion(1 To Ctr) As Variant
             TempUnion(Ctr) = Arg(i)
         End If
     Next i
     ArrayUnion = TempUnion
 End Function

But if you had explained your problem, sometimes you can solve this type of problems without vba.

 

[ichetan]

I'm Looking forward to XIRR((F2:F121, G122), A2:A122) Note G122 Contains the Negative Value. I can't move F2:F121 to G2:G121. So how can I do this without Macro? Using Excel 2007.

 

[pgc01]

Hi

Try:

=XIRR(IF(ROW(2:122)<=121,F2:F121,G122), A2:A122)

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

I'm leaving now but I'll check this thread tomorrow.

 

[ichetan]

This works well for that given cell. However, when I DRAG to find the next XIRR the values are incorrect. I have uploaded the spreadsheet here http://www.exaleads.com/test.xls Can you please look ?

Thanks

 

[pgc01]

This works well for that given cell. However, when I DRAG to find the next XIRR the values are incorrect. I have uploaded the spreadsheet here http://www.exaleads.com/test.xls Can you please look ?

Thanks

No. I cannot get external files.

If you want to post a table with values usually works:

- in your personal settings, set the Editor option: Enhanced Interface - Full WYSIWYG Editing
- select a table in excel and paste it directly in the IE when you answer the post

Remark: it will be easier to read if before copying the table you add borders to it.

In this case a table with 10-15 lines should be enough. Post the input, the expected output, the logic and what you tried

Ex., copyind directly from excel to the IE:


[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]a[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]d[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]f[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[/TR]
</TBODY>[/TABLE]

 

[ichetan]

1. Data Set runs from 01-01-1997 till DEC-2012 with Fixed Monthly Inflow of $1000. For $1000, depending on the Value I allot some Units in the Fund every month
2. The Cumulative allotted units month over month are redeemed after a 10 year period, rolling on a month to month basis in the example below 1049489 is obtained by 7125.518 * 147.286
3. I am calculating XIRR for the same.
4. The negative value cannot be in the column of $1000s so I added a separate column which is where the problem comes.

Columns as Under

Date, Value of Unit, No of Units Allotted, Cumulative Total, Value(B*D), Monthly Inflow, Negative Value, XIRR
[TABLE="width: 601"]
<tbody>[TR]
[TD="align: right"]1-Sep-05[/TD]
[TD="align: right"]85.992[/TD]
[TD="align: right"]11.629[/TD]
[TD="align: right"]6996.604[/TD]
[TD="align: right"]601652[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3-Oct-05[/TD]
[TD="align: right"]94.324[/TD]
[TD="align: right"]10.602[/TD]
[TD="align: right"]7007.206[/TD]
[TD="align: right"]660947.7[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2-Nov-05[/TD]
[TD="align: right"]89.329[/TD]
[TD="align: right"]11.195[/TD]
[TD="align: right"]7018.401[/TD]
[TD="align: right"]626946.7[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Dec-05[/TD]
[TD="align: right"]100.945[/TD]
[TD="align: right"]9.906[/TD]
[TD="align: right"]7028.307[/TD]
[TD="align: right"]709472.5[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2-Jan-06[/TD]
[TD="align: right"]107.188[/TD]
[TD="align: right"]9.329[/TD]
[TD="align: right"]7037.636[/TD]
[TD="align: right"]754350.1[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Feb-06[/TD]
[TD="align: right"]112.483[/TD]
[TD="align: right"]8.89[/TD]
[TD="align: right"]7046.526[/TD]
[TD="align: right"]792614.4[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Mar-06[/TD]
[TD="align: right"]119.495[/TD]
[TD="align: right"]8.369[/TD]
[TD="align: right"]7054.895[/TD]
[TD="align: right"]843024.7[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3-Apr-06[/TD]
[TD="align: right"]130.819[/TD]
[TD="align: right"]7.644[/TD]
[TD="align: right"]7062.539[/TD]
[TD="align: right"]923914.3[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2-May-06[/TD]
[TD="align: right"]134.053[/TD]
[TD="align: right"]7.46[/TD]
[TD="align: right"]7069.999[/TD]
[TD="align: right"]947754.6[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Jun-06[/TD]
[TD="align: right"]112.327[/TD]
[TD="align: right"]8.903[/TD]
[TD="align: right"]7078.902[/TD]
[TD="align: right"]795151.8[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3-Jul-06[/TD]
[TD="align: right"]114.59[/TD]
[TD="align: right"]8.727[/TD]
[TD="align: right"]7087.629[/TD]
[TD="align: right"]812171.4[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Aug-06[/TD]
[TD="align: right"]115.648[/TD]
[TD="align: right"]8.647[/TD]
[TD="align: right"]7096.276[/TD]
[TD="align: right"]820670.1[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Sep-06[/TD]
[TD="align: right"]128.063[/TD]
[TD="align: right"]7.809[/TD]
[TD="align: right"]7104.085[/TD]
[TD="align: right"]909770.4[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3-Oct-06[/TD]
[TD="align: right"]132.634[/TD]
[TD="align: right"]7.54[/TD]
[TD="align: right"]7111.625[/TD]
[TD="align: right"]943243.3[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Nov-06[/TD]
[TD="align: right"]140.191[/TD]
[TD="align: right"]7.133[/TD]
[TD="align: right"]7118.758[/TD]
[TD="align: right"]997985.8[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Dec-06[/TD]
[TD="align: right"]147.937[/TD]
[TD="align: right"]6.76[/TD]
[TD="align: right"]7125.518[/TD]
[TD="align: right"]1054128[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2-Jan-07[/TD]
[TD="align: right"]147.286[/TD]
[TD="align: right"]6.79[/TD]
[TD="align: right"]7132.308[/TD]
[TD="align: right"]1050489[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-1049489[/TD]
[TD="align: right"]40.6776184[/TD]
[/TR]
[TR]
[TD="align: right"]1-Feb-07[/TD]
[TD="align: right"]152.415[/TD]
[TD="align: right"]6.561[/TD]
[TD="align: right"]7138.869[/TD]
[TD="align: right"]1088071[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-1087071[/TD]
[TD="align: right"]55.0440711[/TD]
[/TR]
[TR]
[TD="align: right"]1-Mar-07[/TD]
[TD="align: right"]143.676[/TD]
[TD="align: right"]6.96[/TD]
[TD="align: right"]7145.829[/TD]
[TD="align: right"]1026684[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-1025684[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2-Apr-07[/TD]
[TD="align: right"]136.747[/TD]
[TD="align: right"]7.313[/TD]
[TD="align: right"]7153.142[/TD]
[TD="align: right"]978170.7[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-977171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3-May-07[/TD]
[TD="align: right"]153.169[/TD]
[TD="align: right"]6.529[/TD]
[TD="align: right"]7159.671[/TD]
[TD="align: right"]1096640[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-1095640[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Jun-07[/TD]
[TD="align: right"]161.903[/TD]
[TD="align: right"]6.177[/TD]
[TD="align: right"]7165.848[/TD]
[TD="align: right"]1160172[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-1159172[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2-Jul-07[/TD]
[TD="align: right"]166.647[/TD]
[TD="align: right"]6.001[/TD]
[TD="align: right"]7171.849[/TD]
[TD="align: right"]1195167[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-1194167[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Good. So if I undderstand correctly you want to calculate the values 40.6776184 and 55.0440711? Can you post the range inputs for both cases in the XIRR() formula?

 

[ichetan]

I am only posting Relevant Columns Required for XIRR
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]1-Jan-97[/TD]
[TD="width: 64, align: right"]5.67[/TD]
[TD="width: 64, align: right"]176.367[/TD]
[TD="width: 64, align: right"]1000[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Feb-97[/TD]
[TD="align: right"]5.56[/TD]
[TD="align: right"]356.223[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Mar-97[/TD]
[TD="align: right"]6.09[/TD]
[TD="align: right"]520.427[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Apr-97[/TD]
[TD="align: right"]5.88[/TD]
[TD="align: right"]690.495[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-May-97[/TD]
[TD="align: right"]5.96[/TD]
[TD="align: right"]858.28[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Jun-97[/TD]
[TD="align: right"]5.96[/TD]
[TD="align: right"]1026.065[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jul-97[/TD]
[TD="align: right"]6.66[/TD]
[TD="align: right"]1176.215[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Aug-97[/TD]
[TD="align: right"]7.43[/TD]
[TD="align: right"]1310.805[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Sep-97[/TD]
[TD="align: right"]6.87[/TD]
[TD="align: right"]1456.365[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Oct-97[/TD]
[TD="align: right"]6.92[/TD]
[TD="align: right"]1600.874[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5-Nov-97[/TD]
[TD="align: right"]6.68[/TD]
[TD="align: right"]1750.575[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Dec-97[/TD]
[TD="align: right"]6.62[/TD]
[TD="align: right"]1901.632[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jan-98[/TD]
[TD="align: right"]6.79[/TD]
[TD="align: right"]2048.907[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Feb-98[/TD]
[TD="align: right"]6.44[/TD]
[TD="align: right"]2204.187[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Mar-98[/TD]
[TD="align: right"]7.02[/TD]
[TD="align: right"]2346.637[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Apr-98[/TD]
[TD="align: right"]7.65[/TD]
[TD="align: right"]2477.356[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4-May-98[/TD]
[TD="align: right"]8.67[/TD]
[TD="align: right"]2592.696[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jun-98[/TD]
[TD="align: right"]8.59[/TD]
[TD="align: right"]2709.11[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jul-98[/TD]
[TD="align: right"]7.18[/TD]
[TD="align: right"]2848.386[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Aug-98[/TD]
[TD="align: right"]7.4[/TD]
[TD="align: right"]2983.521[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Sep-98[/TD]
[TD="align: right"]7.46[/TD]
[TD="align: right"]3117.569[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5-Oct-98[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]3249.148[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Nov-98[/TD]
[TD="align: right"]7.95[/TD]
[TD="align: right"]3374.934[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Dec-98[/TD]
[TD="align: right"]8.25[/TD]
[TD="align: right"]3496.146[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jan-99[/TD]
[TD="align: right"]9.29[/TD]
[TD="align: right"]3603.789[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Feb-99[/TD]
[TD="align: right"]10.23[/TD]
[TD="align: right"]3701.541[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Mar-99[/TD]
[TD="align: right"]11.64[/TD]
[TD="align: right"]3787.452[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5-Apr-99[/TD]
[TD="align: right"]12.09[/TD]
[TD="align: right"]3870.165[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-May-99[/TD]
[TD="align: right"]11.14[/TD]
[TD="align: right"]3959.932[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jun-99[/TD]
[TD="align: right"]12.68[/TD]
[TD="align: right"]4038.796[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jul-99[/TD]
[TD="align: right"]13.09[/TD]
[TD="align: right"]4115.19[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Aug-99[/TD]
[TD="align: right"]15.3[/TD]
[TD="align: right"]4180.549[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Sep-99[/TD]
[TD="align: right"]17.36[/TD]
[TD="align: right"]4238.153[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4-Oct-99[/TD]
[TD="align: right"]18.43[/TD]
[TD="align: right"]4292.412[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Nov-99[/TD]
[TD="align: right"]18.11[/TD]
[TD="align: right"]4347.63[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Dec-99[/TD]
[TD="align: right"]21.03[/TD]
[TD="align: right"]4395.181[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Jan-00[/TD]
[TD="align: right"]24.91[/TD]
[TD="align: right"]4435.326[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Feb-00[/TD]
[TD="align: right"]25.57[/TD]
[TD="align: right"]4474.434[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Mar-00[/TD]
[TD="align: right"]27.74[/TD]
[TD="align: right"]4510.483[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Apr-00[/TD]
[TD="align: right"]24.13[/TD]
[TD="align: right"]4551.925[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-May-00[/TD]
[TD="align: right"]20.78[/TD]
[TD="align: right"]4600.048[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jun-00[/TD]
[TD="align: right"]19.97[/TD]
[TD="align: right"]4650.123[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Jul-00[/TD]
[TD="align: right"]22.39[/TD]
[TD="align: right"]4694.786[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Aug-00[/TD]
[TD="align: right"]18.69[/TD]
[TD="align: right"]4748.291[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4-Sep-00[/TD]
[TD="align: right"]21.27[/TD]
[TD="align: right"]4795.306[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Oct-00[/TD]
[TD="align: right"]18.96[/TD]
[TD="align: right"]4848.049[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Nov-00[/TD]
[TD="align: right"]18.57[/TD]
[TD="align: right"]4901.899[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Dec-00[/TD]
[TD="align: right"]18.93[/TD]
[TD="align: right"]4954.725[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jan-01[/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]5007.916[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Feb-01[/TD]
[TD="align: right"]19.67[/TD]
[TD="align: right"]5058.755[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Mar-01[/TD]
[TD="align: right"]19.66[/TD]
[TD="align: right"]5109.62[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Apr-01[/TD]
[TD="align: right"]16.16[/TD]
[TD="align: right"]5171.501[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-May-01[/TD]
[TD="align: right"]17.34[/TD]
[TD="align: right"]5229.171[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jun-01[/TD]
[TD="align: right"]17.89[/TD]
[TD="align: right"]5285.068[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Jul-01[/TD]
[TD="align: right"]16.99[/TD]
[TD="align: right"]5343.926[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Aug-01[/TD]
[TD="align: right"]16.14[/TD]
[TD="align: right"]5405.884[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Sep-01[/TD]
[TD="align: right"]15.95[/TD]
[TD="align: right"]5468.58[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Oct-01[/TD]
[TD="align: right"]14.69[/TD]
[TD="align: right"]5536.654[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Nov-01[/TD]
[TD="align: right"]16.73[/TD]
[TD="align: right"]5596.427[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Dec-01[/TD]
[TD="align: right"]18.17[/TD]
[TD="align: right"]5651.463[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jan-02[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]5706.408[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Feb-02[/TD]
[TD="align: right"]19.64[/TD]
[TD="align: right"]5757.324[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Mar-02[/TD]
[TD="align: right"]21.92[/TD]
[TD="align: right"]5802.944[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Apr-02[/TD]
[TD="align: right"]22.59[/TD]
[TD="align: right"]5847.211[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-May-02[/TD]
[TD="align: right"]22.94[/TD]
[TD="align: right"]5890.803[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Jun-02[/TD]
[TD="align: right"]21.53[/TD]
[TD="align: right"]5937.25[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jul-02[/TD]
[TD="align: right"]22.31[/TD]
[TD="align: right"]5982.073[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Aug-02[/TD]
[TD="align: right"]20.86[/TD]
[TD="align: right"]6030.012[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Sep-02[/TD]
[TD="align: right"]21.63[/TD]
[TD="align: right"]6076.244[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Oct-02[/TD]
[TD="align: right"]19.82[/TD]
[TD="align: right"]6126.698[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Nov-02[/TD]
[TD="align: right"]20.08[/TD]
[TD="align: right"]6176.499[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Dec-02[/TD]
[TD="align: right"]21.44[/TD]
[TD="align: right"]6223.141[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jan-03[/TD]
[TD="align: right"]22.9[/TD]
[TD="align: right"]6266.809[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Feb-03[/TD]
[TD="align: right"]22.87[/TD]
[TD="align: right"]6310.534[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Mar-03[/TD]
[TD="align: right"]23.37[/TD]
[TD="align: right"]6353.324[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Apr-03[/TD]
[TD="align: right"]22.39[/TD]
[TD="align: right"]6397.987[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-May-03[/TD]
[TD="align: right"]23.93[/TD]
[TD="align: right"]6439.776[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Jun-03[/TD]
[TD="align: right"]26.24[/TD]
[TD="align: right"]6477.886[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jul-03[/TD]
[TD="align: right"]29.795[/TD]
[TD="align: right"]6511.449[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Aug-03[/TD]
[TD="align: right"]32.041[/TD]
[TD="align: right"]6542.659[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Sep-03[/TD]
[TD="align: right"]37.539[/TD]
[TD="align: right"]6569.298[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Oct-03[/TD]
[TD="align: right"]38.983[/TD]
[TD="align: right"]6594.95[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Nov-03[/TD]
[TD="align: right"]44.741[/TD]
[TD="align: right"]6617.301[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Dec-03[/TD]
[TD="align: right"]46.774[/TD]
[TD="align: right"]6638.68[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jan-04[/TD]
[TD="align: right"]52.286[/TD]
[TD="align: right"]6657.806[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Feb-04[/TD]
[TD="align: right"]49.582[/TD]
[TD="align: right"]6677.975[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Mar-04[/TD]
[TD="align: right"]52.93[/TD]
[TD="align: right"]6696.868[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Apr-04[/TD]
[TD="align: right"]53.294[/TD]
[TD="align: right"]6715.632[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-May-04[/TD]
[TD="align: right"]53.258[/TD]
[TD="align: right"]6734.409[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jun-04[/TD]
[TD="align: right"]46.045[/TD]
[TD="align: right"]6756.127[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jul-04[/TD]
[TD="align: right"]46.318[/TD]
[TD="align: right"]6777.717[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Aug-04[/TD]
[TD="align: right"]49.396[/TD]
[TD="align: right"]6797.962[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Sep-04[/TD]
[TD="align: right"]51.902[/TD]
[TD="align: right"]6817.229[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Oct-04[/TD]
[TD="align: right"]55.598[/TD]
[TD="align: right"]6835.215[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Nov-04[/TD]
[TD="align: right"]55.338[/TD]
[TD="align: right"]6853.286[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Dec-04[/TD]
[TD="align: right"]60.356[/TD]
[TD="align: right"]6869.854[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Jan-05[/TD]
[TD="align: right"]66.387[/TD]
[TD="align: right"]6884.917[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Feb-05[/TD]
[TD="align: right"]63.951[/TD]
[TD="align: right"]6900.554[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Mar-05[/TD]
[TD="align: right"]67.761[/TD]
[TD="align: right"]6915.312[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Apr-05[/TD]
[TD="align: right"]68.24[/TD]
[TD="align: right"]6929.966[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-May-05[/TD]
[TD="align: right"]65.484[/TD]
[TD="align: right"]6945.237[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jun-05[/TD]
[TD="align: right"]71.78[/TD]
[TD="align: right"]6959.168[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jul-05[/TD]
[TD="align: right"]74.338[/TD]
[TD="align: right"]6972.62[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Aug-05[/TD]
[TD="align: right"]80.94[/TD]
[TD="align: right"]6984.975[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Sep-05[/TD]
[TD="align: right"]85.992[/TD]
[TD="align: right"]6996.604[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Oct-05[/TD]
[TD="align: right"]94.324[/TD]
[TD="align: right"]7007.206[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Nov-05[/TD]
[TD="align: right"]89.329[/TD]
[TD="align: right"]7018.401[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Dec-05[/TD]
[TD="align: right"]100.945[/TD]
[TD="align: right"]7028.307[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Jan-06[/TD]
[TD="align: right"]107.188[/TD]
[TD="align: right"]7037.636[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Feb-06[/TD]
[TD="align: right"]112.483[/TD]
[TD="align: right"]7046.526[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Mar-06[/TD]
[TD="align: right"]119.495[/TD]
[TD="align: right"]7054.895[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Apr-06[/TD]
[TD="align: right"]130.819[/TD]
[TD="align: right"]7062.539[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-May-06[/TD]
[TD="align: right"]134.053[/TD]
[TD="align: right"]7069.999[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Jun-06[/TD]
[TD="align: right"]112.327[/TD]
[TD="align: right"]7078.902[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Jul-06[/TD]
[TD="align: right"]114.59[/TD]
[TD="align: right"]7087.629[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Aug-06[/TD]
[TD="align: right"]115.648[/TD]
[TD="align: right"]7096.276[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Sep-06[/TD]
[TD="align: right"]128.063[/TD]
[TD="align: right"]7104.085[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3-Oct-06[/TD]
[TD="align: right"]132.634[/TD]
[TD="align: right"]7111.625[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Nov-06[/TD]
[TD="align: right"]140.191[/TD]
[TD="align: right"]7118.758[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Dec-06[/TD]
[TD="align: right"]147.937[/TD]
[TD="align: right"]7125.518[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2-Jan-07[/TD]
[TD="align: right"]147.286[/TD]
[TD="align: right"]7132.308[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-1049489[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1-Feb-07[/TD]
[TD="align: right"]152.415[/TD]
[TD="align: right"]7138.869[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]-1087071[/TD]
[/TR]
</tbody>[/TABLE]

On 2nd Jan XIRR(IF(ROW(1:121)<=121,D1:D120,E121), A1:A121)*100 so on...

 

[pgc01]

Try in F121:

=XIRR(IF(ROW($1:121)<ROW(),$D$1:D120,E121), $A$1:A121)*100

... confirmed with CSE

Copy down