ModeldrillIRR in Excel
The internal rate of return is the discount rate that makes the NPV of a series of cash flows zero. In Excel, =IRR(values, [guess]) takes a range that starts with the negative investment at time zero followed by the later cash flows, all one period apart.
Syntax
=IRR(values, [guess])| Part | What it is |
|---|---|
values | Range of cash flows in time order, starting with the time zero outlay; must contain at least one negative and one positive |
guess | Optional starting estimate (default 10%); set it when IRR returns #NUM! |
Examples
Cash flows: -1000, 400, 400, 400
=IRR({-1000,400,400,400})Result: 9.70%
At 9.70% the three PVs add to 1,000.
Cash flows: -500, 300, 300
=IRR({-500,300,300})Result: 13.07%
MIRR with 8% finance and reinvestment rates
=MIRR({-1000,400,400,400}, 8%, 8%)Result: 9.10%
Inflows compound at 8% to 1,298.56, then (1,298.56 / 1,000)^(1/3) - 1.
Common mistakes
- #NUM! because all cash flows have the same sign, or because Excel did not converge: check the signs and supply a guess near the expected rate.
- Leaving the initial investment out of the range: IRR needs the outlay as the first value, unlike NPV.
- Comparing a monthly IRR to an annual hurdle: annualize with (1 + monthly IRR)^12 - 1.
Practice IRR in Excel
These drills check your answer on live data, free, no account needed.
- NPV, IRR and profitability indexThe project costs the year 0 amount in B5 and returns cash in years 1 to 5. Find the NPV at the rate in B2 (B7, using NPV), the IRR (B8, using IRR) and the profitability index (B9: present value of the inflows divided by the initial cost).
- Return on an investment with irregular datesAn investor put money in twice and received four distributions. Using the dates in column A and flows in column B, fill the XIRR (E5), the money multiple (E6: total received divided by total invested), the years held (E7: last date less first date, over 365) and the profit (E8).
Questions
What is the difference between IRR and XIRR?
IRR assumes equal gaps between cash flows; XIRR uses actual dates and returns an annual rate. Use XIRR for irregular timing.
Why is my IRR higher than NPV suggests?
IRR assumes interim cash flows are reinvested at the IRR itself. MIRR uses an explicit reinvestment rate and is usually lower and more realistic.
Related
- NPV in ExcelExcel NPV discounts every value one period, so the time zero investment goes outside the function: =NPV(rate, cash flows) + initial. Exact examples, errors.
- XNPV and XIRRXNPV and XIRR discount cash flows on their real dates using a 365-day year, with the first date as time zero. Syntax, exact worked examples and common errors.
- Payback periodPayback period = years before full recovery + unrecovered amount / cash flow in the recovery year. Simple and discounted versions, exact examples, formula.
- DCF model stepsThe six steps of a DCF: forecast unlevered free cash flow, set WACC, compute terminal value, discount it all, bridge to equity value and divide by shares.