You've successfully subscribed to The Finbox Blog
Great! Next, complete checkout for full access to The Finbox Blog
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Net Present Value (NPV) Explained: Formula, Excel Calculation, Free Template, And More

Net Present Value (NPV) Explained: Formula, Excel Calculation, Free Template, And More

. 5 min read

In this article

What Is The NPV (Net Present Value)?

The Net Present Value is the value of the future cash flows of an investment discounted to the present. Financial analysts and investors use it to determine the potential of an investment and evaluate the intrinsic value of a business, investment securities, and more. Although it may seem a bit difficult at first, the NPV is a very straightforward concept. Let's make things clear with an example.

Alex owns a shop in the city center and wants to buy a $10.000 truck that could add $2.000 a year in profit to the business. The truck has an estimated useful life of ten (10) years, after which it must be replaced. Alex could get the funds from a bank with a 4% interest rate. How does he know if this is a profitable investment? He can use the NPV formula! In this article, we'll discuss everything you need to know about it.

How To Calculate NPV: Formula Explained

Now that we have an idea of the NPV meaning, we can move forward and discuss how to calculate the NPV with the right formula to analyze the profitability of a project or an investment. The Net Present Value of a series of future cash flows is equal to the sum of the NPV of each cash flow. Here's the NPV equation:

NPV Formula


Here's a breakdown of the individual components of the NPV formula:

  • NPV = Net Present Value
  • F = Future cash flow
  • d = Discount rate
  • n = The number of periods in the future
  • Xo = First investment

How To Calculate Net Present Value (NPV) In Excel [+ Free Template]

You can easily undertake a Net Present Value calculation in Excel using the NPV or the XNPV functions. The NPV function assumes that all the cash flow occurs at regular and equal periods, while, with the XNPV function, analysts can specify precise dates for each cash flow and calculate the net present value of potentially irregularly spaced cash flows.

When possible, it's preferable to use the XNPV function since it is much more accurate. The NPV function also has some other limitations that we are going to discuss right away—let's analyze the example below:

NPV template excel


Download The Template

First, we can observe that the two functions return different values. That's because the NPV function assumes all periods are equal, while the XNPV handles the real intervals, which in some years are 366 instead of 365.

Let's discuss other differences and analyze how to apply each function to a real example.

How To Use The XNPV Function In Excel

using XNPV function excel


As depicted in the picture above, using the XNPV function in Excel is very straightforward. You just need to set a discount rate, a series of cash flows, and dates.

XNPV(discount rate, cashflow_amounts, cashflow_dates)

How To Use The NPV Function In Excel

using NPV function in Excel


The NPV function is slightly different and involves an additional calculation if the first cash flow happens at the beginning of the first period.

NPV(discount rate, series of cash flow)

First, you don't need to select a range of dates since the NPV function assumes that all the cash flow occurs at regular and equal periods.

Second, you can't include the initial investment in the function, but you need to perform an additional calculation. As the NPV help page states:

The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. [....] If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments.

Let's summarize the differences between the NPV and the XNPV functions.

Differences Between NPV and XNPV functions

  1. The NPV function assumes that all the cash flow occurs at regular and equal periods, while, with the XNPV function, analysts can specify precise dates for each cash flow and calculate the net present value of potentially irregularly spaced cash flows.
  2. Using the NPV function, you don't need to select a range of dates since it assumes that all the cash flow occurs at regular and equal periods.
  3. You can't include the initial investment in the NPV function, but you need to perform an additional calculation.
  4. More often than not, the two functions return different values because the NPV function assumes all periods are equal, while the XNPV handles the real intervals, which are usually different for each month/year.

NPV Example Calculation—Interpreting Net Present Value

As discussed above, Alex owns a shop in the city center and wants to buy a $10.000 truck that could add $2.000 a year in profit to the business. The truck has an estimated useful life of 10 years, after which it must be replaced. Alex could get the funds from a bank with a 4% interest rate.

A net present value calculation can help the investor determine whether to take an investment or not given a series of projected cash flows and the cost of capital. Here's the net present value calculation for the truck:

NPV template excel


Download The Template

First, you need to evaluate if the net present value is positive or not. A net present value greater than zero (0) means that the investment will add value to the business.

Another way to interpret the NPV is to consider it as the investor's profit after interest expenses. In other words, adding the NPV to the initial investment represents the price at which the investor would obtain zero (0) profit. Any price above that number would mean a loss.

NPV vs. IRR

IRR (Internal Rate of Return) is the compounded annual return an investor can expect over the investment’s lifetime. IRR is also the discount rate at which the NPV is equal to zero (0). Let's analyze the difference between NPV and IRR with the previous example:

npv vs irr excel


If Alex gets a loan at a 4% interest rate, he will realize a significant gain on the investment because its compounded annual return (IRR) is 15.09%. As you can see, the NPV is greater than zero (0). As long as Alex can get a loan with an interest rate lower than 15.09%, the investment will add value to the business. As depicted in the picture below, a cost of capital equal to the investment's internal rate of return means zero (0) profit for the investor.

irr vs npv real example


Did you notice that the internal rate of return is different using the IRR and the XIRR function? Can you spot the difference between the two functions? Learn everything you need to know in this IRR guide!