RATE v. IRR: which to use?
 

I think this is more of a question about present value concepts and how
to formulate a financial problem than it is about how to use the Excel
functions. The question was sparked by someone else's inquiry.

Suppose I build something and sell it. It costs me $2000 to build it,
and the buyer pays me $135 per month for 36 months. What is my rate of
return?

I thought I could use RATE or IRR equally well. But the results are
very different.

On the one hand, I might compute RATE(36, 135, -2000, 135*36). That
produces a monthly rate of 7.59%. That's the monthly rate at which an
investment of $2000 grows to $4860 over 36 months (verified with FV).

On the other hand, I might compute IRR(A1:A37), where A1 is -2000 and
A2:A37 are 135. That produces a monthly rate of return of 5.89%.
That's the monthly rate at which the sum of the present values of the
$135 monthly cash flows equals the initial investment of $2000
(verified by summing a column of PV).

Which formulation fits the problem better and why? That is, what's
wrong with my thinking in one case or the other?

Or did I make a simple mistake in formulation, and once corrected, both
yield the same result? How?

RATE v. IRR: which to use?
 

In cell A1 enter -2000 and in all cells from A2 to A37 enter 135.
In some other cell enter the following:
=IRR(A1:A37)
In another cell enter the following:
=RATE(36,-135,2000)
These two should give you the same result.
RATE is for constant payments and IRR can be used for unequal payments. They
happen to be equal in this case.

" wrote:

I think this is more of a question about present value concepts and how
to formulate a financial problem than it is about how to use the Excel
functions. The question was sparked by someone else's inquiry.

Suppose I build something and sell it. It costs me $2000 to build it,
and the buyer pays me $135 per month for 36 months. What is my rate of
return?

I thought I could use RATE or IRR equally well. But the results are
very different.

On the one hand, I might compute RATE(36, 135, -2000, 135*36). That
produces a monthly rate of 7.59%. That's the monthly rate at which an
investment of $2000 grows to $4860 over 36 months (verified with FV).

On the other hand, I might compute IRR(A1:A37), where A1 is -2000 and
A2:A37 are 135. That produces a monthly rate of return of 5.89%.
That's the monthly rate at which the sum of the present values of the
$135 monthly cash flows equals the initial investment of $2000
(verified by summing a column of PV).

Which formulation fits the problem better and why? That is, what's
wrong with my thinking in one case or the other?

Or did I make a simple mistake in formulation, and once corrected, both
yield the same result? How?

RATE v. IRR: which to use?
 

Martin P wrote:
In cell A1 enter -2000 and in all cells from A2 to A37 enter 135.
In some other cell enter the following: =IRR(A1:A37)
In another cell enter the following: =RATE(36,-135,2000)
These two should give you the same result.

Klunk! Of course that RATE formulation is equivalent to the IRR
formulation. Thanks.

But my real question is: which of the following formulations is the
correct answer to the problem and why?

The problem was: If I build something at a cost of $2000, and I sell
it for $4860 in payments of $135 over 36 months, what is the monthly
rate of return?

Why is RATE(36,135,-2000) the correct answer instead of
RATE(36,135,-2000,135*36)?
Or is the second formuation the correct answer? (Surprise!)

On one level, I do believe that the IRR is the correct answer for the
problem; and I do see that the first formulation matches the IRR
formulation.

On another level, I do not see what is wrong with the second way of
thinking, namely that I will have $4860 after 36 months of payments of
$135 and an initial investment of $2000.

Obviously I am having a "brain fart". Can someone "clear the air" for
me? :-)

RATE v. IRR: which to use?
 

RATE(36,135,-2000,135*36) would be the formula you would use if you had
135×36 dollars in the 37th month in addition to the all the amounts of 135
dollars that were paid monthly

" wrote:

Martin P wrote:
In cell A1 enter -2000 and in all cells from A2 to A37 enter 135.
In some other cell enter the following: =IRR(A1:A37)
In another cell enter the following: =RATE(36,-135,2000)
These two should give you the same result.

Klunk! Of course that RATE formulation is equivalent to the IRR
formulation. Thanks.

But my real question is: which of the following formulations is the
correct answer to the problem and why?

The problem was: If I build something at a cost of $2000, and I sell
it for $4860 in payments of $135 over 36 months, what is the monthly
rate of return?

Why is RATE(36,135,-2000) the correct answer instead of
RATE(36,135,-2000,135*36)?
Or is the second formuation the correct answer? (Surprise!)

On one level, I do believe that the IRR is the correct answer for the
problem; and I do see that the first formulation matches the IRR
formulation.

On another level, I do not see what is wrong with the second way of
thinking, namely that I will have $4860 after 36 months of payments of
$135 and an initial investment of $2000.

Obviously I am having a "brain fart". Can someone "clear the air" for
me? :-)

RATE v. IRR: which to use?
 

Martin P wrote:
RATE(36,135,-2000,135*36) would be the formula you would use if you had
135×36 dollars in the 37th month in addition to the all the amounts of 135
dollars that were paid monthly

Right. Klunk! Thanks again.