IRR

Calculates the internal rate of return of a series of cash flows.

Syntax:

IRR(payments, guess)

payment is a range containing the payments made or received, at regular intervals.

guess (optional, defaults to 10%) is a first guess at the rate.

IRR iterates to find the rate of return which gives a zero net present value for the cash flows. At least one of the cash flows must be negative and at least one positive - to allow the net present value to be zero. The rate of return is per period, and interest is compounded each period.

The payments are assumed to arise at the start of each period; the order in which the payments are given is important.

Example:

IRR(A1:A4)

where A1:A4 contain -5000, 1000, 2000, 3000, returns approximately 8.21%.

Application:

The IRR function is a powerful tool used in financial analysis to evaluate the profitability of a potential investment. It represents the discount rate at which the net present value (NPV) of all cash flows from a project or investment equals zero. In simpler terms, it's the effective rate of return that an investment is expected to generate.

Let's consider a practical example: a small business owner, Sarah, is considering opening a new coffee shop. She needs to determine if this venture is financially worthwhile.

Scenario:

Sarah has identified a great location and has estimated the initial investment and subsequent cash flows for the first five years.

Initial Investment (Year 0): This is the cost to set up the coffee shop, including equipment, rent deposit, renovations, and initial inventory. This is a negative cash flow as it's an outflow of money.
Projected Annual Cash Flows (Years 1-5): These are the estimated net profits (revenue minus operating expenses) the coffee shop is expected to generate each year. These are positive cash flows.

Table of Cash Flows:

	Year	Description	Cash Flow
	A	B	C
1	0	Initial Investment	-$150,000.00
2	1	Year 1 Net Cash Flow	$30,000.00
3	2	Year 2 Net Cash Flow	$45,000.00
4	3	Year 3 Net Cash Flow	$55,000.00
5	4	Year 4 Net Cash Flow	$60,000.00
6	5	Year 5 Net Cash Flow	$65,000.00

Using the IRR Function:

Sarah would use the IRR function to calculate the internal rate of return for this series of cash flows.

The function would look like this:

IRR(C1:C6)

Calculation and Result:

When calculated, the IRR for this project is approximately 18.15%.

Interpretation:

The IRR of 18.15% means that the coffee shop project is expected to generate an average annual return of 18.15% over its five-year life.

Decision-Making:

To decide whether to proceed with the investment, Sarah needs to compare this IRR to her "hurdle rate" or "required rate of return." This hurdle rate is the minimum return she expects to earn on her investment, considering the risk involved.

If Sarah's hurdle rate is 15%, then the coffee shop project's IRR of 18.15% is higher. This indicates that the investment is likely to be profitable and exceed her minimum expectations. She would likely decide to move forward.
If Sarah's hurdle rate is 20%, then the coffee shop's IRR is lower than her required return. In this case, the project may not be financially attractive enough, and she might decide to look for other investment opportunities.

Conclusion:

The IRR function provides a single, easy-to-understand metric that summarizes the profitability of a project. By comparing the calculated IRR to a predetermined hurdle rate, investors and business owners can make informed decisions about which projects to pursue. It helps to answer the crucial question: "Is this investment worth the risk and initial capital outlay?"

Result of IRR(C1:C6):

18.15%