Overview
This lesson explains how to use Net Present Value (NPV), Internal Rate of Return (IRR), and Modified Internal Rate of Return (MIRR) in Excel to evaluate investment projects.
Net Present Value (NPV)
- NPV is the present value of all cash inflows and outflows for a project, discounted by a chosen rate.
- The discount rate used is typically the firm’s weighted average cost of capital (WACC).
- In Excel, add the time zero cash flow separately, then use the NPV function for the remaining flows.
- Excel’s NPV function assumes the first cash flow occurs in one year, not at time zero.
Internal Rate of Return (IRR)
- IRR is the discount rate that makes the NPV of a cash flow stream equal to zero.
- IRR represents the project’s expected rate of return.
- The calculation assumes all cash inflows are reinvested at the IRR itself.
- In Excel, use the IRR function and highlight all cash flows, including time zero.
Modified Internal Rate of Return (MIRR)
- MIRR is similar to IRR but allows different rates for finance and reinvestment.
- MIRR does not assume reinvestment at the IRR; users specify both finance and reinvestment rates.
- MIRR is useful when NPV and IRR results conflict or when multiple IRRs exist.
- In Excel, use the MIRR function, entering the finance and reinvestment rates.
Sensitivity Analysis with Data Tables
- Use Excel's data tables to analyze how changes in WACC affect NPV.
- Link the NPV cell to the table, select the range, and use "What-If Analysis" > "Data Table" with WACC as the column input.
- Results show NPV for each discount rate; NPV crosses zero at the IRR.
Normal vs. Non-Normal Cash Flows
- Normal cash flows: one outflow at time zero, followed by inflows.
- Non-normal cash flows: multiple outflows (e.g., at time zero and end), potentially resulting in multiple IRRs.
Key Terms & Definitions
- Net Present Value (NPV) — Sum of present values of all cash inflows and outflows for a project.
- Internal Rate of Return (IRR) — Discount rate where NPV equals zero.
- Modified Internal Rate of Return (MIRR) — Refinement of IRR with separate finance and reinvestment rates.
- Weighted Average Cost of Capital (WACC) — Average rate a company pays to finance its assets.
Action Items / Next Steps
- Practice using NPV, IRR, and MIRR functions with sample project cash flows in Excel.
- Complete a sensitivity analysis using data tables for different WACC values.
- Review the differences between normal and non-normal cash flows.