Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 30

Unconventional Financial 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 ⇒ Excel Tools ⇒ 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 icon.

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