The concepts of present value and internal rate of return are essential to evaluating investment opportunities and serve as the cornerstone for corporate finance. In this guide, we'll review what NPV and IRR are and how they are used in finance.
Financial Concepts
Net Present Value
The cornerstone of finance is the concept of Net Present Value (NPV). Essentially, a dollar today is worth more than a dollar in the future. Using NPV, we can discount future cash - say from a project or investment - to determine the value today.
Example: Assuming an 8% discount rate, or cost of capital, an investment that costs $100 today that will yield $50 at the end of the next three years has an NPV =
-$100 + $50/(1+8%)^1 + $50/(1+8%)^2 + $50/(1+8%)^3 = $28.85.
The positive NPV indicates modeled value above the assumed cost of capital. A decision also depends on risk, the reliability of the forecasts, and other available uses of capital.
When building a Discounted Cash Flow (DCF) model to value a company, as we do in our financial modeling course, we are using NPV to discount to today's value the future cash flows the company will generate.
Internal Rate of Return
The project above has an NPV greater than zero when the discount rate is 8%. Let's say we wanted to determine the discount rate that would make the NPV = 0.
In Excel, we can use Goal Seek to set the NPV to 0 by changing the discount rate.
Data > What-If Analysis > Goal Seek
Setting the NPV to 0 by changing the discount rate yields a value of 23.4%. The 23.4% represents the IRR of the investment, or the money-weighted annualized return.
We can also use the IRR function and we'll get the same 23.4%. For this conventional cash-flow example, an IRR above the 8% cost of capital is consistent with a positive NPV. IRR can be misleading when comparing projects of different sizes or timing, and some cash flows have multiple IRRs or none.
IRRs are especially useful when evaluating Leveraged Buyouts and bond investments (the bond yield is an IRR).
Financial Functions in Excel
Most Financial Functions utilize inputs from a small pool of possibilities. These commonly include:
- rate: interest rate, if annual interest rate, periods must reflect this (APR/12)
- nper: number of periods (usually expressed in months)
- pmt: payment amount
- PV: present value; use a negative sign for money paid out and a positive sign for money received
- FV: future value; use a sign consistent with the direction of the cash flow
-
type: defines when a payment is due:
- “0” = payments are due at the end of a period
- “1” = payments are due at the beginning of a period
Present Value in Excel
=PV(rate,nper,pmt,[FV],[type])
Future Value in Excel
=FV(rate,nper,pmt,[PV],[type])
Net Present Value in Excel
=NPV(rate, value1, value2, …)
Excel's NPV function treats its cash flows as occurring at the ends of equally spaced periods. Add an initial cash flow occurring today separately, for example =NPV(8%,50,50,50)-100. For irregular dates, use XNPV with the complete dated cash-flow series.
=XNPV(rate, values, dates)
Internal Rate of Return
=IRR(values, [guess])
If left blank, the [guess] defaults to 0.1
If payments occur at inconsistent periods, the XIRR function can be used.
=XIRR(values, dates, [guess])