As Seen on TechCrunch: Finofo Raises \$1.65M CAD!

How to Calculate IRR in Excel: Step-by-Step Guide

Internal Rate of Return (IRR) is a financial metric used to measure the profitability of an investment. It represents the rate of return at which the net present value (NPV) of cash flows from an investment becomes zero. In simpler terms, IRR is the interest rate that makes the present value of an investment's cash inflows equal to the present value of its cash outflows.

How to Calculate IRR in Excel: Step-by-Step Guide

Step 1: Enter the Initial Investment

1. Open Excel and create a new worksheet.
2. In a cell, enter the initial investment amount as a negative value (cash outflow). For example, if the initial investment is \$1,000, enter "-1000".

Step 2: Enter the Cash Flows

1. In the cells below, enter the cash flows for each period. Use positive values for cash inflows and negative values for cash outflows. For example, if you receive \$200 per month for five months, you'd enter "200" in five consecutive cells.

Step 3: Use the IRR Function

1. Select the cell where you want to display the IRR result.
2. Go to the "Formulas" tab on the Ribbon.
3. Click on "Financial" in the Function Library group.
4. Choose "IRR" from the dropdown menu.

Step 4: Set the Values for the IRR Function

1. The IRR function will prompt you to enter values for "Values" and "Guess".
2. Select the range of cash flows (excluding the initial investment) for the "Values" field.
3. For the "Guess" field, you can leave it blank or enter an initial guess for the IRR. Often, leaving it blank works well.

Step 5: Get the IRR Result

1. Press "Enter" or click "OK" after entering the values.
2. The cell will display the calculated IRR.

Example

Suppose you have an initial investment of -\$1,000 and cash flows of \$200, \$250, \$300, \$350, and \$400 over five consecutive periods. The formula would look like this:

=IRR(B2:B6)

Conclusion

By following these steps, you can easily calculate the internal rate of return (IRR) for your investment using Excel. IRR is a valuable tool for assessing the potential profitability of an investment, and Excel provides a convenient way to perform these calculations.