Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can XIRR Work on Dates That Move Backwards in Time?
I have used XIRR in the past with a row of dates that move from earliest to
oldest date as you move right to left in the spreadsheet. The corresponding data values are directly below the dates. Today I worked with a spreadsheet where the dates move opposite: Earliest date is on the far right, and more recent dates move toward the left. Is there any way to get XIRR to work with such an arrangement of dates? I only get errors. -- W |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can XIRR Work on Dates That Move Backwards in Time?
"W" wrote:
Today I worked with a spreadsheet where the dates move opposite: Earliest date is on the far right, and more recent dates move toward the left. Is there any way to get XIRR to work with such an arrangement of dates? I only get errors. It would be nice if XIRR sorted all of the dates and corresponding values. (I created a user-defined function to do just that.) But it does not. The first date/value in the range must be the earliest date. All others can be in any order. Suppose your dates are newest-to-oldest in B1:Z1, with corresponding values in B2:Z2. Create the following array-entered formulas (press ctrl+shift+Enter instead of just Enter): A1: =Z1 A2: =Z2 Then instead of XIRR(B1:Z1,B2:Z2), use XIRR(A1:Y1,A2:Y2). If you do not want hard-code the right-most cell references, there are ways to determine the right-most value. For example, INDEX(B1:Z1,1,MATCH(2,1/$B$1:$Z$1)). That must be in an array-entered formula (press ctrl+shift+Enter instead of just Enter). Change Z1 to the right-most cell that you expect to use. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can XIRR Work on Dates That Move Backwards in Time?
On Tuesday, January 8, 2013 4:07:19 AM UTC-5, W wrote:
I have used XIRR in the past with a row of dates that move from earliest to oldest date as you move right to left in the spreadsheet. The corresponding data values are directly below the dates. Today I worked with a spreadsheet where the dates move opposite: Earliest date is on the far right, and more recent dates move toward the left. Is there any way to get XIRR to work with such an arrangement of dates? I only get errors. -- W If each of the cash flows and their corresponding dates are contained in adjacent cells then order of the cash flows and dates do not matter when calculating internal rate of return for irregular cash flows using Excel XIRR function or TADXL tadXIRR function Here is the actual cash flows that move from left to right A1:E1 -10000 5000 4000 3000 1000 A2:E2 12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/31/2016 Now the same cash flows move from right to left A4:E4 1000 3000 4000 5000 -10000 A5:E5 12/31/2016 12/31/2015 12/31/2014 12/31/2013 12/31/2012 And now all the cash flows are mixed up A7:E7 4000 1000 -10000 5000 3000 A8:E8 12/31/2014 12/31/2016 12/31/2012 12/31/2013 12/31/2015 But since the date for each of the cash flows is in the cell right below it thus it does not matter at all whether the cash flows are in any given order So the following three calls to XIRR or tadXIRR would display the same IRR of 14.49% =XIRR(A1:E1, A2:E2) =tadXIRR(A1:E1, A2:E2) =XIRR(A4:E4, A5:E5) =tadXIRR(A4:E4, A5:E5) =XIRR(A7:E7, A8:E8) =tadXIRR(A7:E7, A8:E9) Excel IRR reports an internal rate of return when interest is compounded periodically whereas tadXIRR function found at http://tadxl.com can display IRR for both periodic and continuous compounding of interest. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can XIRR Work on Dates That Move Backwards in Time?
"Michael Marshall" wrote:
Here is the actual cash flows that move from left to right A1:E1 -10000 5000 4000 3000 1000 A2:E2 12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/31/2016 Now the same cash flows move from right to left A4:E4 1000 3000 4000 5000 -10000 A5:E5 12/31/2016 12/31/2015 12/31/2014 12/31/2013 12/31/2012 And now all the cash flows are mixed up [....] So the following three calls to XIRR [...] would display the same IRR of 14.49% Yes, in theory. But did you even bother to try them, especially the 2nd set (reverse order)? If it truly worked for you, what version of Excel are you using? I do not have access to Excel 2013 or any Mac Excel. But with Excel 2003, 2007 and 2010, XIRR for the 2nd data set returns about 2.980E-09, not about 14.49% that XIRR correctly returns for the 1st data set. At first, this did not surprise me given the formula for XIRR. As I noted, the annualized period for each cash flow is (d[j]-d[1])/365. Note the reference to d[1], the first date in the range. However, when I use Goal Seek to derive the internal rate of return in G4 that results in zero for the formula =SUMPRODUCT(A4:E4/(1+G4)^((A5:E5-A5)/365)), G4 is indeed about 14.49%. This is due to the symmetry of PV and FV calculations. That is, the SUMPRODUCT parameter becomes an array of appreciated values, not discounted values, to wit: =SUMPRODUCT({1000,3000,4000,5000,-10000}/(1+G4)^({0,-366,-731,-1096,-1461}/365) (In the 3rd case of mixed order, we are discounting and appreciating to some "central" point in time, namely the first date in the range.) Nevertheless, if Goal Seek can derive the internal rate of return for that summation, XIRR should be able to as well. Moreover, my own Newton-Raphson implementation of XIRR does work with the dates in reverse order, even when I do not sort the data first. So the Excel errors in some cases and the bogus result in other cases seems to be a defect in the implementation of XIRR. I can imagine that XIRR is fixed in some later revision of Excel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to work hours backwards | Excel Discussion (Misc queries) | |||
Subtracting Dates to get total time work time excluding weekends | Excel Discussion (Misc queries) | |||
Can you work this backwards | Excel Discussion (Misc queries) | |||
How do I calculate dates backwards from a project end date | Excel Worksheet Functions | |||
How can I get the XIRR funct to work for a positive first number? | Excel Worksheet Functions |