Calculating Net Present Value (NPV) and Internal Rate of Return (IRR) in Excel
Net Present Value (NPV) Function
The NPV function calculates the present value of a series of cash flows at equal time intervals. The function is represented as follows:
Note that even though the function is named Net Present Value (NPV), it doesn’t really calculate the net present value. This is because it does not take into consideration the initial investment at time 0.
To calculate the net present value, you will need to subtract the initial investment from the result you get from the NPV function.
Lets take an example to demonstrate this function. Assume that you started a business with an initial investment of $10,000 and received the following income for the next five years.
To calculate the net present value, we will apply the NPV function as follows:
This is the present value of all the future cash flows.
The net present value will be:
Internal Rate of Return (IRR) Function
IRR is based on NPV. It as a special case of NPV, where the rate of return calculated is the interest rate corresponding to a 0 (zero) net present value.
IRR function is represented as follows:
This function accounts for the inflows and the outflows, including the initial investment at time 0.
Using the same example above, the IRR calculation is shown below:
The IRR of 14.974% means that at this rate the net present value will be zero.
Other Related Functions
MIRR: MIRR calculates the modified internal rate of return for a series of periodic cash flows, considering both cost of investment and interest on reinvestment of cash.
XNPV: This function calculates the net present value for a series of cash flows that are not equally spaced in time.
XIRR: This function calculates the internal rate of return for a series of cash flows that are not equally spaced in time.
Download the NPV IRR Excel to view these example calculations.
- Calculating Net Present Value (NPV) and Internal Rate of Return (IRR) in Excel
- Net Present Value of a Project
- The Capital Budgeting Process
- Principles of Capital Budgeting
- Mutually Exclusive Projects, Project Sequencing, and Capital Rationing
- How to Calculate Payback Period
- How to Calculate Discounted Payback Period
- Calculating Profitability Index of a Project
- Conflict Between NPV and IRR