Project profitability

These functions measure whether a project pays off. They take a project's expected revenue, costs and investment per year, and return the standard profitability measures:

• Discount factor (DF): what one unit of money in a given year is worth today, at your discount rate.
• Free cash flow (FCF): revenue + costs + investment for the year.
• Discounted cash flow (DCF): FCF × DF.
• Net present value (NPV): the running total of DCF. When it turns positive, the project has paid back its investment.
• Payback year: the first year NPV is positive, and how many years it took.

How it works

Take a project that invests 1,000 in 2025 and earns from 2026, with a 10% discount rate.

Year	Revenue	Costs	Investment	DF	FCF	DCF	NPV
2025			-1,000	1	-1,000	-1,000	-1,000
2026	400	-100		0.9091	300	272.73	-727.27
2027	500	-100		0.8264	400	330.58	-396.69
2028	700	-100		0.7513	600	450.79	54.10

• The first year is the first year with an investment, here 2025. Its discount factor is 1.
• Each later year is discounted once more: DF = 1 / (1 + rate) ^ (year − first year).
• NPV adds up DCF year by year. It turns positive in 2028, so the project pays back in 2028, after 4 years. The NPV of the whole project is 54.10.

Enter costs and investment as negative values. Enter the discount rate as a decimal: 0.1 for 10%.

Step 1: Add the function library

Add this AqlFunctionModule to your project.

library/aql-functions/project_profitability.aml

AqlFunctionModule ProjectProfitability {
  // First year with an investment, across the selected years.
  function FirstYear {
    param pInvestment {
      type: 'number'
      mode: 'expr'
    }
    param pYear {
      type: 'dimension'
      mode: 'expr'
    }
    return_type: 'number'
    definition: @aql
      unique(pYear)
        | select(y: case(when: pInvestment is not null, then: pYear))
        | min(y)
        | of_all(pYear, keep_filters: true)
    ;;
  }

  // Last of the selected years.
  function LastYear {
    param pYear {
      type: 'dimension'
      mode: 'expr'
    }
    return_type: 'number'
    definition: @aql max(pYear) | of_all(pYear, keep_filters: true) ;;
  }

  function Fcf {
    param pRevenue {
      type: 'number'
      mode: 'expr'
    }
    param pCost {
      type: 'number'
      mode: 'expr'
    }
    param pInvestment {
      type: 'number'
      mode: 'expr'
    }
    return_type: 'number'
    definition: @aql coalesce(pRevenue, 0) + coalesce(pCost, 0) + coalesce(pInvestment, 0) ;;
  }

  // null outside the project years, and when the year is not in the table.
  function Df {
    param pInvestment {
      type: 'number'
      mode: 'expr'
    }
    param pYear {
      type: 'dimension'
      mode: 'expr'
    }
    param pRate {
      type: 'number'
    }
    return_type: 'number'
    definition: @aql
      case(
        when: is_at_level(pYear)
          and max(pYear) >= FirstYear(pInvestment, pYear)
          and max(pYear) <= LastYear(pYear),
        then: 1 / pow(1 + pRate, max(pYear) - FirstYear(pInvestment, pYear)),
        else: null
      )
    ;;
  }

  function Dcf {
    param pRevenue {
      type: 'number'
      mode: 'expr'
    }
    param pCost {
      type: 'number'
      mode: 'expr'
    }
    param pInvestment {
      type: 'number'
      mode: 'expr'
    }
    param pYear {
      type: 'dimension'
      mode: 'expr'
    }
    param pRate {
      type: 'number'
    }
    return_type: 'number'
    definition: @aql Fcf(pRevenue, pCost, pInvestment) * Df(pInvestment, pYear, pRate) ;;
  }

  // Running total of DCF up to the current year.
  function Npv {
    param pRevenue {
      type: 'number'
      mode: 'expr'
    }
    param pCost {
      type: 'number'
      mode: 'expr'
    }
    param pInvestment {
      type: 'number'
      mode: 'expr'
    }
    param pYear {
      type: 'dimension'
      mode: 'expr'
    }
    param pRate {
      type: 'number'
    }
    return_type: 'number'
    definition: @aql window_sum(Dcf(pRevenue, pCost, pInvestment, pYear, pRate), order: pYear) ;;
  }

  // NPV of the whole project: DCF summed over the selected years.
  function NpvTotal {
    param pRevenue {
      type: 'number'
      mode: 'expr'
    }
    param pCost {
      type: 'number'
      mode: 'expr'
    }
    param pInvestment {
      type: 'number'
      mode: 'expr'
    }
    param pYear {
      type: 'dimension'
      mode: 'expr'
    }
    param pRate {
      type: 'number'
    }
    return_type: 'number'
    definition: @aql
      unique(pYear)
        | select(d: Dcf(pRevenue, pCost, pInvestment, pYear, pRate))
        | sum(d)
        | of_all(pYear, keep_filters: true)
    ;;
  }

  // First year with NPV above 0. null if the project never pays back.
  function PaybackYear {
    param pRevenue {
      type: 'number'
      mode: 'expr'
    }
    param pCost {
      type: 'number'
      mode: 'expr'
    }
    param pInvestment {
      type: 'number'
      mode: 'expr'
    }
    param pYear {
      type: 'dimension'
      mode: 'expr'
    }
    param pRate {
      type: 'number'
    }
    return_type: 'number'
    definition: @aql
      unique(pYear)
        | select(y: pYear, npv: window_sum(Dcf(pRevenue, pCost, pInvestment, pYear, pRate), order: pYear))
        | select(p: case(when: npv > 0, then: y))
        | min(p)
        | of_all(pYear, keep_filters: true)
    ;;
  }

  // Years from the first selected year to the payback year, inclusive.
  function PaybackNbYear {
    param pRevenue {
      type: 'number'
      mode: 'expr'
    }
    param pCost {
      type: 'number'
      mode: 'expr'
    }
    param pInvestment {
      type: 'number'
      mode: 'expr'
    }
    param pYear {
      type: 'dimension'
      mode: 'expr'
    }
    param pRate {
      type: 'number'
    }
    return_type: 'number'
    definition: @aql
      PaybackYear(pRevenue, pCost, pInvestment, pYear, pRate)
        - (min(pYear) | of_all(pYear, keep_filters: true))
        + 1
    ;;
  }
}

Step 2: Import it and add the metrics

This example uses a project_cashflow model with one row per year and four columns: year (a number), revenue, cost and investment. Leave investment empty in years without one.

Dataset project_profitability {
  models: [project_cashflow]

  function_module ProjectProfitability: ProjectProfitability

  metric revenue {
    type: 'number'
    definition: @aql sum(project_cashflow.revenue) ;;
  }
  metric cost {
    type: 'number'
    definition: @aql sum(project_cashflow.cost) ;;
  }
  metric investment {
    type: 'number'
    definition: @aql sum(project_cashflow.investment) ;;
  }

  // By year
  metric df {
    label: 'DF'
    type: 'number'
    definition: @aql ProjectProfitability.Df(investment, project_cashflow.year, 0.1) ;;
  }
  metric fcf {
    label: 'FCF'
    type: 'number'
    definition: @aql ProjectProfitability.Fcf(revenue, cost, investment) ;;
  }
  metric dcf {
    label: 'DCF'
    type: 'number'
    definition: @aql ProjectProfitability.Dcf(revenue, cost, investment, project_cashflow.year, 0.1) ;;
  }
  metric npv {
    label: 'NPV'
    type: 'number'
    definition: @aql ProjectProfitability.Npv(revenue, cost, investment, project_cashflow.year, 0.1) ;;
  }

  // For the whole project
  metric npv_total {
    label: 'NPV (project)'
    type: 'number'
    definition: @aql ProjectProfitability.NpvTotal(revenue, cost, investment, project_cashflow.year, 0.1) ;;
  }
  metric payback_year {
    label: 'Payback Year'
    type: 'number'
    definition: @aql ProjectProfitability.PaybackYear(revenue, cost, investment, project_cashflow.year, 0.1) ;;
  }
  metric payback_nb_year {
    label: 'Payback (years)'
    type: 'number'
    definition: @aql ProjectProfitability.PaybackNbYear(revenue, cost, investment, project_cashflow.year, 0.1) ;;
  }
}

Parameters

Parameter	Type	What it is
pRevenue	number, expr	Revenue, such as sum(project_cashflow.revenue).
pCost	number, expr	Costs, as negative values.
pInvestment	number, expr	Investment, as negative values. The first year with an investment is the project's first year.
pYear	dimension	The year, as a number, such as project_cashflow.year.
pRate	number	The discount rate as a decimal, such as 0.1 for 10%.

Fcf takes only pRevenue, pCost and pInvestment. Df takes pInvestment, pYear and pRate.

Use it

Show the year-by-year view in a table, with the year in the table:

explore {
  dimensions {
    year: project_cashflow.year
  }
  measures {
    df: df,
    fcf: fcf,
    dcf: dcf,
    npv: npv
  }
  sorts {
    year asc
  }
}

Show npv_total, payback_year and payback_nb_year as KPIs, without the year.

Limitations

• df, dcf and npv need the year in the table. Without it they return null.
• npv adds up the years shown in the table. If a filter removes a year in the middle of the project, the running total skips it.
• payback_year and payback_nb_year return null when NPV never turns positive.
• payback_nb_year counts from the first selected year, not from the first year with an investment.
• Internal rate of return (IRR) is not included. It needs an iterative solver, which AQL functions don't support.