# 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.

```aml title="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.

```aml
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:

```aql
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.