How to Calculate NPV

Updated on 19/07/2024604 Views

Table of Content

Are you willing to find out whether your previous or upcoming investments in your business are fruitful or not? Here is the way out. Know the current state of profit or loss in your business with just a click in excel. Simply calculate the NPV (Net Present Value) to analyze the rate of profit of a business project investment.

So in a nutshell, NPV is actually the contrast between the present value of cash inflows and outflows over a certain time period. So, if you learn how to calculate npv in excel it can thoroughly help you to determine if an investment, project, or business will be profitable in the long run. It is mostly evaluated for accounting purposes and investment banking.

If you are willing to pursue business analysis as your career, having knowledge about Excel skills are highly essential.

Also I cannot assert more how helpful it is to learn calculating NPV in excel, I use it extensively for my analytical works. If you are wondering how to calculate npv in excel, have a look at the methods and a lot more about calculating NPV with minute details that I have discussed here. I have shared all I know in this guide below.

How to Calculate NPV in Excel

NPV is the present value or actual value of future flows of funds. The present value of future cash flows is the count worth of it and to know the count value you must use a discount rate.

I have already made a table with the period, cash flows and discount rate that I have mentioned here.
Let's suppose we have 10 years of periods, the initial investment at the period of time 0 is of 200,000 rupees, and suppose we have cash flow of 40,000 for each year for 10 years.

For example, the discount is of 10%
Now for calculating the NPV, we need to add the formula for it.
I'm adding the formula in the NPV column.
Firstly add is equal to sign ‘=’
Then you have to write ‘NPV’

Then start brackets and for mentioning the discount rate which is of 10%, simply click on the column of 10%, it will automatically write the cell name and number accordingly. Which is D1 here.

Then add a comma, then you need to add the values of the cash flows over the years.
So I’m going to select the first column of cash flows, the values are the same over the consecutive years so I'm just simply dragging it till the 10th column, and selecting all of them.
Now you will see it showing ‘B3: B12’ in the formula here.

Now close the bracket. And add the initial investment which is 200,000 rupees, just by clicking on the cell. Here it is B2.

So the full NPV formula is = NPV (discount rate, series of cash flow) + Initial Investment
Now how do I calculate NPV, just hit enter to get your NPV value.

Now you have an NPV of 45,782.68 here. So as you can see our NPV is positive and that means the actual value of all income is higher than the actual value of the expenditures. So we can say that the investment is desirable because it adds more value than the best alternatives that are available. Hence it is worthy of approval in any project.

Learn How to Calculate Present Value of Annuity in Excel

Annuity is precisely a sequence of cash inflows and outflows at equal intervals of time. You can calculate annuities to make a savings plan. Find out monthly loan payments or determine the value of an asset that will provide a fixed yearly income in the future.

Let's dig into an example right away!

Suppose your initial investment is $500 and the constant cash flow through 7 years is also $500 with 4% interest rate.

Now lets see how to calculate the present value of annuity in excel. Here are three ways that I have discussed.

Using Numerical Formula

The numerical formula for calculating present value of annuity is PV = ((constant cash flow*(1-(1/(1+ interest rate)^time periods)))/ interest rate)*(1+ interest rate)
So now if I put the data here, it will look like this

PV = ((500*(1-(1/(1+0.04)^7)))/0.04)*(1+0.04)

And hit enter to get the result.

Using Excel Formula (Type one)

The excel formula for calculating present value of annuity is = PV (interest rate, time periods, payment,,1)
Now if we put the data here it will look like this

= PV (0.04,7,500,,1)

‘Payment’ here is the constant cash flow.

Since there is no future value here that's why you are putting another comma, here excel will ask you whether you are getting the initial investment at the beginning or at the end of the time period. I'm selecting the beginning here.

And then you need to type 1 otherwise the default setting of excel will treat everything as an ordinary annuity.

Now close the bracket and hit enter to get the result. The value will be the same as you can see in the image above.

Using Excel Formula (Type Two)

There is another excel formula for calculating present value of annuity which is = NPV (Interest rate, series of cash flow) + Initial Investment
So now if I put the data here, it will look like this

= NPV (0.04,A3:A8) + A2

Now hit enter to get the present value of annuity.
Now observe that all the values of the results are the same, it's $3,121.07.

Wrapping Up

Learning how to calculate npv is undeniably an invaluable knowledge for business analysis and for enhancing your efficiency in understating project investments. I hope this tutorial of how to calculate net present value has equipped you with the knowledge you need.

Now if you are looking to advance your career further, you may take a look at the bonafide certificate courses that upGrad provides. It is affiliated to prestigious universities and with curricula curated by industry experts. Moreover, these courses give you the added expertise, besides of a better chance at a better job position.

Frequently Asked Questions

What is the formula for calculating NPV?

The formula for calculating NPV = NPV (discount rate, series of cash flow) + Initial Investment

What is the formula for NPV manually?

The formula for NPV manually is = NPV (discount rate, series of cash flow)

How can I calculate NPV in Excel?

In order to learn how to calculate npv have a look at this guide where I have mentioned the steps in detail.

What is an example of NPV method?

I have discussed the method of how to calculate present value as well as how to calculate net present value or NPV through several examples above in this blog.

What is NPV and IRR?

NPV is the difference between the present value of cash inflows and outflows through a time period. IRR (Internal Rate of Return) is the rate at which the NPV of the cash inflows and the net present value of cash outflows are equal.

What is NPV discount rate?

A discount rate is used to calculate the NPV of a business or project or investment as a part of a discounted cash flow. This rate is called the NPV discount rate.

Is NPV easy to calculate?

Yes! It is very easy to calculate NPV.

How do you calculate discount rate for NPV?

If you have the numerical data for NPV, the time period and the projected cash flow of the year, you can easily derive the discount rate by using the formula NPV = F/ (1 + r)^n