Measuring Your Internals with Excel’s IRR Function

Ever wonder how to use Excel’s IRR function? This is how it works. Which is better to do: pay off your credit card or invest in Uncle Ralph’s new business venture? You’re about to finance a car. Should you put down a large down payment? Or should you put down a small amount and invest the rest? How can you make decisions about alternative financial opportunities like these?

The Internal Rate of Return (IRR) method helps answer these types of questions. Excel’s IRR function analyzes the cash flows in and out of an investment and calculates an interest rate that is the effective result of the cash flows. In other words, all the various cash flows are accounted for, and one interest rate is returned. Then you can compare this figure with other financial opportunities.

Perhaps Uncle Ralph’s business venture will provide a 10 percent return on your investment. On the other hand, the credit card company charges you 12 percent on your balance. In this case, paying off the credit card is wiser. Why? Because earning 10 percent is pointless when you’re just losing 12 percent elsewhere. Uncle Ralph will understand, won’t he?

Excel’s IRR function takes two arguments. The first is required; the second is optional in some situations and required in others.

The first argument is an array of cash flows. Following the cash-flows standard, money coming in is entered as a positive value, and money going out is entered as a negative value. Assuming that the particular cash flows in and out are entered on a worksheet, the first argument to the function is the range of cells.

The second argument is a guess at what the result should be. This may sound crazy, but Excel may need your help here (though most times, it won’t). The IRR function works by starting by guessing the result and calculating how closely the guess matches the data. Then it adjusts the guess up or down and repeats the process (a technique called iteration) until it arrives at the correct answer. If Excel doesn’t figure it out in 20 tries, the #NUM! error is returned. In this case, you could enter a guess in the function to help it along. For example, 0.05 indicates a guess of 5 percent, 0.15 indicates a guess of 15 percent, and so on. You can enter a negative number, too. For example, entering –0.05 tells the function that you expect a 5 percent loss. If you don't enter a guess, Excel assumes 0.1 (10 percent).

The following image shows a business venture that has been evaluated with Excel’s IRR function. The project is to create and market t-shirts. Assorted costs such as paying artists are cash flows out, entered as negative numbers. The one positive value in cell B7 is the expected revenue.

Excel’s IRR function has been used to calculate an expected rate of return. The formula in cell B10 is =IRR(B3:B7). The entered range includes all the cash flows, in and out.

This project has an internal rate of return of 12 percent. By the way, the investment amount in this case is the sum of all the cash flows out: $8,400. Earning back $11,960 makes this a good investment. The revenue is significantly higher than the outlay.

Even though a business opportunity seems worthy after IRR has been applied, you must consider other factors. For example, you may have to borrow the money to invest in the business venture. The real number to look at is the IRR of the business venture less the cost of borrowing the money to invest.

However, the project can now be compared with other investments. Another project may calculate to a higher internal rate of return. Then the second project would make sense to pursue. Of course, don’t forget the fun factor. Making t-shirts may be worth giving up a few extra points!

When you’re comparing opportunities with Excel’s IRR function, a higher returned value is a better result than a lower IRR.

The image below compares the business venture shown above with another investment opportunity. The second business venture is a startup videography business for weddings and other affairs. There is a significant outlay for equipment and marketing. An internal rate of return is calculated for the first year, and then for the first and second year together. Cell H10 has the formula =IRR(H3:H5), and cell H11 has the formula =IRR(H3:H6). It's clear that even within the first year, the second business venture surpasses the first.

This is how to use the IRR function:

1. Enter a series of cash-flow values:
   Money paid out, such as the initial investment, as a negative value

   Money coming in, such as revenue, as a positive value

2. Type =IRR( to begin the function entry.
3. Drag the cursor over the range of cells containing the cash flows, or enter the range address.
4. Optionally, enter a guess to help the function.

   To do this, type a comma (,) and then enter a decimal value to be used as a percentage (such as 0.2 for 20 percent). You can enter a positive or negative value.

5. Type a ) and press Enter.
   Considering that IRR is based on cash flows, in and out, it’s prudent to include paying yourself, as well as accounting for investments back in the business. Salary is cash flow out; investment is cash flow in.

The image seen below expands on the videography business with a detailed example. As a business, it has various cash flows in and out — investment, utility payments, professional fees (to the accountant and lawyer), advertising, salary, and so on.

The internal rate of return for the first 3 months of the business is displayed in cell E1. The formula is =IRR(B4:B25,-0.2). By the way, this one needed a guess to return the answer. The guess is –0.2. The internal rate or return is –5 percent. The videography business is not a moneymaker after a few months, but this is true of many startups.

Note that this example includes dates. Excel’s IRR function works with an assumption that cash flows are periodic, which they aren’t in this example. Another function, XIRR, handles dates in its calculation of the internal rate of return.