Create or edit a formula in a new financial report

Work with formulas on the new Income Statement, Balance Sheet, Statement of Owners' Equity and Statement of Cash Flows reports.

When creating or editing a formula, you can:

  • Enter these mathematical operators using your keyboard: > (greater-than), < (less-than), =.
  • Use numerals (1, 2, 3...) and parentheses: (, ).
  • Use group totals and other formulas positioned above your formula in the report.

Create a formula

  1. On the report you want to add the formula to, click Edit Layout.
  2. To add a formula in a standalone row, click the rows icon Image of the Row icon., then select Formula.

    You can also create a formula within a custom column or a schedule.

  3. In Name, enter the name of the line as you want it to appear on the final report.
  4. Place your cursor in the Formula panel and:
    • Click Insert, and select from the available group totals or formula lines.
    • Click any mathematical operator to insert it.
    • Enter numerals, brackets or other mathematical operators using your keyboard.
  5. When you've finished editing the report layout, click Done.

Edit a formula

  1. Click to select the formula line you want to edit.
  2. In the Formula panel on the right-hand side, place your cursor where you want to make changes.
    1. Click Insert, and select from the available group totals or formula lines.
    2. Click to insert a mathematical operator or click if to begin an IF statement.
    3. Use your keyboard to remove any part of the formula or enter numerals, brackets or mathematical operators.
  3. When you've finished editing the report layout, click Done.

Use an IF statement in a formula

IF statements work the same way in Xero as in Microsoft Excel. They need three parts:

  • First, a test that will come back 'yes' or 'no'.
    eg if (Assessable Surplus > 0
  • Second, a comma.
    eg if (Assessable Surplus > 0,
  • Third, what to do if the test is true, a comma, then what to do if it's false.
    eg if (Assessable Surplus > 0, Assessable Surplus *0.28,0)

You can add other variables to your IF statement and use mathematical operators to link them.

Don't use an equals sign (=) at the start of your IF statement.