How to Use XIRR with Excel Arrays and Cash Flow Data in WPS Office Spreadsheets
Question details
User needs guidance on calculating XIRR in a spreadsheet using arrays of dates and cash flows, including symbols, opening amounts, closing amounts, and interim values.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a financial model with irregular cash flows and wants to calculate annualized returns using the XIRR function with array-based data.
- Observed behavior
- The user expects to understand how to arrange dates and cash flow arrays so the XIRR function returns the correct annualized return from mixed inflows and outflows.
Open the workbook in WPS Office Spreadsheets and verify that every cash flow date is stored as a real date value rather than text. If you are calculating multiple symbols, prepare a column that uniquely identifies each symbol so you can filter or array-enter XIRR correctly.
Use the XIRR function with correctly signed value and date ranges
Use this method when you have one cash flow series in columns and need the annualized return based on irregular dates.
XIRR calculates the internal rate of return for cash flows that are not periodic. The result depends on the signs of each amount: outflows should be negative, inflows positive, and the closing value should be included as a positive amount at the last date.
If the result is not close to your expectation, check that the date range and value range are the same size and that there is at least one negative and one positive value.
Create two columns such as Date in column A and Cash Flow in column B. Enter valid dates from earliest to latest and put opening amount, interim amounts, and closing amount in the cash flow column.
Type the opening investment as a negative number, cash contributions or withdrawals as negative or positive according to direction, and the final closing value as a positive number.
Click the result cell and enter =XIRR(B2:B20,A2:A20,0.1). Replace the ranges with your actual cash flow range and date range.
Press Enter and format the cell as a percentage. If you see an error, double-check that all dates are real date values and that the ranges do not include blank text labels.
Calculate XIRR for a specific symbol using an array formula
Use this method when the table contains multiple symbols and you want to calculate XIRR for one symbol without manually filtering the data.
Fix common XIRR #NUM and #VALUE errors
Use this troubleshooting method when XIRR returns an error or an unrealistic percentage.
Calculate XIRR returns with WPS Office Spreadsheets
WPS Office Spreadsheets includes the XIRR function with Excel-compatible syntax, so you can build cash flow models, use opening, closing, and interim amounts, and calculate annualized returns for free.
- 1. Open WPS Office Spreadsheets: Download and install WPS Office, then open a new spreadsheet or open your existing Excel cash flow workbook.
- 2. Enter the cash flow data: Create Date and Cash Flow columns and enter opening, interim, and closing amounts with the correct sign.
- 3. Apply the XIRR formula: In the result cell, type =XIRR(values,dates,0.1) or use the formula to reference your ranges.
- 4. Format and share the workbook: Format the result as a percentage and save the file as .xlsx to keep it compatible with Excel.
Frequently Asked Questions
Why does XIRR return a #NUM! error in WPS Office?
XIRR returns #NUM! when the cash flow range does not contain at least one negative and one positive value, when the date range contains invalid dates, or when the function cannot converge. Check the signs, date formatting, and try a different guess such as 0.1 or -0.1.
How do I include opening and closing amounts in an XIRR calculation?
Enter the opening amount as a negative cash flow on the first date and the closing amount as a positive cash flow on the last date. Interim contributions are usually negative outflows, while interim distributions or withdrawals are positive inflows.
Can I use XIRR for multiple symbols in one table?
Yes. Keep a symbol column and use an array formula such as =XIRR(IF(symbol_range=target,cash_flow_range),IF(symbol_range=target,date_range),0.1). If needed, press Ctrl+Shift+Enter to enter it as an array formula in WPS Office Spreadsheets.
Does WPS Office support the same XIRR syntax as Excel?
Yes. The function uses =XIRR(values, dates, guess) in WPS Office Spreadsheets and works with .xlsx workbooks, so Excel cash flow models can usually be opened and recalculated without changing the formula.
What is the guess parameter in XIRR and do I need to change it?
The guess is the starting estimate for the internal rate of return. The default 0.1, or 10%, works for most models. If the formula does not converge, try another guess such as 0.01 or -0.1.
