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.
Since the NPV is positive, we should invest in the project.
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 time-weighted/average return.
We can also use the IRR function and we'll get the same 23.4%. Since 23.4% is greater than our 8% cost of capital, we should invest in the project (we arrive at the same conclusion as the NPV method).
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 of the investment (must be entered as a negative value)
- FV: future value of the investment (must be entered as a negative value)
- type: defines when a payment is due:
- “0” = payments are due at the beginning of a period
- “1” = payments are due at the end 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, …)
If payments occur at inconsistent periods, the XNPV function can be used.
=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])