# Return growth calculation (1M, 6M, 1Y, 3Y, 5Y, All time)

Return growth is the percentage change of a fund's **adjusted NAV** between a base date and its latest NAV date. The adjusted NAV is already stored for every fund (`mf_nav_history.adjusted_nav`), with dividends included. So the same calculation works for all 27 funds.

Data as of 26 Oct 2026. Database: MariaDB 10.4 / MySQL, schema `fintra_mf`.

---

## 1. Data used

| Table.column | Type | Meaning |
|---|---|---|
| `mf_funds.id` | BIGINT UNSIGNED | Fund id |
| `mf_funds.slug` | VARCHAR | Fund code, e.g. `EFUF` |
| `mf_funds.series_start_date` | DATE | **t0**, the fund's first NAV date |
| `mf_nav_history.fund_id` | BIGINT UNSIGNED | → `mf_funds.id` |
| `mf_nav_history.as_of_date` | DATE | NAV date (one row per fund per date) |
| `mf_nav_history.adjusted_nav` | DECIMAL(16,6) | **K(t)**, the value every return is read from |
| `mf_nav_history.is_glitch` | TINYINT(1) | 1 = bad row; never used in a return |

Only these columns are needed. Do not use `nav` or `composite_nav` for returns; `adjusted_nav` already holds the right value for each fund.

**Rows that count** (the fund's series):

```
as_of_date >= series_start_date  AND  is_glitch = 0
```

---

## 2. Formula

```
T       = latest NAV date of the fund             K(T) = its adjusted_nav
cutoff  = T − range                               calendar months/years (1M, 6M, 1Y, 3Y, 5Y)
b       = latest NAV date ≤ cutoff                the base date
          if the fund has no NAV that old: b = t0 → label "Since launch"
All     : b = t0

Return  = ( K(T) / K(b) − 1 ) × 100
```

| Range | Cutoff |
|---|---|
| 1M | T − 1 month |
| 6M | T − 6 months |
| 1Y | T − 1 year |
| 3Y | T − 3 years |
| 5Y | T − 5 years |
| All time | none, b = t0 |

Month arithmetic does not overflow: 31 Mar − 1 month = 28 Feb (MySQL `DATE_SUB` and Carbon `subMonthsNoOverflow` both behave this way).

**Chart points** for a range: `G(t) = (K(t) / K(b) − 1) × 100` for every series date from b to T. The chart starts at 0% and ends at the headline return.

---

## 3. SQL: all ranges for all funds

```sql
SELECT f.slug, r.label,
       b.as_of_date AS base_date, b.adjusted_nav AS k_base,
       t.as_of_date AS end_date,  t.adjusted_nav AS k_end,
       ROUND((t.adjusted_nav / b.adjusted_nav - 1) * 100, 2) AS return_pct,
       (r.months > 0 AND b.as_of_date = f.series_start_date) AS since_launch
FROM mf_funds f
JOIN (SELECT '1M' AS label, 1 AS months UNION ALL SELECT '6M', 6 UNION ALL SELECT '1Y', 12
      UNION ALL SELECT '3Y', 36 UNION ALL SELECT '5Y', 60 UNION ALL SELECT 'ALL', 0) r
-- T: the latest non-glitch NAV
JOIN mf_nav_history t ON t.fund_id = f.id AND t.as_of_date =
     (SELECT MAX(x.as_of_date) FROM mf_nav_history x
      WHERE x.fund_id = f.id AND x.is_glitch = 0)
-- b: the latest NAV on or before T − range, else t0
JOIN mf_nav_history b ON b.fund_id = f.id AND b.as_of_date = IF(r.months = 0, f.series_start_date, COALESCE(
     (SELECT MAX(x.as_of_date) FROM mf_nav_history x
      WHERE x.fund_id = f.id AND x.is_glitch = 0
        AND x.as_of_date >= f.series_start_date
        AND x.as_of_date <= DATE_SUB(t.as_of_date, INTERVAL r.months MONTH)),
     f.series_start_date))
ORDER BY f.slug, r.months = 0, r.months;
```

For one fund, add `WHERE f.slug = 'EFUF'`.

**Chart points** for one fund and range (here EFUF, 1Y):

```sql
SET @fund = (SELECT id FROM mf_funds WHERE slug = 'EFUF');
SET @t0   = (SELECT series_start_date FROM mf_funds WHERE id = @fund);
SET @T    = (SELECT MAX(as_of_date) FROM mf_nav_history WHERE fund_id = @fund AND is_glitch = 0);
SET @b    = COALESCE((SELECT MAX(as_of_date) FROM mf_nav_history
                      WHERE fund_id = @fund AND is_glitch = 0 AND as_of_date >= @t0
                        AND as_of_date <= DATE_SUB(@T, INTERVAL 12 MONTH)), @t0);
SET @kb   = (SELECT adjusted_nav FROM mf_nav_history WHERE fund_id = @fund AND as_of_date = @b);

SELECT as_of_date, ROUND((adjusted_nav / @kb - 1) * 100, 2) AS growth_pct
FROM mf_nav_history
WHERE fund_id = @fund AND is_glitch = 0 AND as_of_date BETWEEN @b AND @T
ORDER BY as_of_date;
```

---

## 4. Code

```php
use Carbon\Carbon;

const RANGES = ['1M' => 1, '6M' => 6, '1Y' => 12, '3Y' => 36, '5Y' => 60, 'ALL' => null];

/**
 * $series: the fund's rows [as_of_date => adjusted_nav], as_of_date >= series_start_date,
 * is_glitch = 0, sorted by date ascending.
 */
function returnGrowth(array $series): array
{
    $dates = array_keys($series);
    $t0 = $dates[0];
    $T = end($dates);
    $out = [];

    foreach (RANGES as $label => $months) {
        $b = $t0;                                                    // "All", or fund younger than the range
        if ($months !== null) {
            $cutoff = Carbon::parse($T)->subMonthsNoOverflow($months)->toDateString();
            foreach ($dates as $date) {
                if ($date > $cutoff) {
                    break;
                }
                $b = $date;                                          // latest date on or before the cutoff
            }
        }
        $out[$label] = [
            'from' => $b,
            'return' => round(($series[$T] / $series[$b] - 1) * 100, 2),
            'since_launch' => $months !== null && $b === $t0,
        ];
    }

    return $out;
}
```

The same in JavaScript, for a chart:

```js
// rows: [{ date: 'YYYY-MM-DD', k: adjusted_nav }], ascending, glitch rows removed
function returnGrowth(rows, months /* null = All */) {
  const T = rows[rows.length - 1];
  let start = 0;
  if (months) {
    const [y, m, d] = T.date.split('-').map(Number);
    const c = new Date(y, m - 1 - months, Math.min(d, new Date(y, m - months, 0).getDate()));
    const cutoff = `${c.getFullYear()}-${String(c.getMonth() + 1).padStart(2, '0')}-${String(c.getDate()).padStart(2, '0')}`;
    rows.forEach((r, i) => { if (r.date <= cutoff) start = i; });
  }
  const base = rows[start].k;
  const points = rows.slice(start).map(r => ({ date: r.date, growth: (r.k / base - 1) * 100 }));
  return { from: rows[start].date, return: points[points.length - 1].growth, points };
}
```

---

## 5. Worked example: EFUF (Ekush First Unit Fund)

T = 25 Oct 2026, K(T) = 23.031000.

| Range | Cutoff | Base date b | K(b) | Return |
|---|---|---|---|---|
| 1M | 25 Sep 2026 | 24 Sep 2026 | 22.512000 | (23.031000 / 22.512000 − 1) × 100 = **+2.31%** |
| 6M | 25 Apr 2026 | 23 Apr 2026 | 20.852000 | **+10.45%** |
| 1Y | 25 Oct 2025 | 25 Oct 2025 | 19.642000 | **+17.25%** |
| 3Y | 25 Oct 2023 | 25 Oct 2023 | 16.093000 | **+43.11%** |
| 5Y | 25 Oct 2021 | 25 Oct 2021 | 15.891000 | **+44.93%** |
| All time | none | 25 Nov 2019 (t0) | 10.000000 | **+130.31%** |

The 1M base is 24 Sep, the last NAV on or before the 25 Sep cutoff. A fund younger than a range shows its since-launch return for that range (marked * in section 6).

---

## 6. Results for all funds

Return growth in %, T = latest NAV date (25 Oct 2026; VIPB funds 26 Oct 2026; ESRF 19 Oct 2026, because its 25 Oct row is a glitch). * = the fund is younger than the range, so the figure is since launch.

| Fund | t0 | 1M | 6M | 1Y | 3Y | 5Y | All time |
|---|---|---|---|---|---|---|---|
| CWTCBSF | 2023-10-01 | -1.63 | 0.20 | 0.10 | 5.78 | 5.99* | 5.99 |
| CWTEBFGF | 2019-12-22 | 1.03 | 3.82 | 8.99 | 26.22 | 32.42 | 125.86 |
| CWTOF | 2021-06-13 | 4.59 | 8.29 | 13.82 | 25.04 | 25.87 | 35.46 |
| CWTSBGF | 2020-09-13 | 0.96 | 5.41 | 11.52 | 21.41 | 27.02 | 89.22 |
| EDGEALAMIN | 2022-09-01 | -0.25 | 4.17 | 6.86 | 19.30 | 19.90* | 19.90 |
| EDGEAMCGF | 2019-09-05 | 1.82 | 7.53 | 12.14 | 42.77 | 36.66 | 101.30 |
| EDGEBDMF | 2018-08-09 | 1.94 | 7.18 | 12.27 | 42.71 | 37.31 | 110.29 |
| EDGEHQIF | 2022-02-10 | 0.78 | 5.41 | 8.48 | 42.08 | 55.53* | 55.53 |
| EFUF | 2019-11-25 | 2.31 | 10.45 | 17.25 | 43.11 | 44.93 | 130.31 |
| EGF | 2021-10-12 | 2.31 | 10.51 | 17.75 | 47.65 | 52.21 | 52.21 |
| ESRF | 2022-09-06 | 0.61 | 9.59 | 14.63 | 48.31 | 56.05* | 56.05 |
| IDLCAMS | 2019-12-12 | -0.72 | 3.12 | 4.17 | 9.64 | 2.51 | 40.78 |
| IDLCBF | 2017-07-20 | 2.13 | 8.47 | 16.65 | 34.39 | 32.45 | 111.59 |
| IDLCGF | 2018-05-24 | 0.61 | 4.82 | 9.59 | 29.99 | 25.95 | 100.75 |
| IDLCIF | 2021-06-24 | 0.77 | 4.94 | 10.46 | 33.09 | 54.32 | 57.25 |
| SASF | 2019-02-04 | -0.64 | 3.61 | 3.91 | -4.67 | -4.28 | 40.80 |
| SFIF | 2021-08-19 | 0.62 | 6.63 | 14.11 | 39.83 | 48.78 | 46.40 |
| SFIUF | 2018-03-08 | 0.12 | 6.52 | 8.73 | -2.26 | -9.70 | 73.20 |
| UCBAML | 2021-04-08 | 3.47 | 3.57 | 7.43 | 14.62 | 11.98 | 25.05 |
| UCBIPF | 2023-07-13 | 0.55 | 8.82 | 10.14 | 47.75 | 50.55* | 50.55 |
| UCBTGF | 2022-04-07 | -0.40 | 1.63 | 0.40 | 5.32 | 9.46* | 9.46 |
| VIPBAIUF | 2016-07-21 | 3.23 | 5.47 | 8.83 | 29.73 | 30.39 | 113.98 |
| VIPBBF | 2020-12-17 | 2.54 | 4.71 | 9.62 | 33.21 | 37.18 | 64.75 |
| VIPBFIF | 2024-09-05 | 1.65 | 12.67 | 14.96 | 47.34* | 47.34* | 47.34 |
| VIPBGF | 2018-03-01 | 2.90 | 5.48 | 9.35 | 29.19 | 31.23 | 68.43 |
| VIPBNLI | 2012-02-23 | 3.28 | 5.35 | 9.36 | 31.14 | 30.71 | 305.13 |
| VIPBSEBL | 2011-05-26 | 3.36 | 5.64 | 9.06 | 31.54 | 37.59 | 323.62 |

All values come from the SQL in section 3 (26 Oct 2026). For EFUF, Ekush publishes the same figures for its fund page (+17.25% 1Y, +43.11% 3Y, +44.93% 5Y, +130.31% since inception).

---

## 7. Rules

1. **Read only `adjusted_nav`.**
2. **Use only series rows:** on or after `series_start_date`, with `is_glitch = 0`.
3. **T is the latest NAV date**, not today. Ranges count back from T.
4. **The base is the latest NAV on or before the cutoff.** Older years have weekly NAVs, so a base can be a few days earlier than the exact cutoff.
5. **A fund younger than the range uses t0** and is labelled "Since launch". All time always uses t0.
6. **Round only for display.** Keep full precision in calculations.
