How to calculate NPV in Excel

How to calculate NPV in Excel Picture 1

In Excel's financial functions, you've probably seen NPV, but have you ever asked what NPV is and what help users have?

Today let's learn Dexterity Software to learn offline!

What is NPV?

First, what is NPV? What is the benefit of NPV?

NPV is the Net Present Value (NPV) , that is, the net present value of an investment. Based on the NPV, the investor calculates the difference between the present value of cash inflows and the current value of cash outflows. This method uses monetary value over time to deduct the future cash flows to return the present value of a basic investment item, based on the expected discount rate.

NPV formula

(NPV = sumlimits_ {t = 1} ^ n {frac {{{C_t}}} {{{{(1 + r)} ^ t}}}})

Inside:

1. ({C_t}): Net cash flow at time t (Cashflow)
2. ({C_0}): Initial capital expenditure of an investment / project
3. r: Discount rate
4. n: Total duration of project implementation
5. t: Time to calculate cash flow

You can simply understand that if the NPV value is greater than C0, that project is feasible.

To better understand NPV, consider the following example:

You have the capital of VND 500,000,000 to buy a car to run a taxi. The net profit earned a year is VND 120,000,000. Expected use time is 7 years. If you send to the bank savings interest rate of 15% / year, should you save money or buy an investment car?

To find this decision, calculate your NPV after 7 years using the following formula:

Step 1: You calculate the value (frac {{{C_t}}} {{{{(1 + r)} ^ t}}}) of each year.

How to calculate NPV in Excel Picture 2

Step 2: You calculate the sum (frac {{{C_t}}} {{{{(1 + r)} ^ t}}}) of 7 years. The net present value of an investment of VND 700,000,000 will be purchased if buying a car.

How to calculate NPV in Excel Picture 3

Step 3: So after 7 years, the net present value of the investment is only 584,210,258 => Do not implement this project. Instead, you should find another plan or save money.

How to use the NPV function in Excel?

To simplify the calculation of NPV, Excel developed the NPV function with the following function syntax:

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

Inside:

1. rate: Discount rate (for example, 10%).
2. value1, 2, 3: Cash flow every year 1, 2, 3 . (according to the example is 120 million per year).

You enter the formula and get the same result as above:

How to calculate NPV in Excel Picture 4

Note: The NPV function results automatically returns the Currency format , to change the format of the cell you select the result cell, press the shortcut Ctrl + 1 to open the Format Cells dialog box .

Adjust the Number format or the format you want.

How to calculate NPV in Excel Picture 5

Very simple, right? Instead of calculating each net present value of the amount earned each year and then adding up, you can always use the NPV function available in Excel software.

Good luck!