If you are looking for a way to calculate NPV for monthly cash flows then you have come to the right place. The focus of this article is to explain how to calculate NPV with a formula for monthly cash flows in Excel.

## What Is NPV?

NPV or Net Present Value is a core component of financial analysis. It shows you whether a project is going to be profitable for you or not. The formula for NPV is:

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

Here,

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

^{t}Where,

r = Discount Rate

t = Cashflow PeriodÂ

So, the formula for NPV becomes.

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

^{t}– Initial InvestmentWhere,

n = Number of Periods

Here, I have taken the following dataset to explain this article. It contains the Period, monthly Cashflow, and Discount Rate. I will show you how you can calculate the NPV with a formula for monthly cash flows in Excel in 2 different ways.

## 1. Using Generic Formula to Calculate NPV for Monthly Cash Flows in Excel

In this first method, I will show you how you can use the generic formula to calculate NPV in Excel. Here, I will show you 2 different examples. One is for when the initial investment is made after the first month and another is for when the investment is made at the start of the first period.

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

Here, I will explain how to calculate NPV in Excel using the generic formula when the initial investment is made after the first month. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want the PV. Here, I selected
**Cell D5**. - Secondly, in
**Cell D5**write the following formula.

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

- After that, press
**Enter**to get the PV.

**🔎** **How Does the Formula Work?**

**(1+$C$12/12):**Here, the value in**Cell C12**is divided by**12**as I am calculating PV for the monthly cash flow. And, then the result is**summed**with**1**.**(1+$C$12/12)^B5:**Now, I have raised the result**to the power**of the value in**Cell B5**.**C5/(1+$C$12/12)^B5:**Finally, I**divided**the value in cell**C5**by the result and the formula returns the PV.

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

**Autofill**.

- Next, drag the
**Fill Handle**down to copy the formula.

- Finally, you can see that I have copied the formula to the other cells.

- Further, select the cell where you want NPV. Here, I selected
**Cell C14**. - Next, in
**Cell C14**write the following formula.

`=SUM(D5:D10)`

- Next, press
**Enter**and you will get the NPV.

**the SUM function**, I selected cell range

**D5:D10**as numbers. The formula returns the summation of the cell range

**D5:D10**.

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

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

In this example, I will calculate NPV in Excel with a formula for when the initial investment is made at the start of the first period. Let me show you how you can do it.

**Steps:**

- In the beginning, select the cell where you want the PV.
- After that, write the following formula in the selected cell.

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

- Then, press
**Enter**and you will get the PV.

**🔎** **How Does the Formula Work?**

**(1+$C$12/12):**Here, the value in**Cell C12**is**divided**by**12**as I am calculating PV for the monthly cash flow. And, then I summedÂ**1**with the result.**(1+$C$12/12)^B6:**Now, I have raised the result**to the power**of the value in**Cell B6**.**C6/(1+$C$12/12)^B6:**Finally, I**divided**the value in cell**C6**by the result and the formula returns the PV.

- Next, drag the
**Fill Handle**down to copy the formula to the other cells.

- Here, you can see that I have copied the formula to the other cells and got all the
**PV**.

- Afterward, select the cell where you want the NPV.
- Then, write the following formula in that selected cell.

`=SUM(D6:D10)+C5`

- Finally, press
**Enter**to get the NPV.

**SUM**function sums the cell range

**D6:D10**. And, then add the value in cell

**C5**with the result.

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

## 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. Let’s explore.

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

In this example, I will show you how you can use the NPV function when the initial investment is made after the first month. Letâ€™s see the steps.

**Steps:**

- First, select the cell where you want the NPV. Here, I selected
**Cell C14**. - Then, in
**Cell C14**write the following formula.

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

- After that, press
**Enter**to get the NPV.

**NPV**function, I selected

**C12/12**as the rate and cell range

**C5:C10**as values. I divided the value in

**C12**by

**12**because I am calculating NPV for the monthly cash flow. The formula will return the NPV for monthly cash flow.

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

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

Now, I will show you how you can calculate the NPV for the monthly cash flow if the initial investment is made at the start of the first month. Here, I will use the NPV function. In the NPV function, I will leave the initial cost out of the value range. And later I will add it to the result to get the NPV. Letâ€™s see how to do it.

**Steps:**

- In the beginning. Select the cell where you want the NPV.
- Next, write the following formula in the selected cell.

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

- In the end, press
**Enter**and you will get the NPV.

**🔎** **How Does the Formula Work?**

**NPV(C12/12,C6:C10):**Here, in the**NPV**function, I selected**C12/12**as the rate and cell range**C5:C10**as**values**. The formula will return the NPV for monthly cash flow.**NPV(C12/12,C6:C10)+C5:**Now, the formula returns the**summation**of value in cell**C5**and the result from the**NPV**function.

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

### 2.3. Utilize NPV Function Including Initial Cost

Here, I will use the **NPV** function to calculate monthly NPV in Excel when the initial investment is made at the start of the first month. In this case, I will include the initial cost in the function. Letâ€™s see how you can do it.

**Steps:**

- Firstly, select the cell where you want the NPV. Here, I selected
**Cell C14**. - Secondly, in
**Cell C14**write the following formula.

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

- Finally, press
**Enter**and you get the NPV for monthly cash flow.

**🔎** **How Does the Formula Work?**

**NPV(C12/12,C5:C10):**Here, in the**NPV**function, I selected**C12/12**as the rate and cell range**C5:C10**as values. The formula returns the NPV for monthly cash flows.**(1+C12/12):**Now, I divided the value in**C5**by**12**and then**summed 1**with the result.**NPV(C12/12,C5:C10)*(1+C12/12):**Finally, I**multiplied**these**2**results.

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

## Practice Section

Here, I have provided a practice sheet for you to practice how to calculate NPV with a formula for monthly cash flows in Excel.

**Download Practice Workbook**

Here, I have provided the practice workbook. You can download it from here.

## Conclusion

So, you have reached the end of my article. Here, I tried to explain how you can calculate NPV with a formula for monthly cash flows in Excel in 2 different ways. I hope this article was helpful to you. For more articles, stay connected with **ExcelDemy**. Lastly, if you have any questions then let me know in the comment section below.

## 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 Make a Time Value of Money Calculator 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**