Microsoft Office Tutorials and References

In Depth Information

**Measuring Your Internals**

Measuring Your Internals

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. The 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 to 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?

The 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 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. I know this

sounds crazy, but Excel may need your help here (though most times it

won’t). The IRR function works by starting with a guess at 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
) over and

over until it arrives at the correct answer. If it doesn’t figure it out in 20 tries,

then the #NUM! error is returned. In this case, you could enter a guess into

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 you expect a 5

percent loss. If you don’t enter a guess, Excel assumes 0.1 (10 percent).

Figure 6-4 shows a business venture that has been evaluated using IRR. The

project is to create a music CD and market it. Assorted costs such as studio

time are cash flows out, entered as negative numbers. The one positive value

in cell B7 is the expected revenue.