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.

## Download Practice Workbook

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

## 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**

**Read More:** **How to Calculate Discounted Cash Flow in Excel (with Easy Steps)**

## 2 Simple Ways of Calculating NPV for Monthly Cash Flows with Formula in Excel

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. Use 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**.

#### 1.2. Find NPV When Investment Is Made at Start of 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 Cash Flow in Excel (7 Suitable Examples)**

**Similar Reaidngs**

**How to Calculate Free Cash Flow in Excel (to Firm and Equity)****Calculating Payback Period in Excel with Uneven Cash Flows****How to Create a Personal Cash Flow Statement in Excel****Create Cash Flow Projection for 12 Months in Excel****How to Create a Cash Flow Waterfall Chart in Excel**

### 2. Apply 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 Create Investment Property Cash Flow Calculator 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 Calculate Present Value of Future Cash Flows 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 Annual Cash Flow in Excel (with Easy Steps)**

## 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.

## 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 Create Weekly Cash Flow Statement Format in Excel****Calculate Payback Period with Uneven Cash Flows****How to Calculate Net Cash Flow in Excel (3 Suitable Examples)****Calculate IRR in Excel for Monthly Cash Flow (4 Ways)****How to Create Monthly Cash Flow Statement Format in Excel****Calculate Future Value of Uneven Cash Flows in Excel**