NPV In Excel

What Is NPV In Excel?

NPV is an excel financial function that determines the difference between the present value of cash inflows and outflows by applying a discount rate. If the NPV is positive, the investment is deemed profitable; if the NPV is negative, the investment is considered loss-making.

For example, Ram has invested ₹1,000 in a shipping company’s bond at the discount rate of 10%. But, first, let us calculate the NPV of the investment. The Column A contains Description, Column B contains Data

=NPV(B2, B3:B6) = ₹391.

Here, NPV is positive and therefore the investment is considered profitable.

What Is NPV In Excel?

NPV stands for Net Present Value which represents the difference between the values of the present cash inflows and outflows applying a discount rate.
The syntax of the NPV function is as follows: =NPV(rate,value1,[value2],…) where rate represents the discount rate.
The investment value should always be negative, with atleast one positive and one negative value.
NPV is used by organizations in financial planning to find the profitability of an investment.

NPV() Excel Formula

rate = It is the discount rate all over the period. It is a necessary argument.
value1,[value2] = value1 is also a necessary argument. It has the numeric value that shows the outflow as the negative value and inflow as the positive value of the payments. In contrast, value2 is an optional argument.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

How To Calculate NPV In Excel?

Let us look at the use of NPV in Excel examples:

Raj has invested ₹100 in the company’s bond and gets back ₹10 for 1st year, ₹20 for 2nd year, ₹50 for 3rd year, and ₹110 for the 4th year. The discount rate is 10%. Let us calculate the NPV of his investment. And also, check whether the investment is profitable or not.

There are two ways to calculate NPV. We can use both methods to calculate NPV for this example: –

#1 – Manual Method

NPV is calculated with the Present Value (PV) in this method. However, it is a time-consuming method.

The formula of Present Value:

PV = FV / (1+r)ⁿ

PV = Present Value
FV = Future Value
r = Rate of Interest
n = Number of Years

The formula of Net Present Value:

NPV = Sum of all PV income – PV payment

	PV=FV/(1+r)ⁿ	Present Value
PV of 1^st year	=10/ (1+0.1) ^1	=9
PV of 2^nd year	=20/ (1+0.1) ^2	=17
PV of 3^rd year	=50/ (1+0.1) ^3	=38
PV of 4^th year	=110/ (1+0.1) ^4	=75
	SUM of all PV income	=138
NPV	=138– 100	=38

Therefore;

FV = 10, 20, 50, 110
r = 10%
n = is every number of years, 1, 2, 3, 4

Step 1: In this example, the table shows the cash flow of investment made by Raj. But, first, we need to find the NPV of the investment.

Column A contains the Description (the starting investment and every year installments).

Column B contains the Period (the number of years).

Column C contains Cash Flow every year.

Column D: Calculates Present Value.

Row 7 contains the Discount Rate and the sum of all present value income.

Row 8 calculates Net Present Value.

Step 2: Calculate the present value for every year.

PV = FV / (1+r)ⁿ

= C3/(1+B$7)^B3

Step 3: Calculate the sum of all present value income.

=sum(D3:D6)

Step 4: Calculate the net present value. The initial output entered is a negative number, so we perform the addition operation:

NPV = D7+C2

= 138+(-100) = 138-100 = 38

#2 – NPV Excel Formula Method

In this method, NPV is calculated with the inbuilt formula of Excel. Therefore, it is the fastest method to calculate NPV.

Step 1: In this example, the table shows the cash flow of investment made by Raj. But, first, we need to find the NPV of the investment.

Column A contains the Description (the starting investment and every year installments).

Column B contains Cash Flow every year.

Row 7 contains the Discount Rate.

Row 8 calculates Net Present Value.

Step 2: Calculate the net present value.

=NPV(rate,values)+initial value

In this case, the NPV function returns the present value of cash inflows. Because the net, i.e., the present value of future cash flows, should be less than the initial investment, we subtract the investment cost outside of the NPV function.

The initial output entered is a negative number, so we perform the addition operation:

=NPV(B7,B3:B6)+B2

The result comes to ₹38.

Both the methods’ outputs are the same.

NPV (Manual) = ₹38

NPV (Excel) = ₹38

NPV Excel Calculation To Compare Two Investments

Mohan has invested money in two projects. ₹1,000 in project A and gained ₹0 in the first year, ₹500 in the second year, ₹1,150 in the third year. The discount rate is 20%. In project B, he invested ₹1,000 and received ₹1,150 in the first year, ₹500 in the second year, and ₹0 in the third year. The discount rate is 20%. Find NPV and compare these two investments.

Project A

Project A: Present value of the payment is ₹1,000, and the discount rate is 20%.

Step 1: In this example, the table shows the cash flow of investment made by Mohan in project A. But, first, we need to find the NPV of the investment.

Column A contains the Description (the starting investment and every year installments).

Column B contains Cash Flow every year.

Row 6 contains the Discount Rate.

Row 7 calculates Net Present Value.

Step 2: Calculate the net present value.

=NPV(B6,B2:B5)

The output comes to ₹10.61.

Project B

Project B: Present value of the payment is ₹1000, and the discount rate is 20%.

Step 1: This table shows Mohan’s cash flow of investments in project B. In this project, the installment return is different, and the discount rate is the same as 20%. Let us find the NPV to differentiate between these two projects.

Step 2: Calculate the net present value.

=NPV(B6,B2:B5)

The output comes to ₹254.63.

Therefore, the investments made by Mohan in two projects, project A and project B, are the same. Also, the discount rate is the same, but the installment return is different. Due to this, both the NPV values have a vast difference.

NPV of Project A = ₹10.61
NPV of Project B = ₹254.63

Common Errors In NPV Excel

Many errors occur when implementing the inbuilt NPV in Excel formula. Here are the different types of errors and ways to overcome them:

Discount Rate – The discount rate is not according to the period. For yearly cash flow, the discount rate should be for every year, and monthly cash flow, the discount rate should be for every month.
Incorrect Rate Format – The discount rate used in the formula should be in the correct format. The percentage “%” sign is a compulsory part; otherwise, the result will be wrong.
Missing Period Cash Flow – The cash flow should be infrequent. If there is no cash flow between any years, one should write the amount ₹0; otherwise, it will generate the wrong result.
Irregular Interval of Time – The cash flow time should be the same format. If the cash flow of payment and return is yearly, it should be the same for all cash flows. One should enter no monthly or quarterly time.

Important Things To Note

Arguments in the formula must be the numerical value.
The interval time of the cash flows should be in sequence.
The investment value should be negative.
There should be one positive cash flow value and one negative cash flow value.
Ensure that the array is in line.
If NPV>0, the project is profitable. If NPV = 0, the project will begin to generate profit. But, if NPV< 0, the project is non-profitable.
NPV in Excel’s latest version can accept 254 values. But in Excel 2003 version, one can enter only 29 values.

Frequently Asked Questions (FAQs)

What is NPV in Excel?

NPV in Excel is the difference between the present cash inflow and outflow value using the discount rate. It is a financial function of Excel that explains the time value of money. NPV can determine whether the project or any investment is profitable or not. The net present value calculates the worth of investment (made for future gains) on today’s money.

How is NPV calculated in Excel?

NPV in Excel formula to calculate the profitability of the investment is as follows:

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

For example, Shamita has invested ₹100 in a fishing company stock. She gets no return in the first two years, and last year she got ₹150 in return. The rate of discount is 10%. Let us find the NPV of her cash flow of the investment.

Column A contains Period
Column B contains Cash Flow
Row 6 calculates NPV.

=NPV(B6,B2:B5) = ₹11.54.

When to use NPV in Excel?

– NPV is used in the capital budgeting and investment planning of the company.
– It is primarily used in financial organizations to calculate the profitability of any project.
– The NPV formula in Excel is used in Discounting Cash Flow (DCF) modeling.

Download Template

This article must be helpful to understand the NPV Function in Excel, with its formula and examples. You can download the template here to use it instantly –

NPV-in-Excel Download

NPV In Excel

What Is NPV In Excel?