Internal Rate of Return Formula

I would type the values into cells, then select the cells in the formula. So your formula should result in 15%.

Alternatively you can use Goal Seek on a NPV formula to set the rate = 0.

Originally Posted by eslee32

I have a scenario in which Company ABC has the option of paying $242,000 in Period 0 in anticipation for savings of $44,000 on a monthly invoice for 12 months. The monthly invoice is usually $70,000/month. Would the correct use of the formula be
=IRR(-242 000, 44 000, 44 000, 44 000, 44 000, 44 000, 44 000, 44 000, 44 000, 44 000, 44 000, 44 000, 44 000)?

If you want to enter the numbers directly, the correct syntax is:

=IRR({-242000,44000,44000,44000,44000,44000,44000,44000,44000,44000,44000,44000,44000})

Two major differences:

1. The cash flows are entered as an array constant; note the curly braces in red. Despite the array constant, the formula can be normally-entered; that is, just press Enter as usual.

2. There is no space in the numbers; to wit: 44000 instead of 44 000.

Note that Excel IRR returns a periodic rate of return consistent with the frequency of the cash flows; a monthly rate, in this case.

If you want an annual IRR, you need to annualize the monthly rate. There are two equally valid methods, depending on conventions and regulations for your jurisdiction and industry.

a. Simple annual rate: =12*IRR(...)

b. Compounded ("effective") annual rate: =(1+IRR(...))^12 - 1

PS.... I just noticed that you wrote a "savings of $44,000". Do you really mean that the monthly cash flow is $26,000 instead of $70,000?! If so, you should use 26000 instead of 44000 in the IRR formula.