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
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.