# How to Calculate NPV for Monthly Cash Flows with Formula in Excel (2 Methods)

## What Is NPV?

NPV, or Net Present Value, is a fundamental component of financial analysis. It helps determine whether a project will be profitable. The NPV formula is as follows:

NPV = âˆ‘PV – Initial Investment

Where:

PV = Cashflow / (1+r)t

Where:

• PVÂ represents the present value of cash flows.
• rÂ is the discount rate.
• tÂ is the cash flow period.

The complete NPV formula becomes:

NPV = âˆ‘Cashflow / (1+r)t – Initial Investment

Where:

• n = Number of Periods

Here, Iâ€™ll use a dataset containing period, monthly cash flow, and discount rate to demonstrate how to calculate NPV using two different methods in Excel.

## Method 1 – Using a Generic Formula to Calculate NPV for Monthly Cash Flows in Excel

In this method, Iâ€™ll show you two examplesâ€”one when the initial investment occurs after the first month and another when the investment starts at the beginning of the first period.

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

• Select the cell where you want to calculate the present value (PV). Letâ€™s say we choose Cell D5.
• In Cell D5, enter the following formula:
`=C5/(1+\$C\$12/12)^B5`

• Press Enter to get the PV.

Explanation

• (1+\$C\$12/12): Divide the value in Cell C12 by 12 (since weâ€™re dealing with monthly cash flow) and add 1.
• (1+\$C\$12/12)^B5:Â Raise this result to the power of the value in Cell B5.
• C5/(1+\$C\$12/12)^B5:Â Finally, divide the value in Cell C5 by the result to obtain the PV.
Note:Â Here, I used Absolute Cell Reference in the formula so that the formula does not change while using Autofill.

• Use the Fill Handle to copy the formula down to other cells.

• You can see that I have copied the formula to the other cells.

• To calculate NPV, select the cell where you want the result (e.g., Cell C14).
• Enter the formula:
`=SUM(D5:D10)`

• Press Enter to get the NPV.

The SUM function adds up the values in the range D5:D10.

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

In this example, weâ€™ll determine the Net Present Value (NPV) in Excel when the initial investment occurs at the beginning of the first period.

• Select the PV Cell:Â Choose the cell where you want to calculate the present value (PV).
• Enter the Formula:Â In the selected cell, enter the following formula:
`=C6/(1+\$C\$12/12)^B6`

• Press Enter to compute the PV.

Explanation

• (1+\$C\$12/12): Divides the value in Cell C12 by 12 (since weâ€™re dealing with monthly cash flows) and adds 1.
• (1+\$C\$12/12)^B6: Raises the result to the power of the value in Cell B6.
• C6/(1+\$C\$12/12)^B6: Yields the PV.
• Copy the Formula:Â Drag the Fill Handle down to copy the formula to other cells. This will calculate PV for each period.

• You can see that I have copied the formula to the other cells to compute all the PV.

• Calculate NPV
• Select the cell where you want the NPV.
• Enter the following formula:
`=SUM(D6:D10)+C5`

• Press Enter to obtain the NPV.

The SUM function adds up the values in the cell range D6:D10, and then we add the value in cell C5 to get the NPV.

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

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

Steps

• Select the cell where you want to calculate NPV (e.g., Cell C14).
• Enter the following formula:
`=NPV(C12/12,C5:C10)`

• Press Enter to compute the NPV.

In this formula, we use the monthly rate (C12/12) and the cash flow values in the range C5:C10.

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

Steps

• Select the NPV cell.
• Enter this formula:
`=NPV(C12/12,C6:C10)+C5`

• Press Enter to get the NPV.

Explanation

Here, we exclude the initial cost (C5) from the cash flow range and add it back to the NPV result.

### 2.3. Utilize NPV Function Including Initial Cost

Steps

• Select the NPV cell (e.g., Cell C14).
• Enter the following formula:
`=NPV(C12/12,C5:C10)*(1+C12/12)`

• Press Enter to obtain the NPV.

Explanation

This formula includes the initial cost and multiplies the NPV by the factor (1 + C12/12).

## Practice Section

Feel free to practice using the provided sheet.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF