# Net Present Value - NPV Calculator

*by Jon Wittwer*10/7/09

Companies use the **Net Present Value (NPV)** calculation to help decide whether an investment will add value in the long run, to compare different investment options, and to decide whether to introduce a new product. In addition to explaining how to **calculate NPV and IRR**, you can download a **Free Excel NPV Calculator** to help you see how to set up your own financial analysis spreadsheet.

Unlike a break-even analysis which is based on a Net Income of 0, the NPV includes a **discount rate**, or the rate or return that could be earned on an alternative investment in the financial market for example. If the **NPV calculation is > 0**, then the new product line (or whatever it is you are analyzing) may be worth pursuing.

The **Internal Rate of Return** or **IRR** is another useful metric for analyzing an investment. The IRR function in Excel uses the same series of cash flows as the NPV, but it must have at least one negative (usually at t=0) and one positive value and it requires an initial guess at the rate because the calculation is iterative. The NPV Calculator spreadsheet includes the IRR calculation as well.

## NPV Calculator with IRR, XNPV, and XIRR

for Excel and OpenOffice**Excel 2007 or later**

#### Other Versions

#### Template Details

(not for distribution or resale)

*"No installation, no macros - just a simple spreadsheet" - by Jon Wittwer*

### Description

The workbook contains two worksheets:

1) The **NPV Calculator** worksheet shown in the screenshot above lets you calculate NPV and IRR for multiple series of cash flows.

2) The **XIRR, XNPV Calculator** in the screenshot on the right uses Excel's XNPV and XIRR functions to calculate Net Present Value and Internal Rate of Return for a non-periodic series of cash flows (based on the dates).

- Add your own custom series and/or auto-generate the uniform, gradient, or exponential gradient series.

- See how to set up the NPV and IRR cash flows and formulas.

## Need to Estimate Return on Investment Projects?

ModelSheetSoft.com has developed a new technology that lets you create an easy-to-use, professional Investment Analysis Excel spreadsheet in minutes, by first customizing the specifications using an online form. You can then download your customized template that includes mixed equity, debt and lease financing, and many more options. It models sales, working capital, and fixed/variable expenses to evaluate and report on **net present value**, return on capital, and **internal rate of return**.

**Customize** the Investment Analysis template!

After you've tried it, contact me and let me know what you think of their system.

## How to Calculate IRR and NPV in Excel

**To calculate NPV or IRR**, you first need to have a predicted or estimated series of *periodic cash flows*. This will usually involve some initial lump payment as the initial investment (negative cash flow) at time t=0, followed by both inflows (income) and outflows (payments) at regular intervals t=1, t=2, t=3, etc. The image below shows an example.

**Net Present Value** is defined as the "*difference* between the present value (PV) of the * future* cash flows from an investment and the

*amount of investment*" [1] (emphasis added).

This is an excellent definition because it explains why the NPV formula in Excel is not really complete, and what you need to do to use it correctly. The word "Net" in NPV implies subtracting something from something else. The Excel NPV function really only calculates the **sum of the present value of future cash flows**. Note the emphasis on "future", which means cash flows starting at time t=1 or 1 period in the future from the present time t=0.

To calculate NPV, you must also subtract the value of the initial investment, or in other words, add the cash flow at time t=0 (which is a negative value). So in Figure 1 above, the equation for NPV is:

=NPV(*rate*, *values_t1_to_tn*) + *value_t0*

=NPV(B1,B5:B8)+B4

The formula for the internal rate of return (IRR) in Figure 1 is:

=IRR(*values_t0_to_tn*, *guess*)

=IRR(B4:B8,0.1)

It would be a good idea to read the Help document on the NPV and IRR functions, so that you can understand some of the limitations and requirements.

It is educational to note that the NPV formula is identical to calculating the present value of all *future* cash flows using the PV formula and summing them as shown in Figure 1. This can also be done using an array formula as explained by Timothy Mayers in reference [2] below. The array formula is actually more powerful than the NPV function, because you can include the initial cash flow at t=0 to avoid having to add it in separately, and you can have multiple values for the same period or even have the periods out of order.

=SUM(PV(*rate*, *periods_t0_to_tn*, 0, -*values*))

## Using the XNPV and XIRR Formulas to Calculate NPV and IRR

The second worksheet in the **NPV Calculator** spreadsheet is set up to help you calculate the Net Present Value and Internal Rate of Return for a series of scheduled cash flows that are non-periodic. The **XNPV** and **XIRR** functions require you to enter **dates** in addition to the cash flow **values** and the **discount rate**. They use a 365-day year to calculate present value based on an initial negative value (investment).

XIRR is to XNPV as IRR is to NPV

The following array formula can be used in place of the XNPV formula if you need to use a 360-day year or don't want to require the use of the Analysis ToolPak. Because it is an array formula, you need to press Ctrl+Shift+Enter after adding or editing it.

=SUM(*values*/((1+*rate*)^((*dates*-INDEX(*dates*,1))/365)))

### References

- [1] Definition of Net Present Value at
*BusinessDictionary.com* - [2] The NPV Function Doesn't Calculate Net Present Value by Timothy R. Mayes at
*tvmcalcs.com*- Explains how to correctly use the NPV function. - Time Value Function Tutorial by Timothy R. Mayes at
*tvmcalcs.com*

**Disclaimer**: This NPV Calculator spreadsheet and the information on this page is for illustrative and educational purposes only. We do not guarantee the results or the applicability to your unique financial situation. You should seek the advice of qualified professionals regarding financial decisions.