How to Calculate NPV for Monthly Cash Flows with a 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

We’ll use a dataset containing period, monthly cash flow, and discount rate to demonstrate how to calculate NPV using two different methods in Excel.

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

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

In this method, we’ll demonstrate 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

Follow these steps:

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

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

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: We used Absolute Cell Reference in the formula so that the formula does not change while using Autofill.

Calculate NPV When Initial Investment Is Made after First Month

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

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

You can see that the formula has been copied 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)

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

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.

Follow these steps:

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

Find NPV When Investment Is Made at Start of First Period

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 the formula has been copied 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. We will use this function to write an NPV formula for monthly cash flows in Excel. We will demonstrate 3 different examples of using the NPV function in different situations.