Microsoft Office Tutorials and References
In Depth Information
Chapter 30: Unconventional Financial Functions: XNPV and XIRR
Functions: XNPV and XIRR
You can use IRR and NPV to calculate internal rate of return and net present
values for even interval cash flows. In the examples, you saw in the previous
chapters, we used even end-of-year cash flows. The functions can handle similar end-
of-month cash flows.
For uneven interval cash flows, use the XNPV and XIRR functions. They are
part of the Excel Analysis ToolPak Add-In. If you use the Add-In, you may remember
how to activate it. To invoke the Add-In, click on the Office button
Add-Ins and activate the Analysis ToolPak. Excel 2011 for the MAC has
these functions available without the ToolPak.
Let me repeat the process here if you did not read Chapter 13, and explain how
to activate the Analysis ToolPak Add-In. Follow these steps:
1. Click on the file icon
or in Excel 2007 Office
2. In the menu click on Excel Options.
3. In the Excel Options menu select Add-Ins on the left.
4. In the Add-Ins menu choose the Analysis ToolPak and click on Go.
5. Choose again the Analysis ToolPak in the small menu.
6. Click on Yes when you are asked to install it.
It will take Excel a few seconds to install the Analysis ToolPak. For the Mac
you do not have to activate the Analysis ToolPak. The XNPV and XIRR functions
work without it,
See Figure 30.1. All the other Excel Add-Ins can be activated/installed with the
Amazingly, very few Excel users know about these two functions. Some power
users will even create very complicated functions and macros to achieve this result,
though these functions are readily available.
EXCEL XNPV—THE NET PRESENT VALUE FUNCTION
FOR UNEVEN INTERVALS
Consider an example where you invest $50,000 on a certain date and the cash flows
are as shown in the following table.