Financialintermediate
Calculate a fixed loan payment with PMT
=PMT(B1/12,B2*12,-B3)
Excel how-to guide
How to irr return rate in Excel
Compare the implied return of a project against your cost of capital or a benchmark rate to decide whether to proceed.
Step-by-step instructions
How to irr return rate — 3 steps
1
List all cash flows in order in a single column, starting with the initial investment as a negative number.
2
Include the initial outflow in the same range — unlike NPV, IRR handles period 0 inside the function.
3
IRR returns a periodic rate matching the frequency of your cash flows. Multiply by 12 for annual IRR from monthly data.
Example data
Worked example
| Period | Cash Flow |
|---|---|
| 0 (Today) | -50000 |
| Year 1 | 15000 |
| Year 2 | 18000 |
| Year 3 | 20000 |
| Year 4 | 22000 |
| Year 5 | 25000 |
Common mistakes
Errors to watch out for
IRR returns
IRR needs at least one negative and one positive cash flow, and the iterative solver must converge. Try adding a guess as the second argument — =IRR(B2:B7,0.1) — or check that your cash flows change sign at least once.
IRR is a periodic rate, not annual
If your cash flows are monthly, IRR returns a monthly rate. Multiply by 12 for a rough annual rate, or use (1+IRR(...))^12-1 for the exact compounded annual rate.
FAQ
Frequently asked questions
What is the difference between IRR and XIRR?
IRR assumes evenly-spaced periods. XIRR accepts actual dates for each cash flow, making it more accurate for irregular payment schedules.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Financialadvanced
Calculate net present value with NPV
=NPV(B1,B3:B7)+B2