IRR Function

[StompS]

If I am looking for the APY of an investment and the cash outflows are
bimonthly for 6 months, how do I tell Excel that I am doing it bimonthly?
Would that be "^24"? Thank you!

Steve

 

[joeu2004]

If I am looking for the APY of an investment and the cash outflows are
bimonthly for 6 months, how do I tell Excel that I am doing it bimonthly?
Would that be "^24"? Thank you!

I guess I am not doing a good job of explaining, so perhaps I shouldn't
respond. But....

First, do you mean "semimonthly" (twice a month) or "bimonthly" (every
2 months)? Your choice of "^24" would suggest that you mean
semimonthly.

Assuming that you have (12) semimonthly cash flows (some may be zero),
you would annualize the IRR() result (a semimonthly rate of return) by
computing:

(1 + IRR(...))^24 - 1

because there are 24 semimonthly periods in a year.

In a previous posting, I tried to explain why you must always do "1+"
and "-1" as above. Let me if that needs a better explanation.

PS: If you truly mean bimonthly -- that is, you have 3 cash flows --
substitute "^6" for "^24" above.

By the way, whether you have semimonthly or bimonthly cash flows, it
does not matter how many periods you have ("for 6 months" in your
posting). The only thing that matters is the length of each period.
If the period is weekly, you annualize using "^52". If the period is
semimonthly, "^24". If the period is monthly, "^12". If the period is
bimonthly, "^6". If the period is semiannually, "^2".

 

[StompS]

Yes, I meant semimonthly, and you have been doing a great job of
explaining...I just can't seem to figure out why I am getting a formula
error every time I try to do it. I have gone over and over my syntax and it
appears to be correct. I even copied and pasted "values only" so as not to
have any formula go wrong. I'm not sure what I'm missing...

Here is my formula that I have thus far (my location on the sheet has
changed)
=(1+IRR(L11:L25, (sumif(L11:L25,">0")/(-sumif(L11:L25,"<0")))^(1/15)-1)^24-1
Where L11:L25 are my 15 semimonthly cash investments and/or payments and L11
through L25 are values only (no formulas)
After telling me there is an error it highlights "L25," on my formula line.
I think I'm in over my head : (

 

[joeu2004]

Here is my formula that I have thus far (my location on the sheet has changed)
=(1+IRR(L11:L25, (sumif(L11:L25,">0")/(-sumif(L11:L25,"<0")))^(1/15)-1)^24-1
Where L11:L25 are my 15 semimonthly cash investments and/or payments and L11
through L25 are values only (no formulas)
After telling me there is an error it highlights "L25," on my formula line.

When I cut-and-paste what you wrote above, I get an error because of a
missing ")". But that does not result in highlighting any of the "L25"
in the formula (you do not say which one). So I am not sure I am
reproducing your mistake.

(Actually, Excel highlights each reference to the range L11:L25, but
that is just par for the course -- a debugging aid.)

In any case, the missing ")" is before "^24". That is, it should be
"-1))^24". The second ")" balances the left-most parenthesis around
"(1+IRR(...))".

By the way, you neglected to post the additional cashflows in your new
example. So I cannot say with impunity that the sumif/sumif estimate
will work in your case. I took your previous example of 8 cash flows
and inserted 7 zero cash flows in the middle. Surprisingly, the
sumif/sumif estimate worked even in that case.

My point is: you might still get either a #NUM or #DIV/0 error, which
indicates that
the "guess" is not good enough. Post back if you need help with that
-- but be sure to post the values in L11:L25.

I think I'm in over my head : (

I suggest that you break out component parts of a large formula so that
there is less complexity. For example:

A1: sumif(L11:L25,">0") / (-sumif(L11:L25,"<0"))
A2: A1^(1/15) - 1
A3: (1+ IRR(L11:L25, A2))^24 - 1

After everything works, you can always cut-and-paste the sub-formulas
into one complex formula, if you prefer.

PS: I prefer 1/count(L11:L25) instead of 1/15. First, to avoid human
error in counting. Second, if you insert into the range, all of the
formula(s) adjusts automagically.

 

[StompS]

OK,

Something funny is going on here because I know I'm not this stupid.

Here is what I have in cells A1 A2 A3 respectively

sumif(L11:L25,">0")/(-sumif(L11:L25,"<0))
A1^(1/count(L11:L25))-1
=(1+IRR(L11:L25,A2))^24-1 (I added the "=" in front so it would
calculate)

I am still getting "the formula you typed is getting an error"

The values for L11:L25 are as follows (remember they are "values" so they is
not any calculation going on inside those cells):

-600000
-150000
0
0
-5000
-150000
-200000
-400000
-150000
-310000
-150000
-350000
-300000
-415000
3288288


I can not figure out what I am doing wrong. I do appreciate the amount of
time you have invested in helping me with this problem....thank you again.

 

[joeu2004]

Here is what I have in cells A1 A2 A3 respectively
sumif(L11:L25,">0")/(-sumif(L11:L25,"<0))
A1^(1/count(L11:L25))-1
=(1+IRR(L11:L25,A2))^24-1
(I added the "=" in front so it would calculate)
I am still getting "the formula you typed is getting an error"

Well, of course the "=" should prefix each formula. Sorry, I should
have included the "=" myself.

Note: There is also a missing double-quote above: "<0". I don't know
if that is just a posting typo or a typo in your spreadsheet.

The values for L11:L25 are as follows

With those values and the corrections above, here are my results (cells
formatted as Percentage with 4 decimal places; highlight A1:A3 and
click on Format > Cells > Number):

A1: 103.4053%
A2: 0.2235%
A3: 12.6658%

See if you can duplicate these results.

 

[StompS]

I added the double quote in cell A1. As soon as I put the "=" sign in cell
A1 I get the formula error for that cell and it highlights the first ' L25,"
'. Could my Excel settings somehow be keeping me from doing this? L11:L25
cells are formatted as numbers.

 

[joeu2004]

I added the double quote in cell A1. As soon as I put the "=" sign in cell
A1 I get the formula error for that cell and it highlights the first ' L25,"
'. Could my Excel settings somehow be keeping me from doing this?

Probably not. I suspect your are getting mired down in compound typos.
I suggest that you clear A1, A2 and A3, and rewrite them by
copy-and-pasting from below.

A1:
=sumif(L11:L25,">0")/(-sumif(L11:L25,"<0"))

A2:
=A1^(1/count(L11:L25))-1

A3:
=(1+IRR(L11:L25,A2))^24-1

I just tried it, and it works just fine.

 

[Ron Rosenfeld]

If I am looking for the APY of an investment and the cash outflows are
bimonthly for 6 months, how do I tell Excel that I am doing it bimonthly?
Would that be "^24"? Thank you!

Steve

Assuming cash outflows twice a month, you would multiply the result of the IRR
function by 24 to get the annual percentage rate.

Using your values in another message, I get an IRR of 0.49814% per period or
11.96% per year.
--ron

 

[joeu2004]

Assuming cash outflows twice a month, you would multiply the result of the IRR
function by 24 to get the annual percentage rate.

I am glad you pointed this out. It is debatable whether to simply
multiply or exponentiate. Academicians do it both ways. I argue that
exponentiation is "right" because the periodic IRR (less than a year)
would be compounded in practical usage, just as the annual IRR is
compounded in PV and FV computations. Arguably, at issue is the
difference between "APY" (effective rate) and nominal rate. I hasten
to note that in some contexts, US federal regulations compound shorter
periodic rates to compute the APY. Multiplying determines the annual
nominal rate, not the APY. Be that as it may, as I said: academicians
do it both ways. So, to each his own.

Historical note: I used to explain both ways in my responses. But I
decided it complicated matters. So I flipped a coin and picked just
one ;-).

 

[Ron Rosenfeld]

I am glad you pointed this out. It is debatable whether to simply
multiply or exponentiate. Academicians do it both ways. I argue that
exponentiation is "right" because the periodic IRR (less than a year)
would be compounded in practical usage, just as the annual IRR is
compounded in PV and FV computations. Arguably, at issue is the
difference between "APY" (effective rate) and nominal rate. I hasten
to note that in some contexts, US federal regulations compound shorter
periodic rates to compute the APY. Multiplying determines the annual
nominal rate, not the APY. Be that as it may, as I said: academicians
do it both ways. So, to each his own.

Historical note: I used to explain both ways in my responses. But I
decided it complicated matters. So I flipped a coin and picked just
one ;-).

Perhaps. I'm no academician.

However, simple mathematics shows that if you take the 11.96% (actually
11.9552927760608%), divide it by 24 to get 0.498137199002532% and apply that
interest rate sequentially to the cash flows posted by the OP, you obtain the
final result that he posted. To me that means I am earning an effective rate
of 11.96% annually, or .498% per period.
--ron

 

[joeu2004]

However, simple mathematics shows that if you take the 11.96% (actually
11.9552927760608%), divide it by 24 to get 0.498137199002532% and apply that
interest rate sequentially to the cash flows posted by the OP, you obtain the
final result that he posted. To me that means I am earning an effective rate
of 11.96% annually, or .498% per period.

And I could say that if I take my 12.6658%, take the 24th root [1] to
get 0.4981%, and apply that interest rate "sequentially" to the cash
flows posted by the OP, I obtain the "final" result that he posted [2].
To me that means we are earning an effective rate of 12.6658%
annually, or 0.4981% per period.

The point is: we both compute the same periodic rate (0.4981%) first,
then apply some transformation (multiply or compound by a factor 24) to
get our respective annualized rates. So of course, when we each apply
an appropriate inverse transformation (divide by 24 or take the 24th
root), we get back to the same periodic rate that we both started with.
And of course that periodic rate works, since we both computed it
using essentially the same IRR() computation [3]. It proves nothing
for either of us.

I believe the real proof is as follows ....

We can compute the PV of each cash flow using PV(0.4981%,n,0,CFn),
where "n" is the period number and "CFn" is the cash flow for the
period [4]. The sum of the PVs is zero, which is the definition of the
IRR.

Conversely, we can compute the FV of the PV of each cash flow using
FV(0.4981%,n,0,PVn), where "n" is the period number and "PVn" is the PV
of the cash flow [4]. Of course, each FV equals the corresponding
periodic cash flow. That is not surprising: again, I have simply
applied inverse transformations.

But the point is: as we know, FV() assumes that "interest" compounds
periodically. So by the same token, we can take a PV of -$1 and
compute its FV a year later over 24 semimonthly periods using
FV(0.4981%,24,0,-1). The result is $1.126658. Thus, we earn $0.126658
in a year for each $1, which is an annual rate of 12.6658%.

To me, that means that 12.6658% is indeed the annualized IRR of the
semimonthly IRR.

Be that as it may, the fact remains that many people do indeed multiply
or divide to convert between annual and lesser periodic IRRs. Of
course, that is a simpler computation. The dollar error is typically
relatively small -- 5.61% in this case.

HTH.


Footnotes
--------------

[1] I really mean: (1+12.6658%)^(1/24) -1 .

[2] The correct procedure is to use the periodic rate to compute the PV
of the cash flows and demonstrate that the sum of the PVs is zero,
which is the definition of the IRR.

[3] IRR(L11:L25, 0.2235%), although you might have used a different
"guess".

[4] Table of PV and FV of the cash flows. Forgive me if the columns do
not line up. Some newsreaders do strange things, notably Google Groups
:-(, and different font spacings do not help. Hopefully, the only
confusing line might be the last one: the zero is the sum of the PVn
column.

n CFn PVn FVn
0 -600000 600000 -600000
1 -150000 149256 -150000
2 0 0 0
3 0 0 0
4 -5000 4902 -5000
5 -150000 146319 -150000
6 -200000 194125 -200000
7 -400000 386326 -400000
8 -150000 144154 -150000
9 -310000 296442 -310000
10 -150000 142729 -150000
11 -350000 331383 -350000
12 -300000 282634 -300000
13 -415000 389040 -415000
14 3288288 -3067310 3288288
SUM(PV0V14) 0

 

[StompS]

Well,

I am at a loss....I cut and pasted into my chart and the formulas did not
calculate anything. It almost appeared that there was no "=" to show it was
a function (but they were there). Then after a minute of screwing around and
checking again I got the formula error. I have taken up way too much of your
time, I am going to drop this and try something different. Thanks again for
a valiant effort trying to help me understand it!

Steve

 

[joeu2004]

PS....

I believe the real proof is as follows ....

Just in case my previous explanation (see below) overwhelmed everyone
-- quite understandably -- perhaps the following is m-u-c-h easier to
understand.

Consider the following cash flow model: an initial investment of -$1
(A1), 23 semimonthly periods of zero cash flow (A2:A24), and a return
of $1.126658 (A25) in the 24th semimonthly period a year later. The
IRR for that is:

=IRR(A1:A25,0.5)

The result is 0.4981% per semimonthly period -- the same as the IRR for
the OP's cash flow model.

Thus, we earned $0.126658 in a year for an investment of $1. That is
an annual rate of 12.6658%. To me, that means that 12.6658% is indeed
the annualized IRR of the semimonthly IRR of 0.4981%.


----- previous tedious posting -----

However, simple mathematics shows that if you take the 11.96% (actually
11.9552927760608%), divide it by 24 to get 0.498137199002532% and apply that
interest rate sequentially to the cash flows posted by the OP, you obtain the
final result that he posted. To me that means I am earning an effective rate
of 11.96% annually, or .498% per period.

And I could say that if I take my 12.6658%, take the 24th root [1] to
get 0.4981%, and apply that interest rate "sequentially" to the cash
flows posted by the OP, I obtain the "final" result that he posted [2].
To me that means we are earning an effective rate of 12.6658%
annually, or 0.4981% per period.

The point is: we both compute the same periodic rate (0.4981%) first,
then apply some transformation (multiply or compound by a factor 24) to
get our respective annualized rates. So of course, when we each apply
an appropriate inverse transformation (divide by 24 or take the 24th
root), we get back to the same periodic rate that we both started with.
And of course that periodic rate works, since we both computed it
using essentially the same IRR() computation [3]. It proves nothing
for either of us.

I believe the real proof is as follows ....

We can compute the PV of each cash flow using PV(0.4981%,n,0,CFn),
where "n" is the period number and "CFn" is the cash flow for the
period [4]. The sum of the PVs is zero, which is the definition of the
IRR.

Conversely, we can compute the FV of the PV of each cash flow using
FV(0.4981%,n,0,PVn), where "n" is the period number and "PVn" is the PV
of the cash flow [4]. Of course, each FV equals the corresponding
periodic cash flow. That is not surprising: again, I have simply
applied inverse transformations.

But the point is: as we know, FV() assumes that "interest" compounds
periodically. So by the same token, we can take a PV of -$1 and
compute its FV a year later over 24 semimonthly periods using
FV(0.4981%,24,0,-1). The result is $1.126658. Thus, we earn $0.126658
in a year for each $1, which is an annual rate of 12.6658%.

To me, that means that 12.6658% is indeed the annualized IRR of the
semimonthly IRR.

Be that as it may, the fact remains that many people do indeed multiply
or divide to convert between annual and lesser periodic IRRs. Of
course, that is a simpler computation. The dollar error is typically
relatively small -- 5.61% in this case.

HTH.


Footnotes
--------------

[1] I really mean: (1+12.6658%)^(1/24) -1 .

[2] The correct procedure is to use the periodic rate to compute the PV
of the cash flows and demonstrate that the sum of the PVs is zero,
which is the definition of the IRR.

[3] IRR(L11:L25, 0.2235%), although you might have used a different
"guess".

[4] Table of PV and FV of the cash flows. Forgive me if the columns do
not line up. Some newsreaders do strange things, notably Google Groups
:-(, and different font spacings do not help. Hopefully, the only
confusing line might be the last one: the zero is the sum of the PVn
column.

n CFn PVn FVn
0 -600000 600000 -600000
1 -150000 149256 -150000
2 0 0 0
3 0 0 0
4 -5000 4902 -5000
5 -150000 146319 -150000
6 -200000 194125 -200000
7 -400000 386326 -400000
8 -150000 144154 -150000
9 -310000 296442 -310000
10 -150000 142729 -150000
11 -350000 331383 -350000
12 -300000 282634 -300000
13 -415000 389040 -415000
14 3288288 -3067310 3288288
SUM(PV0V14) 0

 

[Ron Rosenfeld]

PS....


Just in case my previous explanation (see below) overwhelmed everyone
-- quite understandably -- perhaps the following is m-u-c-h easier to
understand.

Consider the following cash flow model: an initial investment of -$1
(A1), 23 semimonthly periods of zero cash flow (A2:A24), and a return
of $1.126658 (A25) in the 24th semimonthly period a year later. The
IRR for that is:

=IRR(A1:A25,0.5)

The result is 0.4981% per semimonthly period -- the same as the IRR for
the OP's cash flow model.

Thus, we earned $0.126658 in a year for an investment of $1. That is
an annual rate of 12.6658%. To me, that means that 12.6658% is indeed
the annualized IRR of the semimonthly IRR of 0.4981%.

Here is probably more than you ever wanted to know regarding interest
calculations:

http://www.chicagofed.org/consumer_information/points_of_interest.pdf

(Certainly more than I want to know ))
--ron

 

[Ron Rosenfeld]

Sorry,

Wrong link in my previous post.

But the differences we are discussing have to do with the effects of the
difference between compound and simple interest. Or effective and nominal
rates.

For the example you give, which is equivalent to borrowing $1, and making a
single payment at the end of the period ($1.13), the effective and nominal
rates are the same (12.67% per year). However, if you paid the money back in
24 equal installments, the effective rate would be almost double.


--ron

 

[joeu2004]

But the differences we are discussing have to do with the effects of the
difference between compound and simple interest. Or effective and nominal
rates.

Exactly! I am glad that we agree on that point. And the APY (what the
OP asked for) is the effective rate, by definition. Don't take my word
for it. Go to google.com and type "define: apy". One example:

"The effective, or true, annual rate of return. The APY is the rate
actually earned or paid in one year, taking into account the effect of
compounding. The APY is calculated by taking one plus the periodic rate
and raising it to the number of periods in a year. For example, a 1%
per month rate has an APY of 12.68% (1.01^12 -1)." From
www.fam1st.com/glossary.html .

For the example you give, which is equivalent to borrowing $1, and making a
single payment at the end of the period ($1.13), the effective and nominal
rates are the same (12.67% per year). However, if you paid the money back in
24 equal installments, the effective rate would be almost double.

You are mixing apples and oranges. If you want to talk about of a cash
flow of equal semimonthly returns, we are no longer talking about the
same problem. And yes, the IRR is different -- more than double, as
you say -- not because of the difference between effective and nominal
rates, but because of the time value of money. Using an initial
investment of -$1 and a return of 1.126658 in equal semimonthly
installments ( 0.046945 = 1.126658/24), the semimonthly IRR is 0.9770%
and the APY is 26.2803%.

This is really the last thing I have to say on the subject. I have
tried to help you understand. But I think I am having no effect.
Moreover, it is a moot point because, as I said at the outset, I do
agree that many people do annualize a periodic IRR by simply
multiplying.

That does not produce the APY, by definition; and I argue that for that
reason, it does not produce the true annualized IRR, which should be
the same as the APY, IMHO. You can argue with the latter point, namely
that the annualized IRR should be the same as the APY. But for the
purposes of this thread, I hasten to point out that the OP asked for
the APY. That is the effective rate, by definition, which is the
compounded periodic rate.

 

[Harlan Grove]

Ron Rosenfeld wrote...
....

However, simple mathematics shows that if you take the 11.96% (actually
11.9552927760608%), divide it by 24 to get 0.498137199002532% and apply that
interest rate sequentially to the cash flows posted by the OP, you obtain the
final result that he posted. To me that means I am earning an effective rate
of 11.96% annually, or .498% per period.

Your terminology is wrong. Your 11.96% annual rate is *not* an
_effective_ rate, it's nominal annual compounded semimonthly. The
_effective_ rate is what joeu2... said it was,

(1 + 0.00498137199002532) ^ 24 - 1 = 12.67% (rounded)

That said, simple interest is the first order power series
approximation for compound interest. Often it's close enough, but not
always. If the question of interest here were whether the annual IRR
exceeded 12%, simple interest and compound interest would give
different results. Since IRR is only relevant to compound interest,
exponentiation is always more appropriate for converting IRRs between
different time periods.

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...
...

Your terminology is wrong. Your 11.96% annual rate is *not* an
_effective_ rate, it's nominal annual compounded semimonthly. The
_effective_ rate is what joeu2... said it was,

(1 + 0.00498137199002532) ^ 24 - 1 = 12.67% (rounded)

That said, simple interest is the first order power series
approximation for compound interest. Often it's close enough, but not
always. If the question of interest here were whether the annual IRR
exceeded 12%, simple interest and compound interest would give
different results. Since IRR is only relevant to compound interest,
exponentiation is always more appropriate for converting IRRs between
different time periods.

Harlan and joeu,

Thanks for trying to clear this up for me. I think I understand it.

I guess, because of the slightly different time frames assumed, that results
obtained by using XIRR would be slightly different than those obtained by
converting IRR exponentially to an effective rate.


--ron

 

[vandenberg p]

Hello:

Just to make things even more confusing we have something called the APR
which is reported on loan documents. This rate is computed ignoring the
the inter-year compounding. Lenders have "advertised" this rate as being
the "true" rate. Thus if you pay 1.5% per month the lender will report the
APR as 18% (12*.015), but the effective rate is (1.015)^12-1, closer to 19.6%

Pieter Vandenberg

: Ron Rosenfeld wrote...
: ...
:>However, simple mathematics shows that if you take the 11.96% (actually
:>11.9552927760608%), divide it by 24 to get 0.498137199002532% and apply that
:>interest rate sequentially to the cash flows posted by the OP, you obtain the
:>final result that he posted. To me that means I am earning an effective rate
:>of 11.96% annually, or .498% per period.

: Your terminology is wrong. Your 11.96% annual rate is *not* an
: _effective_ rate, it's nominal annual compounded semimonthly. The
: _effective_ rate is what joeu2... said it was,

: (1 + 0.00498137199002532) ^ 24 - 1 = 12.67% (rounded)

: That said, simple interest is the first order power series
: approximation for compound interest. Often it's close enough, but not
: always. If the question of interest here were whether the annual IRR
: exceeded 12%, simple interest and compound interest would give
: different results. Since IRR is only relevant to compound interest,
: exponentiation is always more appropriate for converting IRRs between
: different time periods.