Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Calculate NPV for Monthly Cash Flows with Formula in Excel

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 Investment

Where,
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.

Dataset for Calculating NPV for Monthly Cash Flows with Formula in Excel


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

Use Generic Formula to Calculate NPV for Monthly Cash Flows in Excel

  • After that, press Enter to get the PV.

Calculate NPV When Initial Investment Is Made after First Month

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

Dragging Fill Handle to Copy NPV Formula in Excel for Monthly Cash Flow

  • 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)

Using SUM function to Calculate NPV in Excel for Monthly Cash Flows in Excel

  • Next, press Enter and you will get the NPV.

Here, in 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

Find NPV When Investment Is Made at Start of First Period

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

Now, the 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


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)

Apply NPV Function for Monthly Cash Flows in Excel

  • After that, press Enter to get the NPV.

Here, in the 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

Apply NPV Function Leaving Initial Cost Out of Range

  • 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)

Utilize NPV Function Including Initial Cost

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

Practice Sheet for NPV Formula for Monthly Cash Flows


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

Mashhura Jahan

Mashhura Jahan

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo