Re: XIRR versus IRR help!
- From: "JoeU2004" <joeu2004@xxxxxxxxxxx>
- Date: Fri, 4 Sep 2009 07:00:02 -0700
PS....
I wrote:
To convert the XIRR result to a periodic rate,
we need to know the frequency of the cash flows
per year. For example, if they are monthly, then:
=(1 + XIRR(...))^(1/12) - 1
Conversely, you can annualize the IRR or RATE result. Again, we need to know the frequency of the cash flows per year. The following assumes monthly.
To get an annualized rate comparable to XIRR, you could compound the monthly rate, thus:
=(1+IRR(...))^12 - 1
where "..." represents the arguments to the IRR() function.
However, it is not uncommon to simply multiply the monthly rate, thus:
=12*IRR(...)
In that case, you might see a significant difference with the XIRR result. And that difference is indeed due to differences in assumptions about the compounding frequency.
----- original message -----
"JoeU2004" <joeu2004@xxxxxxxxxxx> wrote in message news:eRo5e1OLKHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
"Seth M" <sethmberkowitz@xxxxxxxxx> wrote:Is my initial attempt using the XIRR function
incorrect because of the daily compounding?
No one can say for sure because you do not show how you used XIRR and IRR, and you do not tell us exactly what the results of XIRR and IRR were.
But note that Excel XIRR returns an annualized rate, whereas Excel IRR and RATE return the periodic rate for each cash flow, presumed to be equally spaced, as does the HP 12C IRR.
To convert the XIRR result to a periodic rate, we need to know the frequency of the cash flows per year. For example, if they are monthly, then:
=(1 + XIRR(...))^(1/12) - 1
where "..." are the arguments that you passed to XIRR.
Nonetheless, yes, the periodic rate derived from XIRR will probably be slightly different than the rate returned by IRR or RATE (or the HP 12C). As you say, this because XIRR presumes daily compounding based on the exact dates.
For example, if you have monthly cash flows on the first of Jan, Feb, Mar, Apr and May, IRR and RATE presume that the cash flows are equally spaced, whereas XIRR will treat them as 31, 28 or 29, 31 and 30 days apart.
But there are other usage errors that you might have made.
First, note that in Ron's use of RATE, the initial cash flow is negative and the subsequent cash flows are positive. The point is: net inflows and net outflows must have opposite signs.
Since the HP 12C has the same requirement, we might assume you used IRR, XIRR and RATE correctly in that respect.
Second, we do not really know that your cash flows are equally spaced. You did not say. Since the HP 12C IRR presumes equally-spaced cash flows, we might assume that they are. But that assumes that you know how to use IRR correctly in the first place.
You know what they say about "ass-u-me" ;-).
If you have any doubts about your use of these functions, I suggest that you post usage, especially how you used XIRR.
----- original message -----
"Seth M" <sethmberkowitz@xxxxxxxxx> wrote in message news:46f5364e-2867-4a5f-86c9-e9b3a339eed9@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sep 3, 3:54 pm, Ron Rosenfeld <ronrosenf...@xxxxxxxxxx> wrote:On Thu, 3 Sep 2009 12:26:43 -0700 (PDT), Seth M <sethmberkow...@xxxxxxxxx>
wrote:
>Hi:
>I am trying to figure out how to calculate the IRR of a series of
>cashflows. There are 177 cashflows of $41,422, and the up-front cost
>is $3,330,786. I tried using an IRR, but that just gives me an error I
>think because there are too many cashflows. I used an XIRR, but this
>compounds daily, and thus gives me a different result than an HP-12C.
>Please advise!
When you get the #NUM error, it usually means you need to use a guess that is
closer to your expected return than the default of 10%. (See HELP for the IRR
worksheet function).
Using a guess of 1%, I get a result of 1.0467%
However, since your cashflows are all the same, you could also use the RATE
function:
=RATE(177,41422,-330786,0)
--ron
Thanks, that helps. Is my initial attempt using the XIRR function
incorrect because of the daily compounding?
.
- References:
- XIRR versus IRR help!
- From: Seth M
- Re: XIRR versus IRR help!
- From: Ron Rosenfeld
- Re: XIRR versus IRR help!
- From: Seth M
- Re: XIRR versus IRR help!
- From: JoeU2004
- XIRR versus IRR help!
- Prev by Date: Re: Macro help: moving specific cells based upon content
- Next by Date: RE: Search for number after specific text in string
- Previous by thread: Re: XIRR versus IRR help!
- Next by thread: Re: XIRR versus IRR help!
- Index(es):
Relevant Pages
|
