NPV Function

What is the NPV Function?

The NPV Function is an Excel Financial functionFunctionsList of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst that will calculate the Net Present Value (NPV)Net Present Value (NPV)Net Present Value (NPV) is the value of all future cash flows (positive and negative) over the entire life of an investment discounted to the present. NPV analysis is a form of intrinsic valuation and is used extensively across finance and accounting for determining the value of a business, investment security, for a series of cash flows and a given discount rate. It is important to understand the Time Value of Money,Time Value of MoneyThe time value of money is a basic financial concept that holds that money in the present is worth more than the same sum of money to be received in the future. This is true because money that you have right now can be invested and earn a return, thus creating a larger amount of money in the future. (Also, with future which is a foundational building block of various Financial ValuationFinancial Modeling and Valuation methods.

In financial modelingFinancial Modeling for BeginnersFinancial modeling for beginners is our introductory guide to financial modeling - we cover how to build a model, Excel formulas, best practices, and more., the NPV function is useful in determining the value of an investment or understanding the feasibility of a project.  It should be noted that it’s usually more appropriate for analysts to use the XNPV function instead of the regular NPV function.

Formula

=NPV(rate,value1,[value2],…)

The NPV function uses the following arguments:

1. Rate (required argument) – This is the rate of discount over the length of the period.
2. Value1, Value2 – Value1 is a required option. They are numeric values that represent a series of payments and income where:
   • Negative payments represent outgoing payments.
   • Positive payments represent incoming payments.

The NPV function uses the following equation to calculate the Net Present Value of an Investment:

How to use the NPV Function in Excel?

To understand the uses of the function, let’s consider a few examples:

Example – Using the Function

Suppose we are given the following data on cash inflows and outflows:

The required rate of return is 10%. To calculate the NPV, we will use the formula below:

We get the result below:

The NPV formulaNPV FormulaA guide to the NPV formula in Excel when performing financial analysis. It's important to understand exactly how the NPV formula works in Excel and the math behind it. NPV = F / [ (1 + r)^n ] where, PV = Present Value, F = Future payment (cash flow), r = Discount rate, n = the number of periods in the future is based on future cash flows. If the first cash flow occurs at the start of the first period, the first value must be added to the NPV result, not included in the values arguments.

Things to remember

1. Arguments must be numerical, or function with a numerical output. Other forms of input will result in an error.
2. Arrays that are used as input will only have its numerical values evaluated. All other values in the array will be ignored.
3. The input order matters for the series of cash flows.
4. The NPV function assumes that payments are spaced on equal periodic payments.
5. The NPV function and IRR function (Internal Rate of Return) are closely related. IRR is the rate for which the NPV equals zero.

Click here to download the sample Excel file

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

• Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
• Advanced Excel Formulas Course
• Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
• Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts