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:

a	2
d	3
f	5

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

 

[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

1-Sep-05	85.992	11.629	6996.604	601652	1000
3-Oct-05	94.324	10.602	7007.206	660947.7	1000
2-Nov-05	89.329	11.195	7018.401	626946.7	1000
1-Dec-05	100.945	9.906	7028.307	709472.5	1000
2-Jan-06	107.188	9.329	7037.636	754350.1	1000
1-Feb-06	112.483	8.89	7046.526	792614.4	1000
1-Mar-06	119.495	8.369	7054.895	843024.7	1000
3-Apr-06	130.819	7.644	7062.539	923914.3	1000
2-May-06	134.053	7.46	7069.999	947754.6	1000
1-Jun-06	112.327	8.903	7078.902	795151.8	1000
3-Jul-06	114.59	8.727	7087.629	812171.4	1000
1-Aug-06	115.648	8.647	7096.276	820670.1	1000
1-Sep-06	128.063	7.809	7104.085	909770.4	1000
3-Oct-06	132.634	7.54	7111.625	943243.3	1000
1-Nov-06	140.191	7.133	7118.758	997985.8	1000
1-Dec-06	147.937	6.76	7125.518	1054128	1000
2-Jan-07	147.286	6.79	7132.308	1050489	1000	-1049489	40.6776184
1-Feb-07	152.415	6.561	7138.869	1088071	1000	-1087071	55.0440711
1-Mar-07	143.676	6.96	7145.829	1026684	1000	-1025684
2-Apr-07	136.747	7.313	7153.142	978170.7	1000	-977171
3-May-07	153.169	6.529	7159.671	1096640	1000	-1095640
1-Jun-07	161.903	6.177	7165.848	1160172	1000	-1159172
2-Jul-07	166.647	6.001	7171.849	1195167	1000	-1194167

<tbody>
</tbody>

 

[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

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

<tbody>
</tbody>

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