#!/usr/bin/env python3 """Batteries-included personal-assistant budget model.""" from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.comments import Comment BLUE = Font(name="Arial", color="0000FF") # hardcoded inputs BLACK = Font(name="Arial", color="000000") # formulas GREEN = Font(name="Arial", color="008000") # cross-sheet links HEAD = Font(name="Arial", bold=True, color="FFFFFF") TITLE = Font(name="Arial", bold=True, size=14, color="1A1230") SUB = Font(name="Arial", bold=True, size=11, color="7A5AA6") BOLD = Font(name="Arial", bold=True) NOTE = Font(name="Arial", italic=True, size=9, color="666666") HFILL = PatternFill("solid", fgColor="3A2A5C") YEL = PatternFill("solid", fgColor="FFF2B2") GREYF = PatternFill("solid", fgColor="EFEAF6") GOOD = PatternFill("solid", fgColor="D8F5E8") WARN = PatternFill("solid", fgColor="FBD9D9") thin = Side(style="thin", color="D0C8E0") BORD = Border(left=thin, right=thin, top=thin, bottom=thin) USD = '$#,##0.00;($#,##0.00);-' USD0 = '$#,##0;($#,##0);-' NUM = '#,##0' PCT = '0%' wb = Workbook() def style_row(ws, row, c0, c1, fill=None, font=None, border=True): for c in range(c0, c1+1): cell = ws.cell(row=row, column=c) if fill: cell.fill = fill if font: cell.font = font if border: cell.border = BORD # ============================================================ # 1) README / BOTTOM LINE # ============================================================ ws = wb.active ws.title = "Read me" ws.sheet_view.showGridLines = False ws.column_dimensions['A'].width = 3 ws.column_dimensions['B'].width = 104 def line(r, text, font=None, fill=None): c = ws.cell(row=r, column=2, value=text) c.font = font or Font(name="Arial", size=10, color="222222") c.alignment = Alignment(wrap_text=True, vertical="top") if fill: c.fill = fill return c ws.cell(row=1, column=2, value="Batteries-Included Personal Assistant — Cost Model").font = TITLE line(2, "Baked-in API quota · your own Telegram bot · lean serverless backend + persistence. Prices current July 2026.", NOTE) line(4, "THE BOTTOM LINE (50 users)", SUB) line(5, "• Fixed infrastructure is trivial: ~$45/month for the whole fleet (bot + Postgres + storage + queue), or ~$12/month on a single Hetzner box.") line(6, "• The ENTIRE cost is inference, and it is driven by how hard users push the agent — not by how many users you have.") line(7, "• With the cheap workhorse (DeepSeek V3.2) and light Telegram-style usage: ~$80/month all-in for 50 users (~$1.60/user).") line(8, "• Same 50 users, but moderate mixed usage: ~$225/mo on DeepSeek, ~$1,280/mo on Gemini 3 Flash.") line(9, "• If users run heavy agentic sessions (like the Hermes run): $2,000–$12,000+/month for 50 users. This is the tail that bankrupts you.") line(11, "THE ONE RULE", SUB) line(12, "Ship a hard per-user monthly token cap. Without it, a single power user on a heavy agentic loop can out-cost your entire fleet. " "With a cap, 'generous' is cheap: a 30M-token/month allowance (very roomy for chat + light tools) costs about $3.50/user on DeepSeek " "or ~$25/user on Gemini 3 Flash.", fill=YEL) line(14, "RECOMMENDATION", SUB) line(15, "1. Default model = DeepSeek V3.2 (workhorse) with a smart-router escalation to Gemini 3 Flash / Haiku for hard tasks.") line(16, "2. Free tier = 30M tokens/month/user, hard-capped; overage either blocked or billed at cost + margin.") line(17, "3. Backend = Railway (bot + Postgres) to start; migrate the bot to a single Hetzner CX22 once you exceed ~$50/mo of Railway compute.") line(18, "4. Budget $150/month to comfortably carry the first ~50 users at light-to-moderate usage, plus a metered overage pool for the heavy tail.") line(20, "Tabs: Assumptions (edit the blue cells) → Per-User Cost → Fleet Cost → Quota Sizing.", NOTE) line(21, "Not financial advice — an engineering cost estimate. Provider prices move; re-check the sources in the Assumptions tab before committing spend.", NOTE) # ============================================================ # 2) ASSUMPTIONS # ============================================================ a = wb.create_sheet("Assumptions") a.sheet_view.showGridLines = False for col,w in {'A':30,'B':18,'C':16,'D':16,'E':16,'F':30}.items(): a.column_dimensions[col].width = w a['A1'] = "Assumptions — edit blue cells"; a['A1'].font = TITLE # model prices a['A3'] = "MODEL PRICES ($ per 1M tokens)"; a['A3'].font = SUB for i,h in enumerate(["Model","Input $/M","Output $/M"]): cell = a.cell(row=4, column=1+i, value=h); cell.font = HEAD; cell.fill = HFILL; cell.border=BORD models = [("Gemini 3 Flash Preview",0.50,3.00), ("DeepSeek V3.2",0.14,0.28), ("Claude Haiku 4.5",1.00,5.00)] for r,(m,pin,pout) in enumerate(models, start=5): a.cell(row=r,column=1,value=m).font=BLACK a.cell(row=r,column=2,value=pin).font=BLUE a.cell(row=r,column=3,value=pout).font=BLUE a.cell(row=r,column=2).number_format=USD a.cell(row=r,column=3).number_format=USD for c in range(1,4): a.cell(row=r,column=c).border=BORD a.cell(row=5,column=2).comment = Comment("Source: Google AI Studio, Jul 2026, ai.google.dev/gemini-api/docs/pricing","budget") a.cell(row=6,column=2).comment = Comment("Source: DeepSeek API docs, 2026, api-docs.deepseek.com (V3.2 unified). Off-peak -50%, cache -90%.","budget") a.cell(row=7,column=2).comment = Comment("Source: Claude Platform pricing, 2026, platform.claude.com/docs/en/about-claude/pricing","budget") a['A8'] = "Cache discount on cached input tokens"; a['A8'].font=BLACK a['B8'] = 0.90; a['B8'].font=BLUE; a['B8'].number_format=PCT a['B8'].comment = Comment("Prompt-cache hits are ~90% cheaper than fresh input across all three providers.","budget") # usage tiers a['A10'] = "USAGE TIERS (per user)"; a['A10'].font = SUB for i,h in enumerate(["Tier","Tokens/user/day","Input share","Cache-hit share","Active days/mo"]): cell=a.cell(row=11,column=1+i,value=h); cell.font=HEAD; cell.fill=HFILL; cell.border=BORD tiers=[("Light (chat)",150000,0.70,0.20,25), ("Moderate (mixed)",1200000,0.80,0.50,25), ("Heavy (agentic)",20000000,0.90,0.60,25)] for r,(t,tok,ins,cache,days) in enumerate(tiers, start=12): a.cell(row=r,column=1,value=t).font=BLACK a.cell(row=r,column=2,value=tok).font=BLUE; a.cell(row=r,column=2).number_format=NUM a.cell(row=r,column=3,value=ins).font=BLUE; a.cell(row=r,column=3).number_format=PCT a.cell(row=r,column=4,value=cache).font=BLUE; a.cell(row=r,column=4).number_format=PCT a.cell(row=r,column=5,value=days).font=BLUE; a.cell(row=r,column=5).number_format=NUM for c in range(1,6): a.cell(row=r,column=c).border=BORD a.cell(row=12,column=2).comment=Comment("Light = Telegram Q&A + occasional task. Heavy = long autonomous coding/research loops (the Hermes session burned ~48K tokens in minutes).","budget") # infra a['A16'] = "INFRA — lean serverless ($/month, fleet-fixed)"; a['A16'].font=SUB infra=[("Bot + agent service (Railway Pro seat)",20), ("Managed Postgres (small)",10), ("Object storage (R2/S3-class)",5), ("Redis / job queue",8), ("Domain + misc",2)] r0=17 for r,(t,v) in enumerate(infra, start=r0): a.cell(row=r,column=1,value=t).font=BLACK a.cell(row=r,column=2,value=v).font=BLUE; a.cell(row=r,column=2).number_format=USD0 a.cell(row=r,column=1).border=BORD; a.cell(row=r,column=2).border=BORD rtot=r0+len(infra) a.cell(row=rtot,column=1,value="Total fixed infra / month").font=BOLD tc=a.cell(row=rtot,column=2,value=f"=SUM(B{r0}:B{rtot-1})"); tc.font=BLACK; tc.number_format=USD0; tc.fill=GREYF; tc.border=BORD a.cell(row=rtot,column=1).fill=GREYF; a.cell(row=rtot,column=1).border=BORD a.cell(row=rtot+1,column=1,value="Alt: single Hetzner CX22, self-hosted").font=BLACK av=a.cell(row=rtot+1,column=2,value=12); av.font=BLUE; av.number_format=USD0 av.comment=Comment("Source: Hetzner, Jul 2026 — CX22 2vCPU/4GB now ~EUR8.39/mo (~$9) + storage/backup. hetzner.com/cloud","budget") a['A25'] = "USER COUNTS"; a['A25'].font=SUB for i,v in enumerate([25,50,75]): cell=a.cell(row=26,column=2+i,value=v); cell.font=BLUE; cell.number_format=NUM; cell.border=BORD a.cell(row=26,column=1,value="Users").font=BLACK # named refs used by other sheets FIX = f"Assumptions!$B${rtot}" CACHE = "Assumptions!$B$8" U25,U50,U75 = "Assumptions!$B$26","Assumptions!$C$26","Assumptions!$D$26" # model price cells MP = {"Gemini 3 Flash":("Assumptions!$B$5","Assumptions!$C$5"), "DeepSeek V3.2":("Assumptions!$B$6","Assumptions!$C$6"), "Claude Haiku 4.5":("Assumptions!$B$7","Assumptions!$C$7")} # tier param cells: day, inshare, cache, days TP = {"Light":("Assumptions!$B$12","Assumptions!$C$12","Assumptions!$D$12","Assumptions!$E$12"), "Moderate":("Assumptions!$B$13","Assumptions!$C$13","Assumptions!$D$13","Assumptions!$E$13"), "Heavy":("Assumptions!$B$14","Assumptions!$C$14","Assumptions!$D$14","Assumptions!$E$14")} # ============================================================ # 3) PER-USER COST # ============================================================ p = wb.create_sheet("Per-User Cost") p.sheet_view.showGridLines=False heads=["Model","Tier","Tokens/day","Active days","Monthly tokens","Input share", "Output tokens","Input tokens","Cache-hit","Billed input","In $/M","Out $/M","Cost $/user/mo"] widths=[22,12,13,11,15,11,14,14,10,14,9,9,15] for i,(h,w) in enumerate(zip(heads,widths),start=1): p.column_dimensions[chr(64+i)].width=w cell=p.cell(row=2,column=i,value=h); cell.font=HEAD; cell.fill=HFILL; cell.border=BORD cell.alignment=Alignment(wrap_text=True,vertical="center",horizontal="center") p.cell(row=1,column=1,value="Per-user monthly inference cost").font=TITLE combos=[(mn,tn) for mn in ["Gemini 3 Flash","DeepSeek V3.2","Claude Haiku 4.5"] for tn in ["Light","Moderate","Heavy"]] r=3 percell={} for mn,tn in combos: pin,pout=MP[mn]; day,ins,cache,days=TP[tn] p.cell(row=r,column=1,value=mn).font=BLACK p.cell(row=r,column=2,value=tn).font=BLACK p.cell(row=r,column=3,value=f"={day}").font=GREEN; p.cell(row=r,column=3).number_format=NUM p.cell(row=r,column=4,value=f"={days}").font=GREEN; p.cell(row=r,column=4).number_format=NUM p.cell(row=r,column=5,value=f"=C{r}*D{r}").font=BLACK; p.cell(row=r,column=5).number_format=NUM p.cell(row=r,column=6,value=f"={ins}").font=GREEN; p.cell(row=r,column=6).number_format=PCT p.cell(row=r,column=7,value=f"=E{r}*(1-F{r})").font=BLACK; p.cell(row=r,column=7).number_format=NUM p.cell(row=r,column=8,value=f"=E{r}*F{r}").font=BLACK; p.cell(row=r,column=8).number_format=NUM p.cell(row=r,column=9,value=f"={cache}").font=GREEN; p.cell(row=r,column=9).number_format=PCT p.cell(row=r,column=10,value=f"=H{r}*(1-I{r}*{CACHE})").font=BLACK; p.cell(row=r,column=10).number_format=NUM p.cell(row=r,column=11,value=f"={pin}").font=GREEN; p.cell(row=r,column=11).number_format=USD p.cell(row=r,column=12,value=f"={pout}").font=GREEN; p.cell(row=r,column=12).number_format=USD mcell=p.cell(row=r,column=13,value=f"=J{r}*K{r}/1000000+G{r}*L{r}/1000000") mcell.font=BOLD; mcell.number_format=USD; mcell.fill=GREYF for c in range(1,14): p.cell(row=r,column=c).border=BORD percell[(mn,tn)]=f"'Per-User Cost'!$M${r}" r+=1 # ============================================================ # 4) FLEET COST # ============================================================ f = wb.create_sheet("Fleet Cost") f.sheet_view.showGridLines=False for col,w in {'A':22,'B':14,'C':17,'D':17,'E':17,'F':15}.items(): f.column_dimensions[col].width=w f.cell(row=1,column=1,value="Total monthly cost = fixed infra + users x per-user").font=TITLE f.cell(row=2,column=1,value=f"Fixed infra ($/mo):").font=BLACK fx=f.cell(row=2,column=2,value=f"={FIX}"); fx.font=GREEN; fx.number_format=USD0 heads2=["Model / Tier","$/user/mo","25 users","50 users","75 users","$/user @50"] for i,h in enumerate(heads2,start=1): cell=f.cell(row=4,column=i,value=h); cell.font=HEAD; cell.fill=HFILL; cell.border=BORD cell.alignment=Alignment(wrap_text=True,horizontal="center",vertical="center") r=5 for mn,tn in combos: f.cell(row=r,column=1,value=f"{mn} · {tn}").font=BLACK f.cell(row=r,column=2,value=f"={percell[(mn,tn)]}").font=GREEN; f.cell(row=r,column=2).number_format=USD f.cell(row=r,column=3,value=f"=$B$2+{U25}*B{r}").font=BLACK; f.cell(row=r,column=3).number_format=USD0 f.cell(row=r,column=4,value=f"=$B$2+{U50}*B{r}").font=BLACK; f.cell(row=r,column=4).number_format=USD0 f.cell(row=r,column=5,value=f"=$B$2+{U75}*B{r}").font=BLACK; f.cell(row=r,column=5).number_format=USD0 f.cell(row=r,column=6,value=f"=D{r}/{U50}").font=BLACK; f.cell(row=r,column=6).number_format=USD fill = GOOD if (tn=="Light") else (WARN if tn=="Heavy" else None) for c in range(1,7): f.cell(row=r,column=c).border=BORD if fill: f.cell(row=r,column=c).fill=fill r+=1 f.cell(row=r+1,column=1,value="Green = safe to give away · Red = must be metered/capped").font=NOTE # ============================================================ # 5) QUOTA SIZING # ============================================================ q = wb.create_sheet("Quota Sizing") q.sheet_view.showGridLines=False for col,w in {'A':26,'B':16,'C':16,'D':16,'E':16}.items(): q.column_dimensions[col].width=w q.cell(row=1,column=1,value="What a 'generous free quota' costs you").font=TITLE q.cell(row=3,column=1,value="Monthly token cap / user").font=BLACK q.cell(row=3,column=2,value=30000000).font=BLUE; q.cell(row=3,column=2).number_format=NUM q.cell(row=3,column=2).comment=Comment("A hard cap. 30M tokens/mo is very roomy for chat + light tools; ~1M tokens/day of usage.","budget") q.cell(row=4,column=1,value="Input share").font=BLACK q.cell(row=4,column=2,value=0.80).font=BLUE; q.cell(row=4,column=2).number_format=PCT q.cell(row=5,column=1,value="Cache-hit share").font=BLACK q.cell(row=5,column=2,value=0.50).font=BLUE; q.cell(row=5,column=2).number_format=PCT for i,h in enumerate(["Model","Cost/user @cap","50 users infra+quota","Give away? margin left"],start=1): cell=q.cell(row=7,column=i,value=h); cell.font=HEAD; cell.fill=HFILL; cell.border=BORD cell.alignment=Alignment(wrap_text=True,horizontal="center",vertical="center") qmodels=[("Gemini 3 Flash","Assumptions!$B$5","Assumptions!$C$5"), ("DeepSeek V3.2","Assumptions!$B$6","Assumptions!$C$6"), ("Claude Haiku 4.5","Assumptions!$B$7","Assumptions!$C$7")] r=8 for mn,pin,pout in qmodels: q.cell(row=r,column=1,value=mn).font=BLACK # billed input = cap*inshare*(1-cache*disc); out = cap*(1-inshare) q.cell(row=r,column=2, value=f"=($B$3*$B$4*(1-$B$5*{CACHE}))*{pin}/1000000+($B$3*(1-$B$4))*{pout}/1000000") q.cell(row=r,column=2).font=BLACK; q.cell(row=r,column=2).number_format=USD q.cell(row=r,column=3,value=f"={FIX}+{U50}*B{r}").font=BLACK; q.cell(row=r,column=3).number_format=USD0 q.cell(row=r,column=4,value=f"=IF(B{r}<=5,\"yes — trivial\",IF(B{r}<=30,\"ok with margin\",\"cap tighter\"))").font=BLACK for c in range(1,5): q.cell(row=r,column=c).border=BORD r+=1 q.cell(row=r+1,column=1,value="Rule: pick the cap so cost/user @cap is a number you'd happily eat as CAC. DeepSeek lets you be very generous.").font=NOTE wb.save("agent_budget_model.xlsx") print("saved agent_budget_model.xlsx")