This quick and effortless Excel tutorial shows you how to calculate NPV in Excel with real-world data and easy-to-understand images of the steps being performed.

Microsoft Excel is the leading app to perform financial data analysis. It offers various built-in functions to quickly solve many financial problems. One such function is to calculate the net present value of a cash outflow plan with a discount rate, known as NPV.

If you’re evaluating a potential investment opportunity, assessing the profitability of a business project, or planning for long-term financial success, you must learn how to find out the NPV.

Find below various methods to choose from so you can accurately calculate NPV manually or automatically, and programmatically. Let’s get started!

What Is NPV?

NPV is the abbreviation of the financial metric Net Present Value. You can use this financial function to evaluate the profitability of a business project or investment plan.

NPV calculates the present value of all the cash flows you can expect from the project or investment. These include both incoming and outgoing cash flows.

The NPV function discounts back to the present using a discount rate, internal rate of return (IRR), or interest rate. The discount rate is always an annual rate.

The mathematical NPV formula is as mentioned below:

Net Present Value (NPV) = Sum of all cash flows discounted to present value - Initial investment

Where:

• The sum of all cash flows discounted to the present value is calculated by dividing each cash flow by (1 + discount rate) raised to the power of the time period, then summing these present values.
• The initial investment is the initial cash outlay required to start the project or investment. This amount is subtracted from the sum of discounted cash flows to determine the net present value.

You could find the above-mentioned mathematical formula for NPV to be complicated.

However, the built-in Excel function for NPV is ridiculously simple. You’ll only need to supply the cell references for the arguments in the formula syntax. You don’t need to go deep into the technicalities of the underlying formula.

Calculate NPV in Excel Using the NPV Function

To calculate NPV successfully, organize the investment or business project’s cash flow scheme and interest rate data as shown in the above screenshot.

• Years: The column represents the interval of cash flows.
• Cash Flows: You must enter all the inward and outward cash flows here. Negative cash flows (values in parentheses) mean you’re paying a premium each year for 10 years. The positive cash flow indicates that you get the lump sum return in the 11th year.
• Discount Rate: This is the rate at which your investment grows. It could be also the annual interest rate.
• NPV: Below this column header you’ll be calculating the net present value.

Now, in the destination cell, which is E2 in the current exercise, enter the following formula:

=NPV(D2,B2:B12)

Hit Enter to get the NPV value in E2.

You can now customize the cell references of the formula arguments to match your own worksheet dataset.

When calculating NPV using this method, keep the following tips in mind:

• Enter the initial investment and subsequent cash flows into consecutive cells in Excel. If it’s a SIP where you pay each year or month, list the cash outlays first and finally mention the lump sum return.
• Ensure that all cash flows are in the same interval (e.g., yearly, quarterly) to accurately calculate NPV.
• Define the discount rate. It should typically represent the opportunity cost of capital or the desired rate of return.
• A positive NPV indicates that the investment is expected to generate value and is potentially worth pursuing. Contrarily, a negative NPV suggests the opposite.

Calculate NPV in Excel Using the XNPV Function

If you need to consider the time your money is invested into the business project or investment plan when calculating the net present value, you use the XNPV function.

Therefore, whether the cash flows are regular or irregular, doesn’t matter. You just include the intervals of the cash outlays and returns into this formula to get an accurate value.

Also, for those investments where cash flows inwards or outwards take place at a regular interval and you’ve got the dates of those transactions, you can use XNPV instead of NPV for time-adjusted return analysis.

Moreover, If you need to compare the NPV of multiple projects with cash flows occurring at different time periods, you must use XNPV.

The data organization for XNPV calculation is similar to that of the NPV formula. You just need to emphasize more on the interval of the transactions. It’s better if you can capture the intervals accurately in an MM/DD/YYYY format as shown in the above example.

In the destination cell, like E2 in this tutorial, enter the following formula and hit Enter to get XNPV:

=XNPV(D2,B2:B12,A2:A12) 

The XNPV of this investment plan shows a negative value. Therefore, it’s not a good investment option.

Don’t forget to change the cell ranges of the arguments according to your own dataset.

Calculate NPV in Excel Using the PV Function

In this method, you use

=B2/(1+$D$2)^A2

Calculate NPV in Excel Using the Manual PV Formula

Using the PV function of Excel, you can calculate the present value of each cash flow event of an investment or business project plan. Then, you can apply the summation formula to get the NPV.

To calculate NPV using PV, let’s consider a lump sum investment plan. Here, you invest $100,000 in the 0th year. Then, you start receiving payments or returns of $10,000 each year for the next 10 years. The IRR of the investment plan is 10%.

You can see how to organize this data above with cash outlays, inward cash flows, IRR, and present values in the screenshot given above.

In the first cell below the PV column, enter the following formula and hit Enter:

=PV($E$2,A2,0,B2,1) 

Find below the explanation of this PV formula along with its arguments and references:

• Rate: The discount, interest, or IRR of the investment. It’s in cell E2 and should be entered as an absolute cell reference.
• Nper: The period of the payments, like yearly or monthly intervals of cash flows. In this exercise, it’s in column A.
• Pmt: This is the payment you make each year or month. Leave this blank if you’re calculating PV for investment plans. It’ll be required if you’re calculating PV for monthly or yearly payments towards EMIs or mortgages.
• Future value (Fv): Fv is the cash flow, both inwards and outwards. The values of Fv are in column B in the current dataset.

Excel should calculate the PV for the first cash flow, which is your initial lump sum investment.

Now, drag the fill handle down column C until C12 to apply the same PV formula to the rest of the cash flows.

In cell C13, where you want the NPV, enter the following SUM formula and hit Enter:

=SUM(C2:C12)

This is the NPV you have been looking for. It’s 0 because you’ve used IRR to calculate NPV. Internal rate of return or IRR is the discount rate that makes the NPV of the investment plan 0.

Calculate NPV in Excel Using Excel VBA

If you wish to fully automate the net present value calculation process, you can use Excel VBA. With this, you no longer need to remember the arguments and cell references for NPV calculation, as you need with the NPV function.

Also, you can use advanced code in a VBA script to show input boxes and instructions. Finally, you can create a wizard-like experience of calculating NPV that’ll guide you and other users throughout the process.

Find below a VBA script that enables you to calculate time-independent NPV with minimal manual intervention:

 Sub CalculateNPV()

    ' Prompt the user to select the discount rate cell
    Dim DiscountRateRange As Range
    Set DiscountRateRange = Application.InputBox("Select the cell with the discount rate", Type:=8)

    ' Prompt the user to select the cash flow cells
    Dim CashFlowsRange As Range
    Set CashFlowsRange = Application.InputBox("Select the cells with the cash flows", Type:=8)

    ' Prompt the user to select the destination cell for the NPV result
    Dim DestinationCell As Range
    Set DestinationCell = Application.InputBox("Select the cell where you want the NPV result", Type:=8)

    ' Calculate NPV
    Dim NPV As Double
    NPV = Application.WorksheetFunction.NPV(DiscountRateRange.Value, CashFlowsRange.Value)

    ' Write the NPV result to the destination cell
    DestinationCell.Value = NPV

End Sub

Now, all you need to do is create a VBA macro using the above script. If you don’t know how simply follow the steps mentioned in the following Excel tutorial:

📒 Read More: How To Use The VBA Code You Find Online

If the macro is ready, press Alt + F8 to bring up the Macro dialog. Select the CalculateNPV macro and hit Run.

The script shall show a prompt to select the discount rate.

Then, another input box shall ask you to choose the cell range containing the cash flows.

Finally, you need to choose the destination cell.

Excel shall calculate the NPV instantly.

If the cashflows are irregular or you’d like to consider the dates of the cashflows, you can use the following script that calculates XNPV. Just like the previous script, this one will also offer visual guidance throughout the process.

Sub CalculateXNPV()

    ' Prompt the user to select the discount rate cell
    Dim DiscountRateRange As Range
    Set DiscountRateRange = Application.InputBox("Select the cell with the discount rate", Type:=8)

    ' Prompt the user to select the cash flow cells
    Dim CashFlowsRange As Range
    Set CashFlowsRange = Application.InputBox("Select the cells with the cash flows", Type:=8)

    ' Prompt the user to select the date cells
    Dim DatesRange As Range
    Set DatesRange = Application.InputBox("Select the cells with the dates", Type:=8)

    ' Prompt the user to select the destination cell for the XNPV result
    Dim DestinationCell As Range
    Set DestinationCell = Application.InputBox("Select the cell where you want the XNPV result", Type:=8)

    ' Calculate XNPV and write the result to the destination cell
    DestinationCell.Formula = "=XNPV(" & DiscountRateRange.Address & ", " & CashFlowsRange.Address & ", " & DatesRange.Address & ")"

End Sub

Find above the screenshot showing XNPV calculated using the given script.

Conclusions

NPV calculation allows you to make informed decisions when choosing investment plans, budgeting capital expenditures in a business, and allocating resources smartly.

Use any of the above methods to calculate NPV considering your Excel expertise level. I’d suggest you practice the VBA-based method which is easy to use. Not to mention, you don’t need to remember any function arguments as well.

Comment below if the article helped you learn NPV calculation in Excel!