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:
 
Search JabSto ::




Custom Search