Project profitability

Calculate discount factor, free cash flow, discounted cash flow, NPV and payback year for a project with reusable AQL functions.

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.

Project profitability

How it worksCopy Anchor Link

Step 1: Add the function libraryCopy Anchor Link

Step 2: Import it and add the metricsCopy Anchor Link

ParametersCopy Anchor Link

Use itCopy Anchor Link