Microsoft Office Tutorials and References

In Depth Information

**Excel XIRR—The Internal Rate of Return Function for Uneven Intervals**

FIGURE 30.1
To Activate the Add-In

January 1,

2009

October 1,

2009

June 15,

2010

October 25,

2010

December 31,

2010

March 1,

2011

June 15,

2012

$50,000

$12,000

$6,000

$15,000

$8,000

$7,000

$15,000

You cannot use the NPV on this uneven interval cash flow case; however, you

can use the XNPV function. This time, when you use the XNPV function, you
do

have to include the initial investment cash flow in the function. (The NPV does not

allow inclusion of the initial investment, which then needs to be subtracted from the

Excel-calculated NPV.)

The syntax of the function is: XNPV (rate, values, dates). In Figure 30.2, you can

see that I selected cell E1, previously named Rate, for the Rate on the Function

Arguments menu. For the values I selected cells B2:B8 (the cash flow of the project/

investment) and for the Date cells A2:A8. The resulting XNPV is $330.37.

The Rate is a number specifying the annual discount (or interest) rate.

The Values are the numbers that specify cash flow values. There must be in your

data selection at least one negative value (a payment), as well as one positive value

(a receipt).

The first date represents the start of the project. The dates may occur in any order

afterward, as long as they are after the start date.

EXCEL XIRRâ€”THE INTERNAL RATE OF RETURN FUNCTION FOR

UNEVEN INTERVALS

Consider again the same example where you invest $50,000 on a certain date and the

cash flows are as shown in the following table: