Microsoft Excel has been one of the most used applications in the Microsoft Office Suite and over the years has developed not just as a vital tool for Excel consultants and office workers but also for all data-related activity that is possible both at home, and business. So, with Excel there lies the power of tabulating and evaluating data of all sorts and types with main focus given to the operation asked for by the business user. In that regard, it boasts a plethora of methods and functions which at the disposal of any experienced Excel consultant or business user can make him capable of breaking down or building up financial data quickly and easily. One such function is of course the NPV function, which we will cover in this free Excel help article.
The NPV function is used to calculate the Net Present Value of any business or organization and is used for any financial appraisal that may help the firm in the long run for a better economical future. What it does is that it takes in a series of cash flows, both payments and incomes and investments and on to that it affixes the rate of interest that is provided by the user and calculates the present value of the net investment and payments, cash flow in short. Thus the NPV function actually does consider all the financial nuances of the firm at present and calculates its capital value for the future.
The NPV function takes as number of parameters and is not at all constant in number, but usually they can be tabulated as NPV (Rate, Value1, Value2… Value n).
Value: The values fields are where the Excel consultant has to provide the function with all the different cash flows in terms of payment and investments. Usually payments are calculated as negative and incomes as positive due to the direction of cash flow. So any investment or income that is encountered by the company is to be put as positive and any payment as negative. The values have to be of regular intervals and thus equally spaced for proper calculation of the function. There is a provision for putting at maximum a total of 29 arguments as the different values. So, it is obvious that they are required.
Rate: The rate is the rate of discount or subsidy that is provided by the company or that is to be given to the company for all the cash flows it goes through. The user has to provide the function with this rate and then only can the function start calculating the amount of net present value. So, it is also required.
The NPV function calculates the net present value by a mathematical expression similar to:
NPV = ?(i=1 to n) (valuesi/(1+rate)i
Since the NPV function calculates the present cash flows value so in order to truly get the actual result one has to subtract the initial investment after calculating it.
Let us take an example, let the investments be $3000, $5000, $1200 and $4000 for a corporation and the initial investment is $7500 and the rate is 8%. So, the true future value is given as (NPV – Initial Investment): NPV (8, 3000, 5000, 1200, 4000) – 7500.
Which results as $3457.19 and this is the net present value of the company for this project.
Below we have accumulated some of the features that are present in the NPV function:
NPV function makes use of the sequence in which the cash flows are provided to it and therefore it is vital to maintain a proper order of the investments and payments otherwise erroneous results may be shown.
The values field of the NPV function only accepts numerical values and all other inputs that are provided like alphabets, texts, logical symbols and empty fields are all ignored.
If the NPV value given as an output from the function is greater than 0 then it means that the project in question will add monetarily to the company and therefore should be continued, lesser than 0 means it will actually result in a loss and so should be abandoned and a value of 0 means the project will not at all matter in any way to the company.
The IRR is closely related to the NPV and is the rate at 0 NPV i.e. NPV (IRR (),) = 0.
The NPV function is found in all the modern Excel versions like the Excel 2000, 2003, 2007, 2008, 2010 and 2011.