How to Calculate IIR

In finance, the IRR or internal rate of return is a measurement used to evaluate the profitability of investments over a period of time based on the expected cash flows of that investment. Calculating the IRR is easy when working with bonds having a fixed coupon rate, because the exact value of all future cash flows is known. When working with a business model, however, future cash flows must be estimated based upon certain assumptions about future profits and expenses, among other factors. A quick and relatively easy tool for finding the IRR is Microsoft Excel.

Determine your future annual cash flows. If you are calculating the IRR of a bond, simply multiply the coupon rate times the bond's par value. For example, an annual coupon bond with a par value of $1,000 and a coupon rate of 8 percent will have an annual cash flow of $1,000 x .08 = $80. When working with a business plan, you will need to make your best estimate of future cash flows. For example, if you expect sales of a $10 product to be 10,000 units in Year 1 and 15,000 units in Year 2, the expected revenues in Years 1 and 2 would be $100,000 and $150,000 respectively. After subtracting expenses, you would have your yearly cash flows. It is important to note that it is impossible to predict future cash flows of a business with 100 percent accuracy because it requires knowledge of future events.

Enter your information into an Excel spreadsheet. Make a column for years and enter in the number of years for which you are determining the IRR. In another column, enter in the values of the expected cash flows for each of the corresponding years.

Note: An initial investment takes place at Year 0. The investment represents a negative cash flow and should be entered into the spreadsheet as a negative value. For example, if you purchased a $1,000 bond in Year 0, your cash flow is equal to -$1,000. If you invested $500,000 in your business plan in Year 0, your cash flow is equal to -$500,000.

Use Excel's IRR function to calculate the internal rate of return. In a separate cell, enter the formula =IRR(X0:X0). The "XO"s refer to the cells you are including in this formula. You should click and drag through all of your cash flow cells beginning with the negative cash flow for Year 0 and ending with your final entry. Press "Enter" to enter the formula into the cell and you will see a number that represents the IRR.