 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008XIRR Problem
I am trying to use XIRR to solve for the rate for a known schedule of dates and value. That schedule is below. I have solved for the rate using IRR and also a Goal Seek method to confirm the IRR. I have a spreadsheet to show all this. The answer per the IRR and the Goal Seek is -0.009270531%. I also confirmed this on my HP-12C. How does XIRR need to be set up to handle this sitation? This worksheet will be updated monthly and I also need to calculate the ROR from inception, from start of year, for the month. Dates XIRR 12/31/2007 $61,445.04 1/7/2008 $4,062.21 2/6/2008 $4,062.02 3/6/2008 $3,921.48 3/31/2008 -$72,906.91 #NUM! >I am trying to use XIRR to solve for the rate for a known schedule of dates >and value. That schedule is below. I have solved for the rate using IRR and [quoted text clipped - 15 lines] >3/31/2008 -$72,906.91 >#NUM! I don't understand your results or what "situation" you are trying to handle. Also what "rate" are you trying to compute? Applying Excel's IRR function to your series of cash flows (not really appropriate since they are at irregular intervals), I get =IRR({61445.04;4062.21;4062.02;3921.48;-72906.91}) --> -0.21689688% very different from your reported result of -0.009270531% I have no idea what parameters you set for Goal Seek so I can't comment on that. XIRR gives a result of -3.37% which is the Annual rate, based on a 365 day year. --ron My IRR function is =IRR(R2C2:R94C2,-0.009). The Goal Seek proof is the next column running from Row 2 to Row 93 using the same cash flow and accumulated at the IRR rate. If it would be helpful, I could email the spreadsheet, > >I am trying to use XIRR to solve for the rate for a known schedule of dates > >and value. That schedule is below. I have solved for the rate using IRR and [quoted text clipped - 34 lines] > > --ron > The answer per the IRR and the Goal Seek is -0.009270531%. You cannot use IRR on the following example, since the periods are irregular. Well, what I mean is: GIGO. IRR will give you a result, but it assumes the cash flows are equally spaced. Yours are not. > How does XIRR need to be set up to handle this sitation? > [....] [quoted text clipped - 4 lines] > 3/6/2008 $3,921.48 > 3/31/2008 -$72,906.91 XIRR always provides an annualized rate of return. IRR always provides a "periodic" rate of return. (Recall: IRR assumes equally spaced periods.) Since your cash flows are __almost__ monthly, I would expect (1+IRR(...))^12 - 1 to be very close to the XIRR result. With your data, I compute the annualized IRR to be -0.025682814, and XIRR computes -0.033609948. They are equal when rounded to 2 decimal places. "Close enough for goverment work" ;-) ------ original posting ----- > I am trying to use XIRR to solve for the rate for a known schedule of dates > and value. That schedule is below. I have solved for the rate using IRR and [quoted text clipped - 15 lines] > 3/31/2008 -$72,906.91 > #NUM! PS.... I wrote:" > > Dates XIRR > > 12/31/2007 $61,445.04 [quoted text clipped - 6 lines] > XIRR computes -0.033609948. They are equal when rounded to 2 decimal > places. "Close enough for goverment work" ;-) For an even better comparison, change the first date to 12/7/2007 and the last date to 4/6/2008, so that the periods are now almost monthly. The annualized IRR is -0.02568, and XIRR returns -0.02583 -- almost the same, as expected. Note: There will always be at least a small difference between the annualized IRR and XIRR because XIRR the varying number of days between "monthly" dates (i.e. say day number each month). The cash flow is the actual history of this investment, When I set up the IRR function, I have every date, (4/28/2006 through 3/31/2008) amount invested, surrender value, and account value from start to finish in a column from start to end in from R2C1:R705, Any cells without values are set to 0 so the IRR will function properly with the calculation being done for the entire history of the investment i.e. inception to date. My proof also matches. My intent is to able to get the same answer using XIRR instead of using this brute force method. I'm certain the end result is valid. I'm trying to learn how to shape the XIRR request to match what I know is right. > PS.... > [quoted text clipped - 18 lines] > annualized IRR and XIRR because XIRR the varying number of days > between "monthly" dates (i.e. say day number each month). > The cash flow is the actual history of this investment, > When I set up the IRR function, I have every date, > (4/28/2006 through 3/31/2008) [...]. > Any cells without values are set to 0 so the If you are saying that you have an entry for every date (including Sat and Sun), then IRR will compute a daily rate of return. In that case, you would annualize it by the formula: (1 + IRR(range,guess))^365 - 1. You will probably need the "guess" parameter in this case, as I believe you are doing, based on one of your follow-up postings. If you do not include Sat and Sun, or if you do not otherwise have equal cash flows, the IRR result will be misleading. > amount invested, surrender value, and account value > from start to finish IRR and XIRR deal with cash flows, not level amounts. But it would be equally misleading to treat the difference in investment value from time to time as a cash flow. The cash flows are: amounts that you invest (inflow); interest earned or dividend payments (inflow if reinvested; outflow otherwise); and ending value. > My intent is to able to get the same answer using XIRR > instead of using this brute force method. As I explained previously, IRR computes a __periodic__ rate, assuming that each cash flow represents an equally-space period. To compare its result with XIRR, you must annualize the IRR result based on the size of the period. In general: daily IRR: =(1 + IRR(range,guess))^365 - 1 weekly IRR: =(1 + IRR(range,guess))^52 - 1 multiple months IRR: =(1 + IRR(range,guess))^(12/m) - 1 where "m" is the number of months per period. > I'm trying to learn how to shape the XIRR request > to match what I know is right. The "shape" of the XIRR model is the same as the IRR model, with the additional detail of specific dates. You use XIRR only if the cash flows are irregularly spaced (or you want to bother with annualizing the IRR result); otherwise, use IRR. If the XIRR result does not closely match the annualized IRR, the problem is likely in your model -- the values that you are using as "cash flows" -- which would adversely effect the IRR result as well as the XIRR result. Let me know if this is helpful. Otherwise, I will stop wasting my time beating a dead horse. ----- original posting ----- > The cash flow is the actual history of this investment, When I set up the IRR > function, I have every date, (4/28/2006 through 3/31/2008) amount invested, [quoted text clipped - 31 lines] > > annualized IRR and XIRR because XIRR the varying number of days > > between "monthly" dates (i.e. say day number each month). I appreciate you sticking with me. I'm looking at your reply now - 3:55 PM EDT. > > The cash flow is the actual history of this investment, > > When I set up the IRR function, I have every date, [quoted text clipped - 88 lines] > > > annualized IRR and XIRR because XIRR the varying number of days > > > between "monthly" dates (i.e. say day number each month). I finally got it to work. Thanks for your help. I tried various guesses to see the effect. It takes a few digits before you see a variance. XIRR Dates (61,445.04) 12/31/2007 (4,062.21) 1/7/2008 (4,062.02) 2/6/2008 (3,921.48) 3/6/2008 72,906.91 3/31/2008 ROR Guess -0.03365756 -0.09 -0.03365756 -0.10 -0.03365756 -0.11 -0.03365756 -0.12 -0.03365756 -0.13 -0.03365756 -0.14 -0.03365756 -0.15 -0.03365757 -0.16 -0.03365757 -0.17 -0.03365756 -0.18 -0.03365756 -0.19 -0.03365756 -0.20 > I appreciate you sticking with me. I'm looking at your reply now - 3:55 PM EDT. > [quoted text clipped - 90 lines] > > > > annualized IRR and XIRR because XIRR the varying number of days > > > > between "monthly" dates (i.e. say day number each month). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.