WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » NPV in Excel

NPV in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

NPV Function in Excel

NPV in excel is also known as net present value formula in excel which is used to calculate the difference of the present cash inflow and cash outflow for an investment, it is an inbuilt function in excel and it is a financial formula which takes rate value for inflow and outflow as an input.

The NPV (Net Present Value) function on excel calculates the Net Present Value for periodic cash flows, based on a supplied discount rate and a series of payments. The NPV in Excel is generally leveraged under Financial calculation.

In financial projects, the NPV in excel is useful in finding the value of an investment or analyzing the feasibility of a project. It is recommended that it’s better for financial analysts to use the XNPV function over the regular NPV (Net Present Value) in excel function.

NPV Function in Excel

NPV Formula in Excel

NPV Formula

The NPV in excel accepts the following arguments:

  1. Rate (argument required): It is the discount rate over the length of the period.
  2. Value1, Value2: Value1 is required. They are numeric values that represent a series of payments and income where:
    1. Outgoing payments are mentioned as negative numbers.
    2. Incoming payments are mentioned as positive numbers.

NPV Equation

The calculation of NPV (Net Present Value) of an Investment in the Excel’s NPV function is based upon the following equation:

NPV Function Equation

How to use the NPV Function in Excel? (with Examples)

Let’s take a few NPV excel calculation examples before using the NPV function in the Excel workbook:

You can download this NPV Function Excel Template here – NPV Function Excel Template

Example #1

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

The below spreadsheet shows a simple example of the NPV function in excel.

The rate arguments that are supplied to the function are stored in cell C11, and the value arguments have been stored in cells C5-C9 of the spreadsheet. The NPV in Excel is entered in the cell C13.

NPV Function Example - 1

This function gives the result of $231.63.

NPV-Function-Example - 1-1

Note that, in this example, the initial investment of $500 (shown in cell C5) was done at the end of the first period. That is why this value is considered as the first argument (i.e., value1) to the NPV function in excel.

Example #2

The below spreadsheet shows a further example in which the first payment is made at the start of the first period and how this payment should be considered in the NPV function in Excel.

Again, the rate of 10% is stored in cell C11, and cash flow value arguments of the transactions are stored in between the range C5-C9 of the spreadsheet. NPV in Excel is entered in cell C11.

NPV Function Example - 2

The function gives the result of $2,54.80.

NPV Function Example - 2-1

Note that, as the initial investment of $500 (shown in cell C5) was done at the start of the first period, this value is not included in the arguments to the NPV function in Excel. Instead, the first cash flow is added to the NPV excel result.

As described in example # 2, the NPV formula in excel is established on future cash flows. If the first cash flow happens at the start of the first period, the first cash flow value must be added to the NPV excel result, should not be included in the values arguments.

Things to note about the NPV in Excel

  • The NPV investment begins one period ahead of the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation on excel is based upon future cash flows. If the first cash flow is made at the beginning of the first period, the first value must be explicitly added to the NPV excel result, excluded in the values arguments. For more information, see the examples below.
  • Let’s say n is the number of cash flows in the list of values; the formula for NPV (Net Present Value) in Excel would be:

NPV Function Equation

  • If the arguments are supplied individually, numbers, logical values, blank cells, and text representations of numbers are evaluated as numeric values, while other values of cell in text and error form are ignored by the function.
  • If the arguments are supplied in a range, all non-numeric values in the range are ignored.
  • We need to enter transactions, payments, and income values in the right sequence as NPV functions use the order of the 2nd argument, i.e., value1, value2, … to evaluate the order of cash flows.
  • The key difference between NPV functions and PV function is that PV allows cash flows to begin either at the beginning or at the end of the period.
  • NVP (Net Present Value) function in the latest versions of Excel can accept up to 254 value arguments, but with Excel 2003, only up to 29 values can be supplied to the function.

NPV in Excel Video

Recommended Articles

This has been a guide to NPV Function in Excel. Here we discuss the NPV Formula in excel and how to use NPV on Excel along with practical examples and downloadable excel templates.

  • Examples of NPV
  • NPV Profile Definition
  • PV Function in Excel
  • PMT Function
  • Linear Programming in Excel
17 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download NPV Function Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More