|
XIRR 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! |
XIRR Problem
On Mon, 19 May 2008 03:03:01 -0700, Dkline
wrote: 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 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 |
XIRR Problem
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, "Ron Rosenfeld" wrote: On Mon, 19 May 2008 03:03:01 -0700, Dkline wrote: 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 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 |
XIRR Problem
On May 19, 3:03 am, Dkline wrote:
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? [....] 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 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 ----- On May 19, 3:03*am, Dkline wrote: 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! * |
XIRR Problem
PS....
I wrote:" 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 [....] 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" ;-) 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). |
XIRR Problem
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. "joeu2004" wrote: PS.... I wrote:" 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 [....] 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" ;-) 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). |
XIRR Problem
On May 20, 2:24 am, Dkline wrote:
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 a 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 ----- On May 20, 2:24*am, Dkline wrote: 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. "joeu2004" wrote: PS.... I wrote:" 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 [....] 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" ;-) 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). |
XIRR Problem
I appreciate you sticking with me. I'm looking at your reply now - 3:55 PM EDT.
"joeu2004" wrote: On May 20, 2:24 am, Dkline wrote: 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 a 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 ----- On May 20, 2:24 am, Dkline wrote: 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. "joeu2004" wrote: PS.... I wrote:" 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 [....] 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" ;-) 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). |
XIRR Problem
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 "Dkline" wrote: I appreciate you sticking with me. I'm looking at your reply now - 3:55 PM EDT. "joeu2004" wrote: On May 20, 2:24 am, Dkline wrote: 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 a 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 ----- On May 20, 2:24 am, Dkline wrote: 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. "joeu2004" wrote: PS.... I wrote:" 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 [....] 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" ;-) 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). |
| All times are GMT +1. The time now is 02:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com