Microsoft Office Tutorials and References

In Depth Information

**Chapter 30: Unconventional Financial Functions: XNPV and XIRR**

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

work without it,

See Figure 30.1. All the other Excel Add-Ins can be activated/installed with the

same procedure.

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.