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.

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

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.

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

Now, the SUM function sums the cell range D6:D10. And, then add the value in cell C5 with the result.

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.

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.

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.

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.

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.

Calculating NPV.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!

Calculate NPV for Monthly Cash Flows with Formula in Excel

What Is NPV?

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

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

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

2. Applying NPV Function for Monthly Cash Flows in Excel

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

2.2. Apply NPV Function Leaving Initial Cost Out of Range

2.3. Utilize NPV Function Including Initial Cost

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply