How to calculate NPV in Excel using Functions

In the last two posts, you learnt the basics of Net Present Value (NPV) and how to calculate NPV using formulae. However, Microsoft Excel makes it easy for you with two inbuilt functions. The first is NPV (surprise!) and the second is XNPV (my favourite).

NPV

From the official help article, NPV takes the following syntax:

NPV(rate,value1,[value2],...)

The function uses the following arguments:

• Rate Required. The rate of discount over the length of one period
• Value1, value2, … Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income
• Value1, value2, … must be equally spaced in time and occur at the end of each period
• NPV uses the order of value1, value2, … to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence
• Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored

Simply put, you feed your cash flows to NPV and it will chomp them up and give you the net present value of those cash flows at the specified rate. One important point to remember is that these cash flows are assumed to be at equal periods and hence this can be used if your cash flows are arranged in this manner.

However, if you have date dependent cash flows, or if you’d like to make your model more dynamic, then XNPV is a better function to use. That’s also why it is my favourite NPV calculator and one that I’ve used extensively.

XNPV

From the official help article, XNPV takes the following syntax:

XNPV(rate, values, dates)

The XNPV function takes the following arguments:

• Rate Required. The discount rate to apply to the cash flows
• Values Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value
• Dates Required. A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order

You can download the Excel file at the end of this article that will show how to use both NPV and XNPV. You’ll note the slight difference in the final results which is mainly because of the more accurate dates being used by the XNPV function.