NPV Formula in Excel (Table of Contents)
Guide to NPV Formula in Excel
In today’s article, we are going to discuss the formula NPV in excel. NPV is one of the important topics in capital budgeting. In excel we have a built-in excel function called NPV which calculates the Net Present Value of an investment. The syntax of the formula is:
- Rate: This is nothing but the discount rate or return rate expected from the investment.
- Value 1: This is a series of cash flows whether it is positive or negative.
What is NPV?
NPV – Net Present Value is the value of the current investment and future cash flows as on today. In order to determine the NPV of investment and future cash flows, we need to consider the discount rate or ROI expected from the investment.
Example 1: If you are making an investment of 90000 today and it is giving you 25000 for the next five years annually then your total ROI is 125000 (25000*5) at 10% discount rate. NPV is 94,970 i.e. 4970 (94970 – 90000) is the NPV which is coming in from this investment.
Example 2: If you are making an investment of 100000 today and it is giving you 25000 for the next five years annually then your total ROI is 125000 (25000*5) at 10% discount rate. NPV is 94,970 i.e. -5230 (94970 – 100000) is the NPV which resulted in loss of 5230.
If the investment returns positive NPV then you can recommend to take up the project, if the investment returns negative NPV then you can decline the project.
How to Use NPV Formula in Excel? (with Examples)
Below are some examples of NPV Formula in Excel.
NPV Formula Excel Example #1
Assume you are making an investment of 25000 for one of the projects. In the estimation of cash inflow, you are expecting 7000 for 1st year, 7500 for a 2nd year, and 7200 for the next 3 years.
Using this information you are going to analyze whether it is worth an investment or not by considering 15% as the discount rate.
Step 1: Open NPV function in D5 cell.
Step 2: Rate is the discount rate, select cell B10.
Step 3: Now select series of cash flows from B4 to B8.
So NPV returned 24188 as the result.
Current investment is 25000 and NPV value is 24188 i.e. -812 (24188 – 25000) which is a loss as on today. So it is not a good sign to make an investment.
Bottom line formula of NPV is
NPV = Present Value – Cost (Investment)
NPV Formula Excel Example #2
Mr. ABC is about to take up a project which is costing him Rs. 50 Lakh. If he takes up this project he would expect 14 lakh for every year for the next 5 years.
Now he wants to analyze whether the investment of 50 lakh is worth to earn 70 lakh i.e. 14 Lakh * 5 = 70 Lakh in 5 years’ time. He is estimating the discounted rate at 10%.
When you look at the overall investment and return he will earn 20 Lakhs as profit for 5 years but is it worth to invest 50 Lakh, which is the question here.
Apply the NPV function to see the NPV of an investment.
Step 1: Open NPV function select the discount rate i.e. B10 cell.
Step 2: Now select cash flow range.
Step 3: In order to get the NPV value, we need to deduct the initial investment made by Mr. ABC. Since we have initial investment value in negative number we need to add the cell B2 to the formula.
Step 4: Hit enter key we will have NPV value.
NPV value is 307101 which is positive as on today, so Mr. ABC can take up this project which is yielding him 20 lakhs as the profit for 5 years. If the NPV value (Present Value – Cost) is positive then it is highly recommendable to take up the project.
Things to Remember
- NPV considers discount factor because money as on today will not be the same tomorrow.
- Cash flows not necessarily positives it could also be negative values as well.
- If the NPV is zero then you are at the breakeven point.
- NPV directly depends on the discount factor.
This has been a guide to NPV Formula in Excel. Here we discuss how to use NPV formula in Excel with some examples and downloadable excel template. You may learn more about excel from the following articles –