While browsing in a favorite bookstore with my son, I spotted a display of horoscope themed Christmas tree ornaments. The ornaments were glass balls embossed with golden birth signs like Aquarius, Gemini, Cancer, et cetera, and a descriptive phrase that “summed up” the character of people born under a sign. Below my birth sign golden text declared, “Imaginative and Suspicious.”
I said to my son, “I hate it when astrological rubbish is right.”
I am imaginative and suspicious; it’s a curse. When it comes to money my “suspicious dial” is permanently set on eleven. I assume everyone is out to cheat and defraud me until there is overwhelming evidence to the contrary. Paranoia is generally crippling but when it comes to cold hard cash it’s a sound retention strategy.
Prompted by an eminent life move, I found myself in need of a cash flow forecasting tool. Normal people deal with forecasting problems by buying standard finance programs or cranking up spreadsheets; imaginative and suspicious people roll their own.
SWAG, (Silly Wild Ass Guess), is a hybrid J/EXCEL/GIT mob1 that meets my eccentric needs. I wanted a tool that:
- Abstracted away accounting noise.
- Was general and flexible.
- Used highly portable, durable, and version control friendly inputs and outputs.
- Reflected what ordinary people, not tax accountants, actually do with money.
- Is open source and unencumbered by parasitic software patents.
Amazingly, my short list of no-brainer requirements eliminates most standard finance programs. Time to code!
The bulk of SWAG is a JOD generated self-contained J script. You can peruse the script here. SWAG inputs and outputs are brain-dead simple TAB delimited text tables. Inputs consist of monthly, null-free, numeric time series tables, scenario tables, and name cross-reference tables. Outputs are simple, null-free, numeric time series tables. Input and output time series tables have identical formats.
A few examples will make this clear. The following is a typical SWAG input and output time series table. Note: the following tables have been truncated for this blog. The complete text files are available on GitHub here.
Date E0 E1 E2 E3 E4 E5 E6 E7 E8 EC EF Etotal I0 I1 I2 I3 I4 I5 IC Itotal R0 R1 R2 R3 Rtotal D0 D1 D2 D3 D4 Dtotal BB NW U0 U1 U2 U3 2015-09-01 912 1650.000000000 100 0 50 0.000000000 0 0 0 0 0 2712.000000000 4800.000000000 0 0 0 0 0 0 4800.000000000 130000.000000000 25000.000000000 0.000000000 0 155000.000000000 0.00000000 0.000000000 0 0 0 0.000000000 2088.000000000 157088.000000000 155000 0 0.0000000 0.000000000 2015-10-01 912 1656.875000000 100 0 50 0.000000000 0 0 0 0 0 2718.875000000 4806.000000000 0 0 0 0 0 0 4806.000000000 130054.166666667 25062.500000000 0.000000000 0 155116.666666667 0.00000000 0.000000000 0 0 0 0.000000000 2087.125000000 159291.791666667 0 0 0.0000000 0.000000000 2015-11-01 912 1663.778645833 100 0 50 0.000000000 0 0 0 0 0 2725.778645833 4812.007500000 0 0 0 0 0 0 4812.007500000 130054.166666667 25062.500000000 0.000000000 0 155116.666666667 0.00000000 0.000000000 0 0 0 0.000000000 2086.228854167 161378.020520833 0 0 0.0000000 0.000000000 2015-12-01 912 1670.711056858 100 0 50 0.000000000 0 0 0 0 0 2732.711056858 4818.022509375 0 0 0 0 0 0 4818.022509375 130054.166666667 25062.500000000 0.000000000 0 155116.666666667 0.00000000 0.000000000 0 0 0 0.000000000 2085.311452517 163463.331973351 0 0 0.0000000 0.000000000
The first header line is a simple list of names. The first name “Date” heads a column of first of month dates in YYYY-MM-DD format. The SWAG clock has month resolution and dates are the only nonnumeric items. Names beginning with “E” like E0, E1, …, are aggregated expenses. Names beginning with “I” like I0, I1, I2 … are income totals. “R” names are reserves: basically savings, investments, equity and so forth. “D” names are various debts. BB is basic period balance, NW is period net worth and “U” names are utility series. Utility series facilitate calculations. Remaining names are self-explanatory totals. Be aware that this table has been formatted for this blog. Examples of raw input and output tables can be found here.
The next ingredient in the SWAG stew is what many call a scenario. A scenario is a collection of prospective assumptions and actions. In one scenario you buy a Mercedes and assume interest rates remain low. In another, you take the bus and rates explode. When forecasting I evaluate five basic scenarios, grim, pessimistic, expected, optimistic, and exuberant. Being a negative Debbie Downer type I rarely invest time in exuberant scenarios. I concentrate on grim and pessimistic scenarios because once you are mentally prepared for the worst anything better feels like a lottery win.
The following is a typical SWAG scenario table. Scenario tables, like time series tables, are simple TAB delimited text files.
Name Scenario On Group Value OnDate OffDate Method MethodArguments Description reservetotal s0 assumptions 0 2015-09-01 2015-10-01 assume RSavings=. 0.5 [ RInvest=. 3 [ REquity=. 3 [ ROther=. 1 annual nominal percent reserve growth or decline during period car s0 50 2015-09-01 2035-08-01 history annualized car maintenance until first death house s0 912 2015-09-01 2016-08-01 history BackPeriods=.1 current rent until move insurance s0 100 2015-09-01 2035-08-01 history car insurance living s0 1650 2015-09-01 2044-01-01 history YearInflate=.5 normal monthly living expenses salary s0 4800 2015-09-01 2016-08-01 history BackPeriods=.4 [ YearInflate=.1.5 maintain net monthly income until move reservetotal s0 25000 2015-09-01 2015-10-01 reserve Initial=.1 [ RInvest=. 1 stock value at model start reservetotal s0 130000 2015-09-01 2015-10-01 reserve Initial=.1 savings at model start salary s0 4200 2016-08-01 2023-07-01 history BackPeriods=.4 [ YearInflate=.1.5 reduced net income after move until retirement house s0 move 2000 2016-08-01 2016-09-01 history moving expenses house s0 100 2016-08-01 2044-01-01 history incidental housing expenses after move house s0 100 2016-08-01 2044-01-01 history home owners association payments house s0 150 2016-08-01 2044-01-01 history property taxes reservetotal s0 buy house 110000 2016-08-01 2016-09-01 reserve Initial=.1 [ REquity=. 1 down payment added to house equity initial setting prevents double spend loan s0 buy house 150000 2016-08-01 2023-02-01 borrow Interest=. 4.5 [ YearTerm=. 30 [ DHouse=.1 [ LoanEquity=.1 30 year mortgage rate on house until inheritance reservetotal s0 buy house 110000 2016-08-01 2016-09-01 spend down payment on house from savings annuities s0 250 2018-07-01 2035-08-01 history monthly retirement and other annuity payments end date is unknown annuities s0 50 2018-07-01 2035-08-01 history any government pension payments reservetotal s0 assumptions 0 2020-01-01 2044-01-01 assume RSavings=. -2.5 [ RInvest=. -5.0 [ REquity=. -5.0 [ ROther=. 2 market tanks government introduces negative interest loan s0 buy car 10000 2020-07-01 2025-07-01 borrow Interest=. 5 [ YearTerm=. 5 [ DCar=.1 pay balance of car at 5% for 5 years reservetotal s0 buy car 7000 2020-07-01 2020-08-01 spend car down payment from savings reservetotal s0 inherit 180000 2023-01-01 2023-02-01 reserve Initial=.1 inheritance to savings reservetotal s0 buy house 150000 2023-03-01 2023-04-01 transfer Fee=. 1500 [ DHouse=.1 pay off remaining mortgage balance after inheritance fee is closing cost salary s0 1400 2023-07-01 2035-08-01 history estimated social security payments spread over expected life insurance s0 700 2023-07-01 2024-12-01 history medical insurance in the gap between retirement and spouse medicare eligibility annuities s0 100 2024-12-01 2044-01-01 history any retirement pension payments to spouse annuities s0 100 2035-08-01 2044-01-01 history any us social security survivor benefit after first death
Again the first header row is a simple list of names. Most scenario names are self-explanatory but four OnDate, OffDate, Method, and MethodArguments merit some explanation. SWAG series methods assume, history, reserve, transfer, borrow, and spend are modeled on what people typically do with cash.
- assume sets expected interest rates and other global assumptions for a given time period. SWAG series methods operate over a well-defined time period. The period is defined by OnDate and OffDate.
- history looks at past periods and estimates a numeric value that is projected into the future. Currently, history computes simple means but the underlying code can use arbitrary time series verbs.
- reserve manages savings, investments, equity and other cash-like instruments.
- borrow borrows money and sets future loan payments. borrow supports simple amortization loans but is also capable of reading an arbitrary payment schedule that can be used for exotic2 loans.
- transfer moves money between reserves, debts, expenses and income series.
- spend does just what you expect.
SWAG series methods adjust all the series affected by the method. As you might expect SWAG arguments methods are detailed. MethodArguments uses a restricted J syntax to set SWAG arguments. Argument order does not matter but only supported names are allowed. Many examples of SWAG MethodArguments can be found in the EXCEL spreadsheet tp.xlsx. I use EXCEL as a scenario editor. By setting EXCEL filters, you can manage many scenarios.
The final SWAG input is a name cross-reference table. It is another TAB delimited text file that defines SWAG names. You can inspect a typical cross-reference table here.
To run SWAG you:
- Prepare input files.
- Start J, any front-end jconsole, JQT or JHS will do, and load the Swag script.
- Execute RunTheNumbers.
- Open the EXCEL spreadsheet swag.xlsx, click on the data ribbon and then press the “Refresh All” button.
Let’s work through the steps.
Prepare Input Files
By far the most difficult step is the first. Here you review your financial status which means checking bank balances, stock values, loan balances and so on. Depending on your holdings this could take anywhere from minutes to hours. I call this updating actuals. Not only is updating actuals the most difficult and time-consuming step it is also the most valuable. Money that is not closely watched leaks away.
I store my actuals in a simple tabbed spreadsheet. Each tab maintains an image of a text file. I enter my data and then cut and paste the sheets into a text editor where I apply final tweaks and then save the sheets as TAB delimited text files.
Monthly income, expenses and debts are easy to update but some of my holdings do not offer monthly statements. The verb RawReservesFromLast in Swag.ijs fills in missing months with the last known values. When I’m finished preparing input files I’m left with four actual TAB delimited files, RawIncome.txt, RawExpenses.txt, RawReserves.txt, and RawDebts.txt. You can inspect example actual files here.
Start J and load the Swag script.
The SWAG script is relatively self-contained. It can be run in any J session that loads the standard J profile. Load Swag with the standard load utility.
Here SWAG is loaded in JHS.
RunTheNumbers sets the SWAG configuration, loads scenarios, copies actuals to each scenario, and then evaluates each scenario. Scenarios are numbered. I use positive numbers for “production” scenarios and negative numbers for test scenarios. It sounds more complicated that it is. This is all you have to do to execute RunTheNumbers
RunTheNumbers 0 1 2 3 4
The code is simple and shows what’s going on.
RunTheNumbers writes a pair of TAB delimited forecast and statistics files for each scenario it evaluates.
Open swag.xlsx and press “Refresh All”
The spreadsheet swag.xlsx loads SWAG TAB delimited text files and plots results.3 I plot monthly cash flow, estimated net worth and debt/equity for each scenario. The following is a typical cash flow plot. It estimates mean monthly cash balance over the scenario time range.
The polynomial displayed on the graph is an estimated trend line. Things are looking bleak.
Here’s a typical net worth plot.
In this happy scenario, we die broke and leave a giant bill for the government.4
So far SWAG has met my basic needs and forced me to pay more attention to the proverbial bottom line. As I use the system I will fix bugs, refine rough spots, and add strictly necessary features. Feel free to use or modify SWAG for your own purposes. If you find SWAG useful please leave a note on this blog or follow the SWAG repository on GitHub.
- What do you call dis-integrated collections of programs that you use to solve problems? Declaring such dog piles “systems” demeans the word “system” and gives the impression that everything has been planned. This is not how I roll. “Mob” is far more appropriate. It conveys a proper sense of disorder and danger.↩︎
- When borrowing money you should always plan on paying it all back. Insist on a complete iron clad repayment schedule. If such a schedule cannot be provided run like hell or prepare for the thick end of a baseball bat to be rammed up your financial ass.↩︎
- It may be necessary to adjust file paths on the EXCEL DATA ribbon to load SWAG TAB delimited text files.↩︎
- They can see me in Hell to collect.↩︎