How to Calculate NPV Using XNPV Function in Excel

A sum of money is not the same from one period to the other in time. For example, if you won $500 in the lottery 50 years ago you would have been richer than if you had won it yesterday. This rule reflects the power of accumulated interest.

To evaluate the profitability of an investment project, you may use net present value (NPV). NPV is the calculation of the net cash input that a project should obtain in today's dollars, considering the value of money over time. Although it is possible to calculate NPV with conventional mathematical functions, Excel has a dedicated function to calculate NPV.

The following article explains how to use this function in the case of a calculation of NPV with cash flows that are collected in the same periods each year (end of period), and in the case of a calculation of NPV with cash flows that are collected in the same periods every year (but at the beginning of the period), and in the case of a calculation of NPV, with cash flows happening at different time periods.

To take the previous example, if you had won $500 some 50 years ago and you had placed all that money in an investment vehicle with an annual return of 5%, it would have grown to $5,733 today, or $500 * (1 + 5%)^50. 

Calculating the present value of an amount to be received in the future is called discounting. When the future value of a present amount is calculated, it is called capitalization.

To calculate the NPV of an investment project, you must consider the present value of all cash receipts and all cash disbursements related to the project. Generally, if the result is greater than zero dollars, we should accept the project. Otherwise, we should choose to drop it. In other words, realizing a project with an NPV greater than zero dollars will add to a company's value.

The choice of a discount rate is usually linked to the level of risk for the project. If the project is equivalent to the average risk of the company, we can use the weighted average cost of business capital.

Thus, if we take the table of cash flows related to an investment project:

.

.

Additionally, if we assume a 10% discount rate and a machine lifespan of 15 years, this is the result:

The Excel net present value formula:

NPV = -$232,000 + $38,800 (1+0,10)^-1 + $38,800 (1+0,10)^-2 + $38,800 (1+0,10)^-3 + … + $38,800 (1+0,10)^-15

NPV = $63,116 

This means that by choosing to allocate $232,000 today to replace the machine, the company will experience growth of $63,116.

In the event that the cash flows are always collected on the same date each year—the end of a period—you can simply use the basic Excel function, NPV. This function (as illustrated below) requires two parameters: The first is the discount rate and the second is the cash-flow range. 

In the event that the cash flows are always collected on the same date every year, but an earlier date than the period's end, simply multiply the NPV by (1 + rate). Indeed, the basic Excel function assumes that the cash flows are received at the period's end. Similarly, in such a scenario, the first stream should be considered at time 0, so we could simply exclude the NPV function and add it to the NPV of three other flows, which would then be considered at the cash-flow period end but with a lag of a year (see example below).

Finally, if you try to calculate an NPV of a project that generates cash flows at different moments in time, you should use the XNPV function, which includes three parameters: The first is the discount rate, the second is the series of cash flows and the third is the range of dates when the cash flows are received in time. 

Using Excel can make calculating NPV quick and (relatively) easy.