Syntax
=XIRR(values, dates, [guess])Arguments
| Argument | Required | Description |
|---|---|---|
values | Required | The cash flows, with at least one payment and one receipt |
dates | Required | The dates of the cash flows (the first is the start) |
guess | Optional | A first guess for the rate (10% when left out) |
How to use XIRR
XIRR takes 2 required arguments and 1 optional argument. Type =XIRR( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=XIRR(A1:A5, B1:B5)Result Data in the sheet
| A | B | |
|---|---|---|
| 1 | -10000 | 39448 |
| 2 | 2750 | 39508 |
| 3 | 4250 | 39751 |
| 4 | 3250 | 39859 |
| 5 | 2750 | 39904 |
Related financial functions
- SYDReturns the sum of years' digits depreciation of an asset for a period.
- TBILLEQReturns the bond equivalent yield of a Treasury bill.
- TBILLPRICEReturns the price per 100 face value of a Treasury bill.
- TBILLYIELDReturns the yield of a Treasury bill.
- VDBReturns the declining balance depreciation of an asset between two periods, switching to straight line when that is larger.
- XNPVReturns the net present value of cash flows that happen on given dates.
- YIELDReturns the yield of a security that pays periodic interest.
- YIELDDISCReturns the annual yield of a discounted security.
- YIELDMATReturns the annual yield of a security that pays interest at maturity.