## What Is NPV?

NPV, or Net Present Value, is a fundamental component of financial analysis. It helps determine whether a project will be profitable. The NPV formula is as follows:

**NPV = âˆ‘PV – Initial Investment**

Where:

**PV = Cashflow / (1+r)**

^{t}Where:

**PV**Â represents the present value of cash flows.**r**Â is the discount rate.**t**Â is the cash flow period.

The complete NPV formula becomes:

**NPV = âˆ‘Cashflow / (1+r)**

^{t}– Initial InvestmentWhere:

**n**= Number of Periods

Here, Iâ€™ll use a dataset containing period, monthly cash flow, and discount rate to demonstrate how to calculate NPV using two different methods in Excel.

## Method 1 – Using a Generic Formula to Calculate NPV for Monthly Cash Flows in Excel

In this method, Iâ€™ll show you two examplesâ€”one when the initial investment occurs after the first month and another when the investment starts at the beginning of the first period.

### 1.1. Calculate NPV When Initial Investment Is Made after First Month

Follow these steps:

- Select the cell where you want to calculate the present value (
**PV**). Letâ€™s say we choose Cell**D5**. - In Cell
**D5**, enter the following formula:

`=C5/(1+$C$12/12)^B5`

- Press
**Enter**to get the PV.

**Explanation**

**(1+$C$12/12):**Divide the value in**Cell C12**by**12**(since weâ€™re dealing with monthly cash flow) and**add****1**.**(1+$C$12/12)^B5:Â**Raise this result to the power of the value in**Cell B5**.**C5/(1+$C$12/12)^B5:Â**Finally,**divide**the value in**Cell C5**by the result to obtain the PV.

**Note:**Â Here, I used Absolute Cell Reference in the formula so that the formula does not change while using

**Autofill**.

- Use the
**Fill Handle**to copy the formula down to other cells.

- You can see that I have copied the formula to the other cells.

- To calculate
**NPV**, select the cell where you want the result (e.g.,**Cell C14**). - Enter the formula:

`=SUM(D5:D10)`

- Press
**Enter**to get the NPV.

The **SUM function** adds up the values in the range **D5:D10**.

**Read More: **How to Calculate Present Value in Excel with Different Payments

### 1.2. **Calculate NPV When Investment Is Made at the Start of the First Period**

In this example, weâ€™ll determine the Net Present Value (NPV) in Excel when the initial investment occurs at the beginning of the first period.

Follow these steps:

**Select the PV Cell:**Â Choose the cell where you want to calculate the present value (PV).**Enter the Formula:**Â In the selected cell, enter the following formula:

`=C6/(1+$C$12/12)^B6`

- Press
**Enter**to compute the PV.

**Explanation**

**(1+$C$12/12):****Divides**the value in**Cell C12**by**12**(since weâ€™re dealing with monthly cash flows) and adds**1**.**(1+$C$12/12)^B6:****Raises**the result to the**power of the value**in Cell**B6**.**C6/(1+$C$12/12)^B6:****Yields**the**PV**.

**Copy the Formula:**Â Drag the**Fill Handle**down to copy the formula to other cells. This will calculate PV for each period.

- You can see that I have copied the formula to the other cells to compute all the
**PV**.

**Calculate NPV**- Select the cell where you want the
**NPV**. - Enter the following formula:

- Select the cell where you want the

`=SUM(D6:D10)+C5`

- Press
**Enter**to obtain the**NPV**.

**SUM**function adds up the values in the cell range

**D6:D10**, and then we add the value in cell

**C5**to get the NPV.

**Read More: **How to Calculate Present Value of Lump Sum in Excel

## Method 2 – Applying NPV Function for Monthly Cash Flows in Excel

**The NPV function** in Excel returns the net present value. Here, I will use this function to write an NPV formula for monthly cash flows in Excel. I will show you 3 different examples of using the NPV function in different situations.

### 2.1. **Insert NPV Function When Initial Investment Is Made after First MonthÂ **

**Steps**

- Select the cell where you want to calculate NPV (e.g.,
**Cell C14**). - Enter the following formula:

`=NPV(C12/12,C5:C10)`

- Press
**Enter**to compute the NPV.

In this formula, we use the monthly rate (**C12/12**) and the cash flow values in the range** C5:C10**.

**Read More: **How to Calculate Present Value of Uneven Cash Flows in Excel

### 2.2. **Apply NPV Function Leaving Initial Cost Out of Range**

**Steps**

- Select the NPV cell.
- Enter this formula:

`=NPV(C12/12,C6:C10)+C5`

- Press
**Enter**to get the NPV.

**Explanation**

Here, we exclude the initial cost (**C5**) from the cash flow range and add it back to the **NPV** result.

**Read More: **How to Apply Present Value of Annuity Formula in Excel

### 2.3. **Utilize NPV Function Including Initial Cost**

**Steps**

- Select the NPV cell (e.g.,
**Cell C14**). - Enter the following formula:

`=NPV(C12/12,C5:C10)*(1+C12/12)`

- Press
**Enter**to obtain the NPV.

**Explanation**

This formula includes the **initial cost** and **multiplies** the **NPV** by the factor (**1 + C12/12**).

**Read More: **How to Calculate Present Value of Future Cash Flows in Excel

## Practice Section

Feel free to practice using the provided sheet.

**Download Practice Workbook**

You can download the practice workbook from here:

## Related Articles

- How to Apply Future Value of an Annuity Formula in Excel
- How to Calculate Future Value of Uneven Cash Flows in Excel
- How to Calculate Future Value with Inflation in Excel
- How to Calculate Future Value of Growing Annuity in Excel
- How to Calculate Future Value in Excel with Different Payments

**<< Go Back to ****Time Value Of Money In Excel ****|** **Excel for Finance**** | ****Learn Excel**