IRR calculation with exact start and end date

[Nuz]

I have to figure out the IRR of the series of annual cash flows where the initial (first) value and the last value has to be discounted from their exact dates but the other values from the mid-year.
The data is given in three rows, like:
-100 0 0 0 0 (discount from exact dates)
10 10 10 10 10 (discount from middle year)
0 0 0 0 150 (discount from exact dates)

This is a 5-year series and standard IRR function will give 23,0% as IRR.
The values -100 and 150 should be discounted from exact dates and 10's from the middle of each year.

Is there a function to get the desired IRR?
XIRR-function doesn't work because the data is in three rows.
VBA is not preferred here because IRR should update immediately if any value is changed.

 

[Comfy]

Just to clarify would your output be:

Excel Workbook
	A	B	C
10	Year 0	01/06/10	-100.00
11	Year 1	01/06/11	10.00
12	Year 2	01/06/12	10.00
13	Year 3	01/06/13	10.00
14	Year 4	01/06/14	150.00
15
16			17.69%
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C16	=XIRR(C10:C14,B10:B14)

/Comfy

 

[Richard Schollar]

You'll need to amend the way your data is presented to give exact dates when each flow occurs. You can use approximate one for the mid year values (eg 30/6/20XX). Then you can use XIRR:

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/2010</td><td style="text-align: right;;">30/06/2010</td><td style="text-align: right;;">30/06/2011</td><td style="text-align: right;;">30/06/2012</td><td style="text-align: right;;">30/06/2013</td><td style="text-align: right;;">30/06/2014</td><td style="text-align: right;;">31/12/2014</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Initial Flow</td><td style="text-align: right;;">-100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">Yearly flows</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">Final Flow</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">Net Cash Flow @ Date</td><td style="text-align: right;;">-100</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">XIRR</td><td style="text-align: right;;">17.87%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B27</th><td style="text-align:left">=SUM(<font color="Blue">B24:B26</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B29</th><td style="text-align:left">=XIRR(<font color="Blue">B27:H27,B23:H23</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Nuz]

Richard, you have the data exactly correct. I could do this reorganiztion of data of course manually every time but in my model, the user can define the initial date and the final date and immediately, a new IRR should be shown. For example, if the user now decides to change the initial date from 01/01/2010 to 15/3/2012, how would that table be created automatically and IRR calculated?

 

[Richard Schollar]

When do you want the subsequent flows to occur? Would they b e 6 months after the initial flow date, or would they remain exactly mid year? It may well be possible to build up formulas to update the dates and flows automatically.

 

[Nuz]

The subsequent flows should occur in the middle of the year with the exception of the first and last one.
If the first period (year) is for example 01/01/2010 - 31/12/2010 as in your example, and the initial flow occurs 01/04/2010, then the first yearly flow should occur in the middle of 01/04/2010 and 31/12/2010, i.e. 16/08/2010.
Then, the next period is 01/01/2011 - 31/12/2011 and the second yearly flow should occur in the middle of that period, i.e. 02/07/2011 and so on.
For example, if the user defines:
Initial flow: 01/04/2010
Final flow: 31/10/2014
Then the discount points (dates) should be:
-100 01/04/2010
+10 16/08/2010
+10 02/07/2011
+10 02/07/2012
+10 02/07/2013
+10 01/06/2014 (midpoint of 01/01/2014 and 31/10/2014)
+150 31/10/2014

 

[Richard Schollar]

Apologies for delayed response. I think some VBA code would be best to do this - you'd have several input cells to hold things like Last Date and First Date, First Amount, Last Amount and Intermediate Amounts and then use code to generate the schedule (you could run the code from the click of a button).

I can do the code if you think it sounds reasonable.

 

[Nuz]

I have no problem with doing it with the code myself by creating a custom function for example but I basically am looking for a macro-free solution.