EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Financial Functions in Excel NPV Function in Excel
Secondary Sidebar
Excel Functions
  • Financial Functions in Excel
    • PPMT Function in Excel
    • NPER Function in Excel
    • NPV Formula in Excel
    • MIRR Formula in Excel
    • Excel RATE Formula
    • Excel PV Formula
    • Excel PRICE Function
    • MIRR Excel Function
    • PV Excel Function
    • YIELD Excel Function
    • NPV Function in Excel
    • RATE Function in Excel
    • IRR Function in Excel
    • FV Function in Excel
    • PMT Function in Excel
    • XIRR In Excel
    • IPMT in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel for Finance Course Certification
  • Excel Data Analysis Training
  • Excel for Marketing Training

NPV Function in Excel

By Madhuri ThakurMadhuri Thakur

NPV in Excel

NPV Function (Table of contents)

  • Introduction to Excel NPV Function
  • NPV Formula in Excel
  • Use of NPV Formula in Excel
  • Issues with NPV
  • How to Use NPV in Excel?

Introduction to Excel NPV Function

NPV function in excel is used for calculating Net Present Value, which involves the series of cash flow at a particular discount rate. Mostly NPV function is used in financial analysis work where we need to calculate the investment done in any project or assignment by any company. For example, if a company invests in a product for 3 years, we can find the net present value at any rate for each year to know the worth of the total invested amount.

NPV Formula in Excel

The Formula for the NPV function is as follows:

NPV formula

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Rate (required) – It is the discount rate suitable for the investment which is used to discount the projected net cash flows to time 0.

Values (required) – Refers to the stream of cash flows that the project is generating. The stream of cash flows must be compatible and equidistant, i.e. the time should be equal between two cash flows.

The Math behind NPV Formula:

Below is the mathematical formula for calculating an individual cash flow for the present value.

NPV = F / [ (1 + i)^n ]

Where,

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,497 ratings)
  • PV – Present Value
  • F – Future payment (cash flow)
  • i – Discount rate (or interest rate)
  • n – the number of periods in the future the cash flow is

Use of NPV Formula in Excel

Most financial analysts don’t calculate the net present value with a calculator; instead, they use the NPV function.

=NPV(discount rate%, series of cash flow)

Step 1 – Enter the discount rate in a cell.

Step 2 – Enter the cash flows in the series (In consecutive cells).

Using NPV Formula

Step 3 – Type “=NPV (“ select the discount rate “,” select the cash flow cells “)”.

Using NPV Formula 1

The NPV function returns the result as $34.4.

Using NPV Formula 1-1

Issues with NPV

First of all, what’s the issue with NPV in Excel? Why so many people face issues while using it? Well, contradictory to popular belief, the NPV function does not actually compute the Net Present Value. Instead, it computes the present value of a series of cash flows, but it doesn’t net out the actual cash outflow at zero time period. This actual outflow of the cash actually needs to be subtracted out manually when using the NPV function.

Before we get into an example, let’s get some concept and understand what NPV actually means in finance. NPV is directly the difference between value and cost. This implies, to find NPV, we just take the present value of a series of future cash flows at a specific discount rate, then simply subtract out what our original cost is to obtain that stream of cash flows.

How to Use NPV Function in Excel?

This NPV function is very simple easy to use. Let us now see how to use the NPV Function with the help of some examples.

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

Let’s take a few examples. Suppose we have the following series of cash flows as shown in the below examples:

Example #1

Suppose we are working on the below dataset on cash inflows and outflows:

The below spreadsheet gives a simple illustration of how the NPV function works in Excel.

The rate arguments applied to the function are stored in cell C11, and the value arguments have been stored in cells C5-C9 of the spreadsheet.

Example 1

The NPV is entered in cell C12.

Example 1-1

The NPV formula is applied in cell C12.

NPV Example 1-2

When we apply the NPV function on a dataset in the spreadsheet, it gives the result as $1128.45.

NPV Example 1-3

Note that, in this example, the investment made initially of $200 (shown in cell C5) took place at the end of the first period. That is why this value is considered the first argument (i.e. value1) to the NPV Function.

The above NPV calculation of $1128.45 considers the -$200.00 initial cash outlay in the series of cash flows. The formula used in cell C12 to calculate NPV is:

=NPV(C11,C5:C9)

Example #2

The below spreadsheet shows another illustration of where the first payment is made initially during the first instance and why this payment should be considered in the NPV Function.

Example 2

The rate is again 10% and is in cell C11, and value arguments like the cash flow of the transactions are in between the cells ranging C5-C9 of the spreadsheet. The NPV function is entered in cell C12.

NPV Example 2-1

This time, when we apply the NPV function on the dataset in the spreadsheet, it gives the result as $1241.29.

Example 2-2

We notice that the investment made initially of $200 (shown in cell C5) was done during the investment at the start of the first instance; this value gets excluded in the arguments of the NPV Function. Instead, the first cash flow is added separately to the NPV result.

The above NPV calculation of $1241.29 correctly excludes the $200 initially invested cash outlay in the series of cash flows and then nets it out from the result of the NPV Formula. Below is the formula used in cell C12 to correctly calculate NPV above:

=NPV(C11,C6:C9)+C5

As described in the above example, the NPV (Net Present Value) formula in excel is based on cash flows in the future. If the first cash flow takes place at the initial stage of the first instance, the first cash flow value should be excluded from the values arguments and must be added to the NPV result separately. 

Things to Remember

  • 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 future cash flows derive the NPV computation on excel. If the first cash flow is made at the start of the first period, the first value must be directly added to the NPV result, eliminating the values arguments. Check the examples below for more information.

Let’s take n as the number of cash flows in the list of values; the formula for NPV (Net Present Value) in Excel would be calculated using:

Net Present Value(NPV)

  • If the arguments are provided in a range, and all of the non-numeric values in the range are ignored.
  • If the arguments are provided individually, numbers, logical values, blank cells, and text representation of numbers are computed as numeric values, while the function ignores other values of a cell that are in text and error form.
  • The key difference between PV functions and NPV function is that the PV function allows the flow of cash to begin either at the start or at the end of the period.
  • We need to type in transactions, payments, and income values in a correct sequence as NPV functions use the order of the 2nd argument, i.e. value1, value2, value3 … to check the order of cash flows.
  • 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.

Recommended Articles

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

  1. TEXT Function in Excel
  2. PMT Function in Excel
  3. COLUMN Function in Excel
  4. OR Function in Excel
Popular Course in this category
Excel for Finance Training (18 Courses, 7+ Projects)
  18 Online Courses |  7 Hands-on Projects |  85+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download NPV Function Excel Template

EDUCBA

Download NPV Function Excel Template

EDUCBA

डाउनलोड NPV Function Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more