Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? :-) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? :-) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
financial function ..please | Excel Worksheet Functions | |||
growth rate but not average | Excel Discussion (Misc queries) | |||
FVSCHEDULE should allow cell reference for interest rate schedule | Excel Worksheet Functions | |||
XNPV vs. NPV(quarterly) different results | Excel Worksheet Functions | |||
APR - Annual Percentage Rate to Actual Interest Rate | Excel Worksheet Functions |