help needed for Internal rate of return
 

Hi.

I am new to this group.

I am having the values in cells a1 to a23 . 1000 in each cell

In the a24 cell the value entered was 24000

Now I want to have the INTERNAL RATE OF RETURN in the cell a25

I put the formula as =IRR(a1:a24)

Now I have the following error.

"The formula or function used is dividing by zero or empty cell"

Any help is awaited.

TT

help needed for Internal rate of return
 

No News

I just checked the help on the IRR function and you need to have at least
one postitive and one negative value in your array. If all your values are
1000 that does not work.

Dave

"No News" wrote in message
...
Hi.

I am new to this group.

I am having the values in cells a1 to a23 . 1000 in each cell

In the a24 cell the value entered was 24000

Now I want to have the INTERNAL RATE OF RETURN in the cell a25

I put the formula as =IRR(a1:a24)

Now I have the following error.

"The formula or function used is dividing by zero or empty cell"

Any help is awaited.

TT

help needed for Internal rate of return
 

No News wrote:
I am having the values in cells a1 to a23 . 1000 in each cell
In the a24 cell the value entered was 24000
Now I want to have the INTERNAL RATE OF RETURN in the cell a25
I put the formula as =IRR(a1:a24)
Now I have the following error.
"The formula or function used is dividing by zero or empty cell"

You need to show direction of cash flows by using the sign of the
number. The choice of negative and positive is arbitrary. Most people
use "-" to represent outflows (1000?) and "+" or no sign to represent
inflows (24000?).

But that alone will not solve your problem. When I use -1000 and
24000, I still get #DIV/0. The problem is: you need a "guess"
parameter for this cash flow. The default guess is 10%. I found that
1% works well.

help needed for Internal rate of return
 

Dear Dave.

What you have pointed out is correct. I missed to tell you that I put -1000
only in all the cells.

Thank you for your response.

Also please note the other response from
" is much helpfull and understand the function well for
me.

Thanks for you both.

TT

"Dave" wrote in message
...
No News

I just checked the help on the IRR function and you need to have at least
one postitive and one negative value in your array. If all your values
are
1000 that does not work.

Dave

"No News" wrote in message
...
Hi.

I am new to this group.

I am having the values in cells a1 to a23 . 1000 in each cell

In the a24 cell the value entered was 24000

Now I want to have the INTERNAL RATE OF RETURN in the cell a25

I put the formula as =IRR(a1:a24)

Now I have the following error.

"The formula or function used is dividing by zero or empty cell"

Any help is awaited.

TT

help needed for Internal rate of return
 

Dear,

First of all thank you for your response.

I also occur the same. That is to say upto 1% it worked well.

But below that........????

any help?

TT

wrote in message
oups.com...
No News wrote:
I am having the values in cells a1 to a23 . 1000 in each cell
In the a24 cell the value entered was 24000
Now I want to have the INTERNAL RATE OF RETURN in the cell a25
I put the formula as =IRR(a1:a24)
Now I have the following error.
"The formula or function used is dividing by zero or empty cell"

You need to show direction of cash flows by using the sign of the
number. The choice of negative and positive is arbitrary. Most people
use "-" to represent outflows (1000?) and "+" or no sign to represent
inflows (24000?).

But that alone will not solve your problem. When I use -1000 and
24000, I still get #DIV/0. The problem is: you need a "guess"
parameter for this cash flow. The default guess is 10%. I found that
1% works well.

help needed for Internal rate of return
 

wrote in message
oups.com...
No News wrote:
I am having the values in cells a1 to a23 . 1000 in each cell
In the a24 cell the value entered was 24000
Now I want to have the INTERNAL RATE OF RETURN in the cell a25
I put the formula as =IRR(a1:a24)
Now I have the following error.
"The formula or function used is dividing by zero or empty cell"

You need to show direction of cash flows by using the sign of the
number. The choice of negative and positive is arbitrary. Most people
use "-" to represent outflows (1000?) and "+" or no sign to represent
inflows (24000?).

But that alone will not solve your problem. When I use -1000 and
24000, I still get #DIV/0. The problem is: you need a "guess"
parameter for this cash flow. The default guess is 10%. I found that
1% works well.


microsoft.public.excel.worksheet.functions



news.microsoft.com

help needed for Internal rate of return
 

No News wrote:
I also occur the same. That is to say upto 1% it worked well.
But below that........????

I do not understand the question. You might need to use a different
"guess" for each situation. For some situations (like this one, in
fact), a "guess" less than 1% might work even better. In fact, some
people claim (I believe) that -10% "always" works. I cannot imagine
why it would, if that is indeed the case. Are you asking how to choose
a "guess"?

By the way, are you aware that IRR() returns the rate per period? So
if you have monthly cash flows, the IRR() returns a monthly rate. If
you always want an annual rate, you will need to do one of two things,
depending on which "school" you want to follow: (a) either multiply
the monthly rate by 12; or (b) compute the compounded rate with one of
the following:

=(1+IRR(...))^12 - 1

=fv(IRR(...), 12, 0, -1) - 1

IMHO, the compounded rate is the "right" answer. But there are many
academic sources that simply multiply by 12. I suspect that stems from
how, in the US, savings and loan annual interest rates are "downsized"
for shorter periods, namely by dividing the annual rate by the number
of periods.

help needed for Internal rate of return
 

Dear,

Thank you verymuch for providing me these much of informative details.

TT


wrote in message
oups.com...
No News wrote:
I also occur the same. That is to say upto 1% it worked well.
But below that........????

I do not understand the question. You might need to use a different
"guess" for each situation. For some situations (like this one, in
fact), a "guess" less than 1% might work even better. In fact, some
people claim (I believe) that -10% "always" works. I cannot imagine
why it would, if that is indeed the case. Are you asking how to choose
a "guess"?

By the way, are you aware that IRR() returns the rate per period? So
if you have monthly cash flows, the IRR() returns a monthly rate. If
you always want an annual rate, you will need to do one of two things,
depending on which "school" you want to follow: (a) either multiply
the monthly rate by 12; or (b) compute the compounded rate with one of
the following:

=(1+IRR(...))^12 - 1

=fv(IRR(...), 12, 0, -1) - 1

IMHO, the compounded rate is the "right" answer. But there are many
academic sources that simply multiply by 12. I suspect that stems from
how, in the US, savings and loan annual interest rates are "downsized"
for shorter periods, namely by dividing the annual rate by the number
of periods.