siddharthd
d06088fe34
ci / lint-test (push) Successful in 37s
One-off analysis, nothing built. Realistic baseline $4,140/mo -> $24,800 for six months, against $89,770 already accessible ($81,017 loan redraw + $8,753 offset). Records four corrections the raw data needs before any restatement: misfiled Raiz/Vanguard/moomoo debits counted as spend, `other` credits read as negative spend, `government` conflating ATO with rates/rego, and `fees` being mostly annual. CLAUDE.md gains two traps found while doing it: partial split coverage inside a category is usually correct rather than a gap (only shared utilities and subscriptions are split), and the loan repayment is voluntarily above contracted ($2,500 vs $1,190.54 per fortnight) with the difference recoverable via redraw.
212 lines
9.9 KiB
Markdown
212 lines
9.9 KiB
Markdown
# Monthly expense baseline and emergency reserve
|
||
|
||
Analysis run 2026-07-26. **One-off analysis, not a feature** — nothing in the app
|
||
computes these numbers. Read "Reproducing this" before trusting a restated figure.
|
||
|
||
The question: how much should be held in reserve to cover 6–12 months of expenses?
|
||
|
||
## Answer
|
||
|
||
| Scenario | $/mo | 6 months | 12 months |
|
||
|---|---:|---:|---:|
|
||
| Survival — contracted loan repayment, essentials only | 3,040 | 18,250 | 36,500 |
|
||
| **Realistic — contracted loan, + dining and charity** | **4,140** | **24,800** | **49,600** |
|
||
| Status quo — keep overpaying the loan, normal life ex-travel | 5,557 | 33,500 | 67,000 |
|
||
|
||
Use the middle row. The survival row assumes dining is cut to zero and stays
|
||
there, which is not a plan anyone executes for six months.
|
||
|
||
Against that, liquidity already available (statements 133 and 131, 2026-06-30):
|
||
|
||
| | |
|
||
|---|---:|
|
||
| Loan redraw | 81,017.42 |
|
||
| Offset balance | 8,753.00 |
|
||
| **Accessible** | **89,770.42** |
|
||
|
||
That is 3.6× the six-month target and 1.8× the twelve-month one. Redraw grew
|
||
$62,387.17 → $81,017.42 across the last two loan statements, matching the
|
||
overpayment rate — the money spent killing the loan faster is still reachable.
|
||
|
||
**Caveat on counting redraw as the reserve.** It is available at AMP's
|
||
discretion, and lenders reduce or freeze it exactly when a borrower looks
|
||
distressed — which is when it would be needed. The rate also moved 5.54% → 6.29%
|
||
between the two statements, so redrawn funds cost more than they did. Hold some
|
||
genuine cash; it does not need to be $50,000.
|
||
|
||
## This is *your* outgoings, not household spend
|
||
|
||
The app only sees accounts that get imported. Sonu's own spending on the
|
||
household is invisible to it. Grocery spend reads as ~$300/mo gross on 4–10
|
||
transactions, which is implausible for a household and is partly explained by her
|
||
paying from her own account.
|
||
|
||
For "how much reserve do **I** need" that blind spot does not matter — your own
|
||
outgoings is the correct measure. Do not relabel these figures as household
|
||
totals; they are not, and they would be wrong by an unknown amount.
|
||
|
||
## The loan has two floors
|
||
|
||
This is the largest single lever and the reason there are three scenarios.
|
||
|
||
| | $/fortnight | $/mo annualised | Your 50% |
|
||
|---|---:|---:|---:|
|
||
| Contracted minimum | 1,190.54 | 2,579.50 | **1,290** |
|
||
| Actual direct debit | 2,500.00 | 5,416.67 | **2,708** |
|
||
| Voluntary overpayment | 1,309.46 | 2,837.17 | 1,419 |
|
||
|
||
Dropping to contracted cuts your loan cost by $1,418/mo. Sonu's obligation is a
|
||
fixed 50% of the repayment (see CLAUDE.md → "The shared loan"), so it falls with
|
||
it. Her rate returned to $1,250/fortnight in July 2026 after the reduced $750
|
||
period during her leave.
|
||
|
||
## Baseline composition
|
||
|
||
Built from **Feb–Jun 2026** — the months where split data is trustworthy — with
|
||
annual items annualised over 12 rather than divided by the 5-month sample.
|
||
|
||
| Essential | $/mo | Note |
|
||
|---|---:|---|
|
||
| Loan (contracted, your 50%) | 1,290 | 2,708 at the current actual rate |
|
||
| Transport | 326 | |
|
||
| Insurance | 280 | annualised; your 55% |
|
||
| Utilities | 278 | shared energy/water + personal mobile |
|
||
| Subscriptions | 204 | shared household + personal/AI |
|
||
| Groceries | 200 | see the blind-spot note above |
|
||
| Card + package fees | 175 | annualised — see below |
|
||
| Health | 167 | |
|
||
| Rates + rego | 122 | annualised, your share |
|
||
| **Essential** | **3,042** | |
|
||
| + dining 675, charity 422 | 4,139 | charity is a Smith Family sponsorship commitment |
|
||
| + typical shopping | ~4,800 | median 657, **not** the 1,709 mean |
|
||
|
||
**Travel is excluded throughout.** At $3,153/mo of your share even post-cutover it
|
||
would roughly double every figure, and it is the first thing that stops.
|
||
|
||
## Four corrections the raw data needed
|
||
|
||
Any restatement that skips these will be wrong. None are fixed in the data yet.
|
||
|
||
**Micro-investing counted as spend.** Raiz ($9,406 / 27 rows), Vanguard Super
|
||
($500) and moomoo ($300) sit in `other` as debits — $10,206/yr, ~$850/mo of
|
||
phantom spend. These belong in `investment`, which is already excluded.
|
||
|
||
**Incoming money counted as negative spend.** 17 rows in `other` typed `credit`
|
||
($5,622 in the window). `SPEND_SIGNED` negates credits so refunds cancel
|
||
purchases, but these are not refunds — they are money arriving. June 2025 shows
|
||
*minus* $7,814 of total spend because two Wise credits of ~$16.7k each landed in
|
||
`other`.
|
||
|
||
**`government` is two unrelated things.** $25,554 of ATO payments (one annual
|
||
bill, routed through Zen B2B and RewardPay to earn points) versus $2,054 council
|
||
rates and $875 rego. Tax is not a monthly living cost and falls with income
|
||
anyway; rates and rego are non-negotiable. Splitting them moves this line from
|
||
$2,411/mo to $244/mo.
|
||
|
||
**`fees` is mostly annual.** Of $2,599 post-cutover, $1,750 is an annual card fee
|
||
and $349 a loan package annual fee. Recurring is ~$175/mo annualised, not the
|
||
$520/mo the 5-month mean implies.
|
||
|
||
## Splits: what is trustworthy and what is not
|
||
|
||
Splits exist in this app from **2026-01-09** only; before that they were tracked
|
||
in SplitMyExpenses. So a trailing-12-month per-person series splices six months
|
||
of *gross* onto six months of *net* and is not a series at all. Use Feb–Jun 2026.
|
||
|
||
**Partial split coverage within a category is usually correct, not a gap.** This
|
||
was misdiagnosed once during the analysis. Verified composition:
|
||
|
||
| Category | Your share | Split | Unsplit |
|
||
|---|---:|---|---|
|
||
| utilities | 69% | Globird, OVO, GWW, home telecoms | Telstra, Vodafone, Optus, JB Hi-Fi Mobile |
|
||
| subscriptions | 91% | Uber One, Amazon Prime, OnePass | Claude, OpenAI, Anthropic, OpenRouter, You.com, LinkedIn, Xero, Billdu, Spotify, Patreon |
|
||
| fees | 100% | — | credit card fees are personal |
|
||
| charity | 100% | — | personal commitment |
|
||
|
||
Only *shared* utilities and subscriptions are split. Mobile bills, AI
|
||
subscriptions and card fees are personal and correctly sit at 100%. Do not
|
||
"fix" these ratios.
|
||
|
||
## Known weak spots
|
||
|
||
- **`other` is $424–773/mo of your share and unclassified.** The single biggest
|
||
lever on accuracy. Clearing it with the rule preview
|
||
(`GET /api/rules/[id]/matches`) improves every other view at the same time.
|
||
- **Five months is a short sample**, and it contains the Europe trip and the ATO
|
||
bill. Both are excluded, but they crowd out the ordinary months. The
|
||
SplitMyExpenses CSVs would stretch it to 18 months — worth having, not
|
||
blocking, since only an *aggregate ratio per category* is needed, not row
|
||
matching, so the combined-transaction problem does not bite.
|
||
- **`/api/analytics/monthly` does not filter `reconciled_with_id IS NOT NULL`.**
|
||
48 rows are double-counted app-wide. Small, but real, and unrelated to this
|
||
analysis. The queries below do filter it.
|
||
|
||
## Reproducing this
|
||
|
||
Working queries are not checked in; they were run ad hoc against
|
||
`postgres-personal`. The shape that matters:
|
||
|
||
```sql
|
||
-- Per-category monthly distribution, your share, Feb-Jun 2026.
|
||
-- Mirrors src/lib/analytics-sql.ts, plus the corrections above.
|
||
WITH s AS (
|
||
SELECT to_char(t.transaction_date,'YYYY-MM') m,
|
||
COALESCE(o.category_override, t.category, 'other') cat,
|
||
SUM((CASE WHEN t.transaction_type IN ('refund','credit') THEN -1 ELSE 1 END)
|
||
* (CASE WHEN t.interest_amount IS NOT NULL
|
||
THEN t.interest_amount ELSE COALESCE(t.amount_aud, t.amount) END)
|
||
* COALESCE(ts.share_percent, o.my_share_percent,
|
||
100 - COALESCE((SELECT SUM(x.share_percent) FROM transaction_splits x
|
||
WHERE x.transaction_id = t.id
|
||
AND x.participant_id <> 1), 0)) / 100)::numeric(12,2) amt
|
||
FROM transactions t
|
||
LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
|
||
LEFT JOIN transaction_splits ts ON ts.transaction_id = t.id AND ts.participant_id = 1
|
||
LEFT JOIN statements st ON st.id = t.statement_id
|
||
WHERE COALESCE(t.owner_id, st.owner_id) = 1
|
||
AND t.reconciled_with_id IS NULL -- see weak spots
|
||
AND ((t.transaction_type IN ('debit','fee','interest')
|
||
OR t.interest_amount IS NOT NULL)
|
||
OR t.transaction_type IN ('refund','credit'))
|
||
AND COALESCE(o.category_override, t.category, 'other')
|
||
NOT IN ('transfers','investment','income')
|
||
-- correction 1: misfiled investments
|
||
AND COALESCE(t.merchant_normalized, t.merchant_name, '')
|
||
!~* '^(Raiz|Vanguard Super|moomoo)'
|
||
-- correction 2: incoming money miscategorised as 'other' credits
|
||
AND NOT (COALESCE(o.category_override, t.category, 'other') = 'other'
|
||
AND t.transaction_type = 'credit')
|
||
AND t.transaction_date >= '2026-02-01' AND t.transaction_date < '2026-07-01'
|
||
GROUP BY 1, 2
|
||
)
|
||
SELECT cat, count(*) mths, round(avg(amt),0) mean,
|
||
round(percentile_cont(0.5) WITHIN GROUP (ORDER BY amt)::numeric, 0) median
|
||
FROM s GROUP BY cat ORDER BY mean DESC;
|
||
```
|
||
|
||
Use the **median** for anything recurring monthly and the **annualised total** for
|
||
lumpy-but-certain items (insurance, rates, rego, annual card fees). Means are
|
||
badly skewed here — shopping is 1,709 mean against 657 median.
|
||
|
||
Corrections 3 and 4 (`government`, `fees`) are not expressible as filters; they
|
||
need the category split by merchant, done by hand above.
|
||
|
||
## If this becomes a feature
|
||
|
||
The blocker is `other`, not the split model — the split data held up under
|
||
scrutiny. Sequence:
|
||
|
||
1. Recategorise Raiz / Vanguard Super / moomoo to `investment` (27 rows,
|
||
unambiguous) via the rule preview.
|
||
2. Split `government` so rates and rego separate from ATO. Probably a new
|
||
category or a tag; `government` conflates a recurring obligation with an
|
||
annual tax bill.
|
||
3. Work `other` down with the rule preview.
|
||
4. Then a baseline panel on Insights, which already carries the
|
||
Regular/Occasional split this analysis is a stricter version of
|
||
(`REGULAR_CATEGORIES` in `src/lib/categories.ts`).
|
||
|
||
A **liquidity vs baseline** view would be genuinely new rather than a restatement:
|
||
`statements.redraw_available`, offset closing balance, and the
|
||
contracted-vs-actual repayment gap are all in the schema and nothing reads them.
|