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

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:

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

The complete NPV formula becomes:

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.

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:

Cash Flows in Excel" width="465" height="434" />

Explanation

• (1+$C$12/12): Divide the value in Cell C12 by 12 (since we’re dealing with monthly cash flow) and add1.
• (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.

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

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

Cash Flows in Excel" width="467" height="439" />

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:

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
  

  

  

  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.

  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)
  

  Cash Flows in Excel" width="437" height="437" />

  

  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
  

  

  

  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)
  

  

  

  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.

  

  Download Practice Workbook

  You can download the practice workbook from here:

  Related Articles

  • How to Apply Future Value of an Annuity Formula in Excel
  • How to Calculate Future Value of Uneven Cash Flows in Excel
  • How to Calculate Future Value with Inflation in Excel
  • How to Calculate Future Value of Growing Annuity in Excel
  • How to Calculate Future Value in Excel with Different Payments
  Save Saved Removed 0 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

  Leave a reply Cancel reply

  Recent Posts
  • 5 Excel Formulas Every Data Analyst Should Know
  • 5 Essential Excel Functions for Data Analysis
  • Excel Power Pivot Measures – 5 Examples
  • Power Pivot Formulas (2 Suitable Examples)
  • Excel Data for Analysis (Free Download 11 Suitable Datasets)
  • Excel Sample Data (Free Download 13 Sample Datasets)

  Posts from: Time Value of Money in Excel

  • How to Calculate the Present Value with Different Payments in Excel – 5 Examples
  • How to Calculate Future Value in Excel with Different Payments: 5 Ideal Methods
  • How to Calculate Present Value of Future Cash Flows: 4 Methods
  • How to Apply Present Value of Annuity Formula in Excel
  • How to Calculate Present Value of Lump Sum in Excel (3 Ways)
  • How to Apply Future Value of an Annuity Formula in Excel
  • How to Calculate Present Value of Uneven Cash Flows in Excel: 3 Methods
  • How to Calculate Future Value of Uneven Cash Flows in Excel
  • How to Calculate Future Value with Inflation in Excel
  About ExcelDemy.com

  ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.