Returns the internal rate of return for a series of cash flows. Calculates the discount rate that makes the net present value equal to zero, providing the effective annual return rate of an investment. Essential for investment analysis, project evaluation, and comparing returns across different investment opportunities.
Master the fundamentals of Excel IRR function
The IRR (Internal Rate of Return) function calculates the discount rate at which the net present value of a series of cash flows equals zero. IRR represents the effective annualized return rate of an investment, making it a critical metric for investment analysis, capital budgeting, and project evaluation. Unlike NPV which requires a discount rate input, IRR solves for the rate that makes an investment break-even in present value terms. IRR enables comparison of investments with different cash flow patterns and sizes, though it should be used alongside NPV and other metrics for comprehensive analysis. The function uses iterative numerical methods to find the solution.
IRR solves for the rate where NPV = 0: Σ(CFt/(1+IRR)^t) = 0. This rate represents the investment's effective return.
Excel uses Newton-Raphson or similar iterative methods to find IRR, starting from guess value. May not converge if no solution exists or guess is poor.
Requires at least one positive and one negative cash flow. Typically first cash flow is negative (initial investment). Without sign change, IRR may not exist.
Some cash flow patterns can have multiple IRRs (e.g., investment with intermediate outflows). Guess parameter helps find specific solution. Use XIRR or MIRR for complex cases.
IRR > required return (hurdle rate) = Accept investment. IRR < required return = Reject investment. IRR = required return = Indifferent. Higher IRR generally = better investment.
IRR assumes reinvestment at IRR rate (may be unrealistic). Doesn't account for project scale. Can give misleading results for non-conventional cash flows. Best used with NPV.
Function-specific parameters
Function-specific return type
Evaluate and compare investment return rates across different opportunities
Rank projects by IRR to allocate capital to highest-return opportunities
Assess project profitability and compare against required return (hurdle rate)
Analyze portfolio returns and compare investment performance
Evaluate acquisition returns and compare deal structures
Calculate property investment returns including purchase, income, and sale
Exact matching required
Returns numeric position
Handles missing text gracefully
=IRR(values, guess)An array or reference to cells containing cash flows. Must include at least one positive and one negative value. First cash flow is typically negative (initial investment). Cash flows must be in chronological order.
Your guess for the IRR. Default is 0.1 (10%). Excel uses iterative calculation starting from guess. Provide closer guess for faster convergence, especially for non-standard cash flows. For multiple possible IRRs, different guesses may find different solutions.
The internal rate of return as a decimal (multiply by 100 for percentage)
Description: Calculates the discount rate where NPV equals zero
Calculate return rate for investment
Returns 7.71% IRR, meaning investment generates 7.71% annual return. Compare to required return (hurdle rate) to make investment decision.
Using IRR function in VBA through WorksheetFunction
Sub IRRExample()
Dim result As Double
Dim cashFlows As Variant
cashFlows = Array(-1000, 300, 300, 300, 300)
result = Application.WorksheetFunction.IRR(cashFlows)
Range("A1").Value = result
Range("A1").NumberFormat = "0.00%"
MsgBox "IRR: " & Format(result, "0.00%")
End Sub
' Calculate IRR from cell range
Sub IRRFromRange()
Dim irrResult As Double
Dim guessValue As Double
guessValue = 0.1
On Error Resume Next
irrResult = Application.WorksheetFunction.IRR(Range("A1:A5"), guessValue)
On Error GoTo 0
If irrResult <> 0 Then
Range("B1").Value = irrResult
Range("B1").NumberFormat = "0.00%"
Else
Range("B1").Value = "No Solution"
End If
End Sub
' Compare multiple investments
Sub CompareIRRs()
Dim projectA As Double, projectB As Double
On Error Resume Next
projectA = Application.WorksheetFunction.IRR(Range("A1:A5"))
projectB = Application.WorksheetFunction.IRR(Range("B1:B5"))
On Error GoTo 0
If projectA > projectB Then
Range("C1").Value = "Project A Better: " & Format(projectA, "0.00%")
Else
Range("C1").Value = "Project B Better: " & Format(projectB, "0.00%")
End If
End Sub
' IRR with different guesses
Sub IRRWithGuesses()
Dim cashFlows As Variant
Dim guess As Double, irr As Double
Dim i As Integer
cashFlows = Range("A1:A6").Value
For i = 1 To 10
guess = 0.05 + (i * 0.02)
On Error Resume Next
irr = Application.WorksheetFunction.IRR(cashFlows, guess)
On Error GoTo 0
Range("B" & i).Value = irr
Range("B" & i).NumberFormat = "0.00%"
Next i
End SubCalculate return rate for investments
Compare IRR across multiple projects
Rank projects by return rate
Calculate property investment returns
Evaluate business investment returns
Analyze investment portfolio returns
IRR returns #NUM! error
=IRR({1000, 2000, 3000})Solution: IRR requires at least one positive and one negative cash flow. Check: 1) Cash flows have sign change (negative to positive or vice versa), 2) Try different guess value, 3) Verify cash flows are in correct order, 4) For complex patterns, consider XIRR or MIRR. If all cash flows same sign, IRR cannot be calculated.
IRR returns unexpected value or different values with different guesses
=IRR({-1000, 300, -200, 400}, 0.1)Solution: Cash flows with multiple sign changes (e.g., -1000, 300, -200, 400) can have multiple IRRs. Try different guess values to find alternative solutions. For non-conventional cash flows, consider using MIRR (Modified IRR) which provides unique solution.
IRR fails to find solution even with valid cash flows
=IRR(A1:A5, 0.15)Solution: Try different guess values closer to expected IRR. Start with reasonable estimate (e.g., 0.10 for 10%). For very high or very low IRRs, use extreme guesses. If still fails, cash flow pattern may not have real solution - verify cash flow data.
IRR returns extremely high or negative percentages
=IRR(A1:A5)Solution: Verify cash flow signs and values are correct. Check for data entry errors. Negative IRRs are possible for losing investments. Very high IRRs (>100%) may indicate data errors or very short-term high returns. Review cash flow calculations.
IRR returns #VALUE! error
=IRR(A1:A5)Solution: Non-numeric values in cash flow range. Verify all cells contain numbers. Check for text, errors, or blank cells in range. Ensure cell references are correct and range contains valid numeric cash flows.
Uncertain how to interpret IRR result
=IRR(A1:A5)Solution: IRR represents annualized return rate. Compare to required return (hurdle rate): IRR > hurdle = accept, IRR < hurdle = reject. Higher IRR = better return. However, consider: project size (use NPV too), risk level, and reinvestment assumptions. Use with NPV for comprehensive analysis.