How to calculate NPV in Excel?

In this tutorial, we explain how to calculate the net present value (NPV) of an investment project using the NPV and XNPV functions in Excel. We’ll show how to compute the NPV without using these functions as well.

If you prefer to go over this tutorial in a video format or if you’re visiting this page through our YouTube channel to download the Excel template, just click on the “Video tutorial and Excel template” section in the contents box below.

Step 1: Enter the project’s cash flows

The first step is to enter the project’s investment cost and future cash flows. The most common scenario is that there will be an investment cost at t=0 and the project will start generating cash inflows from t=1. Of course, some projects might have investment costs spread over time and/or not start generating cash inflows in t=1.

For the purposes of this tutorial, we will focus on an investment project that requires an investment cost of $1,000 (t=0) and that generates cash inflows of $500 for the following three years (t=1, t=2, and t=3). We have chosen Jan 1, 2023 as the date for t=0, and determined the remaining dates by adding 365 days at a time: Jan 1, 2024 (t=1), Dec 31, 2024 (t=2), Dec 31, 2025 (t=3). Figure 1 shows how we entered the data into Excel.

Step 2: Compute the net present value

A project’s NPV can be calculated by adding up the present values of its cash flows. However, to compute the present values, we need a discount rate. Let’s assume a discount rate of 10%. Then, the project’s net present value is:

−$1,000 + $500/1.10 + $500/1.10² + $500/1.10³ = −$1,000 + $454.55 + $413.22 + $375.66 = $243.43

We’ll show you three different ways to obtain this result in Excel.

Option 1: Manual calculation

The first option is to compute the present value of each cash flow and add them up as shown in Figure 2 below. For example, the present value of $500 received on 01-Jan-24 is calculated in cell D5 as follows:

=D4/(1+B5)^D3

And, the net present value in cell G5 is obtained by adding up the present values of cash flows, including the original investment:

=sum(C5:F5)

Option 2: Use Excel’s NPV function

The second (and faster) option is to use Excel’s inbuilt NPV function. This is illustrated in Figure 3. As you can see, the function requires you to enter the discount rate (cell B5) first. This is followed by each of the cash flows (cells D4, E4, and F4), excluding the original investment at t=0. The function gives you the present value of these future cash flows, so we need to manually consider the initial investment (cell C4) to arrive at the net present value.

=NPV(B5, D4, E4, F4)+C4

Option 3: Use Excel’s XNPV function

The third and final option is to use the XNPV function. This function requires you to enter the exact date of each cash flow. Then, the result is obtained by specifying the discount rate (cell B5), the stream of cash flows including the original investment at t=0 (cells C4:F4), and the array of dates (cells C2:F2) as shown in Figure 4.

=XNPV(B5, C4:F4, C2:F2)

Of course, all three options we have examined above give the same result, which is $243.43.

Plotting an NPV schedule

Based on the example we’re using in this tutorial, we should be investing in the project we’re examining as it has a positive NPV of $243.43. But, what if we’re unsure about the discount rate we’re using? We’ve assumed the correct discount rate to be 10%, but the true discount rate could be higher or lower.

What we could do in such a scenario is to plot an NPV schedule (or profile) in Excel to see whether the project yields a positive net present value even at higher discount rates. In fact, we’d call the discount rate that sets NPV = 0 as the internal rate of return (IRR).

All we need to is to repeat Step 2 for different values of the discount rate as shown in Figure 5. Specifically, we’ve varied the discount rate between 10% and 30% with 1 percentage point intervals, calculating the NPV for each discount rate. Then, the plot was obtained by following these steps:

1. Go to the Insert tab.
2. In the “charts” section, click on the “line chart” icon.
3. Once the Chart Design tab becomes active, click on Select Data.
4. Enter the NPV values (cells G5:G25) in the “chart data range” field.
5. Finally, for the “axis label range”, choose the range of discount rate values (cells B5:B25) to produce the plot.

This analysis suggests that the project is viable so long as the discount rate is below 23.4%, which is the project’s IRR.

Video tutorial and Excel template

What is next?

If you’ve enjoyed this tutorial, you might find the following tutorials useful as well.