What Is ALM and Why Should You Care?
ALM — Asset-Liability Management — is how banks avoid going bankrupt. They model every asset (loans they've made, securities they hold) alongside every liability (deposits, bonds they've issued) and stress-test the relationship between them.
Your personal finances have the same structure. You just never drew the diagram.
YOU, INC.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ASSETS LIABILITIES
├─ Stocks (portfolio) ├─ Margin loan
├─ Cash reserves ├─ Credit lines
├─ Real estate ├─ Monthly expenses (recurring)
├─ Future dividends ├─ Taxes (deferred)
└─ Labor income (opt.) └─ Inflation (hidden)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
NET WORTH = Δ
Most people track the left side. This series tracks both.
The Schema
We start with SQLite — no servers, no dependencies, one file forever. This is the database that every subsequent article in this series will read from.
# alm_schema.py
import sqlite3
from datetime import date
DB_PATH = "life_alm.db"
SCHEMA = """
-- ASSETS: What you own
CREATE TABLE IF NOT EXISTS holdings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ticker TEXT NOT NULL,
name TEXT,
shares INTEGER NOT NULL,
cost_basis REAL, -- total cost, not per-share
asset_class TEXT DEFAULT 'equity',
added_at TEXT DEFAULT (date('now'))
);
-- Market prices (daily snapshot)
CREATE TABLE IF NOT EXISTS prices (
ticker TEXT NOT NULL,
price_date TEXT NOT NULL,
close_price REAL NOT NULL,
PRIMARY KEY (ticker, price_date)
);
-- Dividend history and projections
CREATE TABLE IF NOT EXISTS dividends (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ticker TEXT NOT NULL,
ex_date TEXT,
pay_date TEXT,
amount_per_share REAL NOT NULL,
is_projection INTEGER DEFAULT 0 -- 1 = forecast, 0 = actual
);
-- LIABILITIES: What you owe
CREATE TABLE IF NOT EXISTS loans (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL, -- 'securities_margin', 'credit_line', etc.
balance REAL NOT NULL,
rate REAL NOT NULL, -- annual interest rate (0.02 = 2%)
collateral_type TEXT, -- 'portfolio', 'real_estate', NULL (unsecured)
margin_call_pct REAL, -- ratio that triggers margin call (e.g., 0.70)
forced_liq_pct REAL, -- ratio that triggers forced liquidation (e.g., 0.85)
freeze_pct REAL, -- ratio that freezes new borrowing (e.g., 0.60)
updated_at TEXT DEFAULT (date('now'))
);
-- BURN RATE: Recurring liabilities
CREATE TABLE IF NOT EXISTS expenses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category TEXT NOT NULL, -- 'housing', 'food', 'insurance', 'tax', etc.
monthly_amount REAL NOT NULL,
is_fixed INTEGER DEFAULT 1, -- 1 = fixed, 0 = variable
notes TEXT
);
-- INCOME: Active earnings (optional/side-FIRE)
CREATE TABLE IF NOT EXISTS income (
id INTEGER PRIMARY KEY AUTOINCREMENT,
source TEXT NOT NULL, -- 'ai_consulting', 'spouse', etc.
monthly_amount REAL NOT NULL,
is_guaranteed INTEGER DEFAULT 0,
notes TEXT
);
-- SNAPSHOTS: Daily balance sheet
CREATE TABLE IF NOT EXISTS snapshots (
snapshot_date TEXT PRIMARY KEY,
total_assets REAL,
total_liabilities REAL,
net_worth REAL,
margin_ratio REAL, -- total_loan_balance / portfolio_value
monthly_cashflow REAL,
fire_ratio REAL -- passive_income / expenses
);
"""
def init_db():
conn = sqlite3.connect(DB_PATH)
conn.executescript(SCHEMA)
conn.close()
print(f"Database initialized: {DB_PATH}")
if __name__ == "__main__":
init_db()
Run it once. You now have a financial database.
$ python alm_schema.py
Database initialized: life_alm.db
Loading Your Data
Here's a loader that populates the database with your actual positions. Replace the numbers with your own.
# load_portfolio.py
import sqlite3
from alm_schema import DB_PATH
def load_sample_data():
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
# === HOLDINGS ===
holdings = [
("2674", "Hard Off Corp", 15000, 22_500_000, "equity"),
("8291", "Nissan Tokyo HD", 50000, 20_000_000, "equity"),
("5869", "Waseda Gakken", 20000, 18_000_000, "equity"),
("2411", "Gendai Agency", 50000, 15_000_000, "equity"),
("7201", "Nissan Motor", 50000, 12_000_000, "equity"),
]
c.executemany(
"INSERT INTO holdings (ticker, name, shares, cost_basis, asset_class) VALUES (?,?,?,?,?)",
holdings
)
# === CURRENT PRICES ===
prices = [
("2674", "2026-04-08", 2057.0),
("8291", "2026-04-08", 515.0),
("5869", "2026-04-08", 1326.0),
("2411", "2026-04-08", 475.0),
("7201", "2026-04-08", 365.2),
]
c.executemany(
"INSERT INTO prices (ticker, price_date, close_price) VALUES (?,?,?)",
prices
)
# === LOANS ===
loans = [
("securities_margin", 50_000_000, 0.02, "portfolio", 0.70, 0.85, 0.60),
("consumer_credit", 8_000_000, 0.02, None, None, None, None),
]
c.executemany(
"""INSERT INTO loans (name, balance, rate, collateral_type,
margin_call_pct, forced_liq_pct, freeze_pct) VALUES (?,?,?,?,?,?,?)""",
loans
)
# === MONTHLY EXPENSES ===
expenses = [
("housing", 30_000, 1, "Mortgage paid off — maintenance only"),
("food", 100_000, 0, None),
("insurance", 50_000, 1, None),
("utilities", 30_000, 1, None),
("transport", 40_000, 0, None),
("education", 100_000, 1, "Children"),
("misc", 150_000, 0, None),
("tax_social", 300_000, 1, "Income tax + social insurance"),
]
c.executemany(
"INSERT INTO expenses (category, monthly_amount, is_fixed, notes) VALUES (?,?,?,?)",
expenses
)
# === INCOME ===
income = [
("ai_consulting", 1_000_000, 0, "Side-FIRE: work by choice"),
("spouse", 200_000, 1, None),
]
c.executemany(
"INSERT INTO income (source, monthly_amount, is_guaranteed, notes) VALUES (?,?,?,?)",
income
)
conn.commit()
conn.close()
print("Portfolio data loaded.")
if __name__ == "__main__":
load_sample_data()
The Balance Sheet Query
Now the payoff — a single query that gives you your complete financial picture:
# balance_sheet.py
import sqlite3
from alm_schema import DB_PATH
def get_balance_sheet(price_date="2026-04-08"):
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
# Total portfolio value
c.execute("""
SELECT SUM(h.shares * p.close_price)
FROM holdings h
JOIN prices p ON h.ticker = p.ticker
WHERE p.price_date = ?
""", (price_date,))
portfolio_value = c.fetchone()[0] or 0
# Cash reserves (simplified — you'd track this separately)
cash = 4_500_000 # or query from a cash table
# Total assets
total_assets = portfolio_value + cash
# Total loan balances
c.execute("SELECT SUM(balance) FROM loans")
total_loans = c.fetchone()[0] or 0
# Monthly expenses
c.execute("SELECT SUM(monthly_amount) FROM expenses")
monthly_expenses = c.fetchone()[0] or 0
# Monthly income
c.execute("SELECT SUM(monthly_amount) FROM income")
monthly_income = c.fetchone()[0] or 0
# Annual dividends (projected)
c.execute("""
SELECT SUM(d.amount_per_share * h.shares)
FROM dividends d
JOIN holdings h ON d.ticker = h.ticker
WHERE d.is_projection = 1
""")
annual_dividends = c.fetchone()[0] or 0
# Derived metrics
net_worth = total_assets - total_loans
margin_ratio = total_loans / portfolio_value if portfolio_value > 0 else float('inf')
monthly_cashflow = monthly_income - monthly_expenses
monthly_passive = annual_dividends / 12
fire_ratio = (annual_dividends + 0) / (monthly_expenses * 12) if monthly_expenses > 0 else 0
# Margin loan specific
c.execute("""
SELECT balance, freeze_pct, margin_call_pct, forced_liq_pct
FROM loans WHERE collateral_type = 'portfolio'
""")
margin_loan = c.fetchone()
conn.close()
report = f"""
╔══════════════════════════════════════════════════╗
║ PERSONAL BALANCE SHEET ║
║ {price_date} ║
╠══════════════════════════════════════════════════╣
║ ASSETS ║
║ Portfolio ¥{portfolio_value:>14,.0f} ║
║ Cash ¥{cash:>14,.0f} ║
║ Total Assets ¥{total_assets:>14,.0f} ║
╠══════════════════════════════════════════════════╣
║ LIABILITIES ║
║ Total Loans ¥{total_loans:>14,.0f} ║
║ Monthly Burn ¥{monthly_expenses:>14,.0f} ║
╠══════════════════════════════════════════════════╣
║ NET WORTH ¥{net_worth:>14,.0f} ║
╠══════════════════════════════════════════════════╣
║ KEY RATIOS ║
║ Margin Ratio {margin_ratio:>8.1%} ║
║ Monthly Cashflow ¥{monthly_cashflow:>14,.0f} ║
║ FIRE Ratio {fire_ratio:>8.1%} ║
╚══════════════════════════════════════════════════╝
"""
print(report)
# Margin safety analysis
if margin_loan:
bal, freeze, call, liq = margin_loan
print("MARGIN SAFETY ANALYSIS")
print("─" * 50)
for label, threshold in [("Freeze", freeze), ("Margin Call", call), ("Forced Liq", liq)]:
if threshold:
trigger_value = bal / threshold
drop_pct = (1 - trigger_value / portfolio_value) * 100
print(f" {label:.<20} ratio>{threshold:.0%} triggers at ¥{trigger_value:,.0f} ({drop_pct:+.1f}%)")
if __name__ == "__main__":
get_balance_sheet()
Running this prints:
MARGIN SAFETY ANALYSIS
──────────────────────────────────────────────────
Freeze.............. ratio>60% triggers at ¥83,333,333 (-33.4%)
Margin Call......... ratio>70% triggers at ¥71,428,571 (-42.9%)
Forced Liq.......... ratio>85% triggers at ¥58,823,529 (-53.0%)
One command. Complete picture. Every morning.
The Snapshot Recorder
Automate daily snapshots with cron. This builds the historical record that powers the trend analysis in later articles.
# snapshot.py
import sqlite3
from datetime import date
from alm_schema import DB_PATH
def record_snapshot():
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
today = date.today().isoformat()
# Get latest prices for each ticker
c.execute("""
SELECT SUM(h.shares * p.close_price)
FROM holdings h
JOIN prices p ON h.ticker = p.ticker
WHERE p.price_date = (SELECT MAX(price_date) FROM prices)
""")
portfolio = c.fetchone()[0] or 0
c.execute("SELECT SUM(balance) FROM loans")
loans = c.fetchone()[0] or 0
c.execute("SELECT SUM(monthly_amount) FROM income")
income = c.fetchone()[0] or 0
c.execute("SELECT SUM(monthly_amount) FROM expenses")
expenses = c.fetchone()[0] or 0
net_worth = portfolio - loans
margin_ratio = loans / portfolio if portfolio > 0 else 0
cashflow = income - expenses
c.execute("""
INSERT OR REPLACE INTO snapshots
(snapshot_date, total_assets, total_liabilities, net_worth,
margin_ratio, monthly_cashflow)
VALUES (?, ?, ?, ?, ?, ?)
""", (today, portfolio, loans, net_worth, margin_ratio, cashflow))
conn.commit()
conn.close()
print(f"Snapshot recorded: {today} | NW: ¥{net_worth:,.0f} | Margin: {margin_ratio:.1%}")
if __name__ == "__main__":
record_snapshot()
Add to cron:
# crontab -e
30 16 * * 1-5 cd /home/user/alm && python snapshot.py
Market closes at 15:00 JST. Snapshot at 16:30 after data settles.
Why SQLite?
You might wonder why not PostgreSQL, or a spreadsheet, or a SaaS tool.
SQLite is the only database that will still work in 20 years without maintenance. It's a single file. No server process. No authentication. No network dependency. Backup is cp. Migration is scp.
For a system that manages your life's finances, that property — zero operational burden — is not a nice-to-have. It's a requirement.
Your FIRE system should be the most boring piece of infrastructure you own. Boring means reliable. Reliable means you trust it. Trust means you act on it.
What We Built
- A 7-table SQLite schema covering assets, liabilities, expenses, and income
- A data loader for real portfolio positions
- A balance sheet generator with margin safety analysis
- A daily snapshot recorder for cron automation
This database is the foundation. Every article that follows reads from it.
Top comments (0)