Accounting professionals must understand the difference between NPV vs PV. While these two concepts are closely related, they serve different purposes and help answer different financial questions.
In Excel, both PV (Present Value) and NPV (Net Present Value) are built-in financial functions that simplify complex calculations and support better business decision-making. Whether you're an accounting professional, finance student, CPA candidate, or business analyst, knowing when and how to use each function can improve the accuracy of your financial analyses.
Summary
Present Value (PV) and Net Present Value (NPV) are financial metrics used to determine the current worth of future cash flows based on the time value of money, which can be calculated using specific Excel functions to evaluate whether an investment creates value after accounting for its costs.
What Is Present Value (PV)?
Present Value (PV) measures the current worth of a future amount of money or a series of future cash flows based on a specified discount rate. In other words, PV answers the question: "How much is future money worth today?"
The concept is based on the time value of money, which states that a dollar today is worth more than a dollar received in the future; today's dollar can be invested and earn a return.
Present Value Formula: PV=FV(1+r)nPV = \frac{FV}{(1+r)^n}PV=(1+r)nFV
Where:
- FV = Future value
- r = Discount rate
- n = Number of periods
Excel uses this principle in its built-in PV function.
What Is Net Present Value (NPV)?
Net Present Value (NPV) goes a step further. Instead of calculating the value of a single future amount, NPV evaluates an entire investment by comparing:
- The present value of future cash inflows
- Minus the present value of cash outflows (including the initial investment)
NPV helps determine whether an investment will create value.
NPV Formula: NPV=Present Value of Future Cash Inflows−Initial InvestmentNPV = \text{Present Value of Future Cash Inflows} - \text{Initial Investment}NPV=Present Value of Future Cash Inflows−Initial Investment
If:
- NPV > 0: The investment is expected to generate value.
- NPV = 0: The investment breaks even.
- NPV < 0: The investment may not be financially worthwhile.
NPV vs PV: Key Differences
Although both calculations use discounted cash flow analysis, they answer different questions.
- Purpose:
- PV (Present Value): Finds today's value of future money
- NPV (Net Present Value): Evaluates an investment's profitability
- Includes Initial Investment?
- PV (Present Value): No
- NPV (Net Present Value): Yes
- Measures:
- PV (Present Value): Value of future cash flows
- NPV (Net Present Value): Value created after accounting for costs
- Common Accounting Use:
- PV (Present Value): Loans, retirement planning, annuities
- NPV (Net Present Value): Capital budgeting and project evaluation
- Excel Function:
- PV (Present Value): PV()
- NPV (Net Present Value): NPV()
A simple way to remember the difference: PV tells you what future cash flows are worth today. NPV tells you whether an investment creates value after considering its costs.
How to Use NPV in Excel?
The Excel NPV function calculates the value today of future cash flows using a specified discount rate. Excel assumes that all listed cash flows occur at regular intervals and that the first cash flow occurs one period from now.
NPV Function Syntax: =NPV(discount rate, range of cash flows)
Where:
- rate = Discount rate
- Range of cash flows = Future cash flows
Example: Using NPV in Excel
Figure 1 below shows the sequence of cash flows from two investments. Which investment helps the company more?
Figure 1: Using the Excel NPV function
The NPV function ignores blank cells (add "0" if there is no cash flow during a period) and assumes the first cash flow occurs one period from now and cash flows occur at regular intervals. So, if cash flows occur at the beginning of a time period, we would separate out the first cash flow and compute the NPV of investment 1 using the formula =C7+NPV($C$3,D5:E5), see cell C11 in the figure above.
Copying this formula to cell C12 computes the NPV for Investment 2. Since Investment 1 has a positive NPV and Investment 2 has a negative NPV, we would conclude that Investment 1 helps the company and Investment 2 hurts the company.
If cash flows occur at the end of the year (cash flow for year 0 occurs at the end of year 0), then copying the formula =NPV($C$3,C5:E5) from C14 to C15 computes the NPV of the two investments.
Computing NPV in Excel for Irregularly Spaced Cash Flows
Often cash flows don’t occur at regularly spaced intervals. For these instances, the XNPV function in Excel can be used to compute the NPV of a sequence of irregularly spaced cash flows.
Enter the annual discount rate, dates of cash flows, and the cash flow values. Then, the XNPV function returns the NPV of the cash flows as of the first listed date. For example, Figure 2 below shows the formula =XNPV(B2,C5:C7,B5:B7), computing the NPV of the cash flows (-$80.85)
Figure 2: XNPV function computes NPV
How to Use PV in Excel?
Excel's PV function calculates the present value of a loan, investment, or stream of future cash flows.
PV function syntax: =PV(Rate,Nper,Payment,Future_value,Type).
- Rate = the discount rate.
- Nper = number of periods (the discount rate must be consistent with your choice of period length.)
- Payment = annual payment (use a minus sign if you are paying out money and want PV to return a positive value.)
- Future Value (often 0) = the amount of money paid out (minus sign for paid out, positive sign for money received) at the end of the annuity.
- Type = 0 for end of period cash flows and 1 for beginning of period cash flows.
Example: Using PV in Excel
Figure 3: Examples of the PV function
Assume that the annual discount rate is 12% and we are going to buy a machine.
- In cell B4, we find that if we pay $3,000 for five years at the end of each, the present value of our payments is $10,814.33.
- In cell B5, we find that if the payments are made at the beginning of each year, the present value of the payments is $12,112.05.
- In cell B6, we find that if we pay for five years $3000 at the end of each year and make an additional payment of $500 in five years, then our payments have a present value of $11,098.04.
Learn More with Excel CPE
Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning!
Check out Becker's wide range of CPE courses that teach you to make the most of this powerful tool:
- Excel: Technical Analysis Trading Strategies
- Excel: Enterprise Risk Management
- Excel: Magic with Excel
- Excel: Solve Hard Problems in Corporate Finance
- Excel Metrics: Best Practices
- Python for Excel Users: A Gentle Introduction
These and many more Excel-focused, CPE credit-earning courses are included in Becker's Prime CPE subscription. Sign up now for 12 months of access to over 1700 on-demand, webcast, and podcast CPE courses!
Unlock Unlimited CPE with a Prime Subscription
Becker makes it easy to meet your CPE requirements, gain new skills, and stay aware of critical updates and changes in the industry!
With Prime, you can access over 1,700 courses for a full year and earn unlimited CPE credits.
