EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Financial Functions in Excel NPV Formula in Excel
 

NPV Formula in Excel

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 9, 2023

NPV Formula in Excel

 

 

Introduction to NPV Formula in Excel

NPV function or formula comes under the FINANCIAL category of function in Excel. It’s a financial calculation to determine or measure the value of investments. It calculates the difference between the cash inflow (income) & cash outflow (cash you pay for the investment). NPV function helps out in analyzing the feasibility of a project & the time value of money.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Most commonly used function by financial analysts in financial modeling & analysis, equity investment, investment banking, real estate project, project financial assessment & loans & payouts from insurance contracts. NPV function is used as both worksheet & VBA function

Definition

NPV (Net Present Value) function in Excel calculates or determines the Net Present or actual Value of investment based on the series of future payments (Negative value) & income (positive values) concerning supplied discount rate & initial investments made.

The formula for the NPV function

Below is the formula:

NPV Formula

Unfortunately, Excel does not define or calculate the NPV function correctly. To calculate NPV correctly in Excel, you should exclude the initial cash outflow (Investment) from your NPV formula and add the original investment amount at the end of the NPV formula to find the actual NPV.

=NPV (rate, value1, [value2], [value3] …) + the initial investment

or

=NPV (Discount rate, range of values) + the initial investment

The NPV function syntax or formula has the below-mentioned arguments:

  • Values: It is a series of cash flows that indicates income & a series of future cash payments.
Note: We should not include the initial investment in the value argument of the NPV formula, as the NPV function or calculation is based on future cash flows.

Here negative values are considered outgoing payments, whereas Positive values are treated as incoming payments or income.

Concerning the latest version of Excel, NPV can accept up to 254 value arguments.

  • Rate: It is a discount or interest rate for a specific period of time.
Note: The rate argument can be directly entered as 5% in percentage format, or it can be entered as a decimal value, i.e., 0.05 for 5%

In the output, a Positive or higher NPV value indicates that the investment is desirable, takes a good decision, and adds more or better value for your investment (profitable project or investment), whereas negative NPV values indicate a loss.

How to Use NPV Formula in Excel?

Let’s look at how the NPV function works in Excel.

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

Example #1 – Finding the Net Present Value

In the below-mentioned example, the Table contains the below-mentioned details.

The initial investment is 100000 (the initial investment is expressed in negative value), which is a cash outflow & interest rate is 10%

Series of cash flow: Year 0 -100000

Year 1 20000

Year 2 20000

Year 3 20000

Year 4 20000

Year 5 20000

Year 6 20000

Year 7 20000

Year 8 20000

Year 9 20000

Year 10 20000

I need to find NPV (NET PRESENT VALUE) using NPV FUNCTION.

NPV Formula in Excel example 1.1

Now, let’s apply the NPV function in cell “D16“. Select the cell “D16” where the NPV function needs to be applied, click the insert function button (fx) under the formula toolbar, and the dialog box will appear, type the keyword “NPV“ in the search for a function box, NPV will appear in select a function box.

NPV Formula in Excel example 1.2

Double click on the NPV function, and A dialog box appears where arguments for the NPV function must be filled or entered.

i.e. =NPV (rate, value1, [value2], [value3] …) + the initial investment

  • Rate: It is a discount or interest rate for a specific period of time.
Note: It can be entered as a decimal value, i.e., 0.10 for 10% here
NPV Formula in Excel example 1.3
  • Values Argument: It is a series of cash flows excluding the initial investment with a negative sign at the start of the stream. To select an array or cell reference, click inside cell B6, and you’ll see the cell selected, then select the cells till B15 so that the column range will get selected, i.e., B6:B15.

NPV Formula in Excel example 1.4

Click ok after entering the two arguments. =NPV(0.1,B6:B15)

To this formula, =NPV (0.1, B6:B15), you must add the initial investment values to get the correct NPV.

NPV Formula in Excel example 1.5

The above NPV calculation correctly excludes the 100000 initially invested cash outlay in the series of cash flows and then results in the correct NPV value of 22,891

NPV Formula in Excel example 1.6

Example #2 – Investment Based on Highest Returns

In the below-mentioned table, Suppose I want to select any one investment option from two given options with the same initial investment of 100000.

Here I need to compare NPV value, based on a 9% interest rate for both projects with an initial investment of 100000.

NPV Formula in Excel example 2.1

Let’s apply the NPV function in cell “B12” for Project 1 & “C12” for Project 2.

Select cell “B12” where the NPV function needs to be applied; click the insert function button (fx) under the formula toolbar; the dialog box will appear, type the keyword “NPV“ in the search for a function box, NPV function will appear in Select a function box.

The above step is simultaneously applied in cell “C12” also.

NFE example 2.2

Double click on the NPV function, and a dialog box appears where arguments for the NPV function must be filled or entered.

i.e. =NPV (rate, value1, [value2], [value3] …) + the initial investment

  • Rate: It’s a discount or interest rate for a specific period of time.
Note: It can be entered directly as a decimal value, i.e., 0.09 for 9% or 9%.
NFE example 2.3

A similar procedure or steps is followed for Project 2 and for entering the rate argument.

  • Value Argument: It is a series of cash flows excluding the initial investment (Year 0).

Here the value argument will be B7:B11 (Cash flow from year 1 to year 5). A similar procedure or steps is followed for Project 2 and for entering the value argument.

NFE example 2.4

Click ok after entering the two arguments. ‘=NPV(0.09,B7:B11).

To this formula, =NPV(0.09, B7:B11), you must add the initial investment values to get the correct NPV.

For Project 1

=NPV(0.09,B7:B11)+B6 i.e. returns the NPV value 4,045

NFE example 2.5

Whereas

For project 2

=NPV(9%,C7:C11)+C6 i.e. returns the NPV value 4,438

NFE example 2.6

Inference: Based on the NPV, Project 2 or investment is preferable; it has given a better NPV than Project 1.

Things to Remember

  • The NPV function ignores value arguments such as text values, empty cells or logical values, representations of numbers, or error values that cannot be translated into numbers.
  • NPV function helps out in analyzing the feasibility of a project & the time value of money.

Recommended Articles

This is a guide to NPV Formula in Excel. Here we also discuss How to Use NPV Formula in Excel along with the examples and Excel template. You can also go through our other suggested articles to learn more –

  1. Excel Evaluate Formula
  2. Advanced Formulas in Excel
  3. NPV Function in Excel
  4. Normal Distribution Formula in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

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

EDUCBA

Download NPV Formula Excel Template

EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

Forgot Password?

EDUCBA

Download NPV Formula Excel Template

EDUCBA

डाउनलोड NPV Formula Excel Template

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW