Mashhura Jahan
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:
Here,
Where,
r = Discount Rate
t = Cashflow Period
So, the formula for NPV becomes.
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.
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.
- 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.
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.
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.
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
