Microsoft Excel is one of the leading analytical software tools available in the international market and is widely used by Excel consultants, financial and analytical firms as well as small business for calculating and analyzing data. Microsoft Excel is known for its ease of use and sheer power. Most importantly, it possesses a wide array of Excel functions which can process and arrange data as per wish of the user and makes his life much easier. The Microsoft Excel XNPV is such a function. In this free Excel help article we will discuss a bit about this function and get an idea about its features and usage.
For cash flows that are not periodic the Excel XNPV function is normally used in order to calculate their net present value. So therefore if the not so regular dates of the cash flows are mentioned to the function with their respective values, it will give the estimated net present value as per the given discount rate. So, it is very convenient when dealing with data that are not necessarily periodic in nature.
In order to use the function properly, the Excel consultant has to be aware of the proper arguments that are to be passed through the excell XNPV function for it to actually tabulate the required results. The XNPV function takes three quantities as its arguments namely rate, value and dates and so is in the form XNPV (rate, value, date). Here, let us take a look at the different parameters and their types to be used:
Rate: The rate mentioned here is the rate of discount to be applied on the cash flows that come in of the investment. It is required for the function to work.
Value: The value field corresponds to all the financial exchanges that occur in the company be it cost or payment of currency. The initial value is generally a cost or capital value and therefore it is negative in value. For the other values that are inserted, as they are payments they are positive; the discount rate is added on to them on an annual nature. For the function to work correctly there must be one negative value and also it should be followed by at least a positive one as well. It is necessary for XNPV as well.
Date: The date parameter keeps a tab on the date of payments that are made to the firm and starts off as soon as the first payment is made. After it is made the dates start to roll on as the payment increases but they can be inserted in any order as the user wishes to. With the dates present, XNPV can calculate the payment discounts efficiently and therefore it is required for the function to work.
The mathematical expression that is used to calculate XNPV is as follows:
XNPV = ? (i=1 to n) Pi/(1+rate)did1/365
Here, di is the date of the i-th payment, d1 is the date of the 0-th payment, Pi is the value of the i-th payment and rate is the discount rate added to them.
There are a certain set of problems and errors that are usually encountered while using the XNPV function, and here we have compiled some of them:
Excel starts off with the date of the first payment and therefore no date can be prior to it in the function. But if such a case occurs then the #NUM! Error is notified. So no date should precede the starting date. The same error also occurs if the arrays of date and value have different lengths.
Excel stores the dates in a sequential manner starting from January 1, 1900 and then counting on and so if the date field has an illegal entry it shows the #VALUE! Error. In fact, any non-numeric field entry shows this error.
The #NAME! Error occurs when the Analysis Tool pack is not present in Excel as an add-in and it can be downloaded easily for both the 2003 and 2007 versions.
The Microsoft Excel XNPV function is a powerful tool in analytics and data entry for any Excel consultant or small business and it is widely used as it is compatible with the 2003, 2004, 2007, 2008, 2010 and 2011 versions of Excel.