How I track my finances using spreadsheets Part 3: Net Worth

<– Part 2 | Part 4 –>

Hi dear RIP readers,

this is Part 3 of my Spreadsheets series, and by far the longest so far. In this post I’ll deep dive into how I track my Net Worth. I hope you like it 😉

Some of the terms used here have been defined in the previous episodes. Please, read all of them before this one.

Ok, some quick context: I’m going to zoom in into the Net Worth section of the current Yearly Net Worth sheet, within the “RIP Net Worth” spreadsheet. This deep dive happened in September 2020, so I’m using the “2020 NW” sheet, populated up to column L: “Sep”, whose Net Worth section spans from row 2 to row 72 at the moment.

Here’s “RIP Net Worth” Spreadsheet embedded for fun:

Enjoy!

Net Worth section

As I said in the previous episode, for historical reasons the Yearly NW Sheet contains several sections that cover different tracking goals.

There are three main sections: Net Worth, Cash Flow, and FIRE Metrics.

The NW section is the one that tracks our Net Worth (you guessed it right!), and it’s the most complicated one.

Strictly speaking, the NW section spans from row 2 to row 72 (at the moment), but in this paragraph I’m covering up to row 103, i.e. all there is before the Cash Flow section.

Rows in the Asset/Liability list (row 3 to row 61) represents either an individual asset/liability or a section/subsection separator.

The Asset/Liability list (or simply Asset List) is split in subsections by their asset type.

Why? I want to be able to sum assets by their currency and asset class. I find that having spatial cohesion and a column (col B) for the currency works pretty well.

As an alternative I could have added another column for asset type, and use more complex formulas to sum assets of the same type (but different currencies). The advantages of this alternative approach would have been more freedom in how to organize the rows. For example I could have put all the entries from the same provider (PostFinance, InteractiveBrokers,…) close together. At the moment I prefer to keep an asset class consistency and ease my formulas.

The subsections are stocks, bonds, cash, taxes, other. For almost a decade I used to have a real estates subsection. Not anymore in 2020, finally. If I’ll ever invest in precious metals, cryptos, p2p lending, startups, art pieces or whatever else, I’d add the corresponding subsections to my NW. Same if I’ll ever own something valuable like a car or a collection or whatnot.

Stock subsection

The stock subsection tracks my investments in equities.

A listed security row is a row that represents the total value of the shares of a specific security that’s traded on exchanges. For example let’s take row 5, VYM (Vanguard US high dividend stocks ETF).

The formula to get the total value of this asset is the product of the number of shares I own by the market value of a share, which is in another cell, in another section.

In this example, VYM price can be found in Cell L75, in the “Finance” section.

The “realtime” share price is pulled into this spreadsheet via GOOGLEFINANCE function. As you can see the parameter I pass to the function call is the first column on the same row, i.e. the string “VYM” in this case. I use the dollar sign “$” before the A so that when I copy the current month column into the next month, the formula still holds. Then, at snapshot time, after I copied the formulas over, I freeze the value for the ending month. This is done in bulk for all the cells in the Finance subsection, via copying (CTRL+C, or CMD+C) and pasting values only (CTRL+SHIFT+V).

This is the way I chose to track listed securities, using two separate rows per security, one with total asset value, and one with share price.

I want to know how many shares of each security I own, the share prices, and the total value of my investment in each security at the end of each month (or live, for the current month).

I considered other alternatives:

• Only using a single cell per security, with formula “NumShares*GOOGLEFINANCE(Symbol)”. The problem with this approach is that it’s hard to track number of shares owned and security price over time. If I want to freeze snapshots the same way I described above, the only way to do it and preserve both the NumShares and the share price at the end of the month would be to manually substitute the GOOGLEFINANCE expression with its current value at month ending. For each security. For example, the expression for my VYM holdings would change from “=1200*GOOGLEFINANCE($A5)” to “=1200*83.79” at the end of August 2020. A lot of manual work, and still hard to track how VYM performed over the year.
• Three cells, one for number of shares, one for share price, and one for asset value (the product of the other two). I could track both the evolution of the number of shares that I own for each security, security price, and asset value over time. But it would have meant more rows (3x instead of 2x number of listed assets). Maybe this is the solution I’d adopt if I decide to move investments into their own sheet.
• A separate sheet for investments, and only report total value (per asset? per asset class?) in the NW. This is probably the best approach, especially if combined with the previous one. main disadvantage is that it increases overall complexity. Maybe it’s good for active traders who trades a lot, or for value investors who invest in 15+ companies… I still see it as an overkill, but given how volatile I’m becoming with my investment strategy, this approach starts to make sense.

I did use the single cell approach for individual stocks, during April-June 2020. Data were even more aggregated: I only allowed a single row for all the individual stocks to avoid messing up my sheet. The formula looked something like:

“=NumSharesECGOOGLEFINANCE(SymbolEC) + NumSharesRECGOOGLEFINANCE(SymbolREC) + NumSharesMCGOOGLEFINANCE(SymbolMC) + NumSharesTrollGOOGLEFINANCE(SymbolTroll)”

Luckily all of them were quoted in USD, else I’d have needed extra currency conversion steps. I didn’t plan to hold them for long, so this approach worked pretty well for me. If I’ll ever build a larger individual stocks portfolio I’ll reassess my reporting strategy.

I don’t track overall investment portfolio performance over time. It’s too complicated, and redundant. I’m ok with the Interactive Brokers reports.

“Don’t you want to track dividends issued by each investment over time?”

That’s a good point. You can extend the “three cells” strategy to a four cells, or maybe five (dividend per share, and total dividend)… If you want to track that you probably have no other choice than to build your own yearly investments sheet – Maybe I’ll do it next year, let’s see.

“Cool, looking forward… Btw, RIP, what are those “3A – 100% Stocks” and “3A – 75% Stocks” rows?”

Good question! Let me introduce the sub-subsections 🙂

Stocks sub-subsections

Row 10 (“3A – 100% Stocks”) and row 13 (“3A – 75% Stocks”) are stocks sub-subsections separator.

The stock subsection It’s a bit more structurally complicated compared to the other subsections. I’ve split it in a few sub-subsections to handle Pillar 3A investments.

Our Pillar 3As are invested in funds that invest X% stocks (like PostFinance Pension75).

Tracking these “X% in stocks”added two degrees of complexity:

1. Need to split their total amount between their constituent asset classes to track my overall asset allocation. For example, PF Pension75 it’s 75% stocks (with their own geographic allocation), and 25% “non stocks” that I consider bonds in my accounting.
2. Need to take into account future taxation on those Tax Deferred accounts. More on this later.

The solution I’ve adopted is to create subsections, and to use them in calculating current Asset Allocation in the Investing sheet, which we’ll explore in a future episode.

Finance section (tangent topic)

While we are on the investments topic, let’s touch the Finance section:

Pretty straightforward:

• A row for each security that I own for at least a month during the year.
• Cell value (past months) is the security snapshot price at the end of each month. Yes, I try to be there at 10pm-12am the last day of each month to freeze the prices!
• Cell value (current month) is the security current market price (delayed up to 20 minutes). Formulas: “=GOOGLEFINANCE($Ax)”, where x = row number.
• Cell background color is green or red depending on security price going up or down compared to last snapshot price.
• Cell color is gray if I didn’t own the security during that month.

Some nuances:

Securities Currency

I used to hardcode the securities currency (column 2), but I had two issues in the past.

• Sometimes GOOGLEFINANCE misinterpreted the symbol and returned the value of the same security traded in a different exchange, sometimes in a different currency.
• Sometimes numerical values were very different from the one I owned, messing up total NW. I remember it happened for IEML and CSPXJ last year. That’s why I had to append “-USD” (CXPXJ-USD) in 2019.

To fix this I query GOOGLEFINANCE for the symbol’s currency, and use a kind of “switch” statement with a “default” case that would alert me (sorry non-C++ speaking readers, a.k.a. everyone). The GOOGLEFINANCE function can do much more than simply returning current market price!

Maybe I should always use “exchange:symbol”, like explained in the documentation. [2020-09-12 Update: I just did that. Yes, I know, the post is not even published and I could have just took new screenshots and avoided this paragraph entirely, but I’m lazy. And I wanted to show you Rubber Duck Debugging at play 🙂 ]

PostFinance pension funds

PostFinance pension funds (Pension75 and Pension100) share values are inserted by hand.

They’re not in GOOGLEFINANCE. I look up the prices on Bloomberg (PFPEN75, PFPN100), which is faster at updating share prices than my e-banking account at PostFinance. Crazy, I know.

If I had a Viac account, I’ll probably handle it in a similar way.

Currency Pairs

Currency Pairs (row 89 to row 91) are separated from other listed securities by an empty row, but I treat them in the same way of stocks & bonds ETFs.

I care about USD, CHF, and EUR so I track the 3 combinations.

I don’t track both AB and BA, just the one that’s more standard. For example, EURUSD instead of USDEUR. I know I could save a row and only track two currency pairs, but it would have messed up some of the formulas.

In case I’d have to handle a fourth currency (say GBP) I’d avoid combinatorial explosion C(N,2) and would only track N-1 currency pairs from a base currency TBD (EUR or CHF) to the others.

A friend of mine shared with me his spreadsheet with 5 currencies, holy crap!

Significant Figures

For security prices I display only 4 significant figures (1.181, 50.66, 133.7… good thing I don’t own Berkshire Hathaway stocks!), but when snapshotting I save the value at whatever precision GOOGLEFINANCE can provide.

I’m getting crazy adding and removing a decimal digit with PFPN100 because it crosses 100 CHF/Share up and down every other day!

Consistency between NW and Expense tracking

I use the frozen currency pairs values in the monthly expenses sheet as well.

More on this in a following chapter on Expenses Tracking.

Performances

Last two populated columns hold the YTD and MTD security performance, for a quick overview of how is the year/month going. Mostly red, this 2020 for non-FAANGTesla stock owners.

Ok, back to the main course!

Expected Taxes on TDAs explanation

Among the stocks and bonds rows you can be puzzled to find expected taxes entries:

This is because the pension funds (Pillar 2 and Pillar 3) are Tax Deferred Accounts (TDA), which means I will have to pay taxes on them one day. When? Take a look at my Swiss Pension System post. How much? Take a look at my Lump Sum Tax post.

For Pillar 2, in case I won’t withdraw my money before retirement age, there’s the option to convert your total amount into an annuity instead of taking a lump sum, which may or may not be a good deal based on many factors. At the moment, I’m assuming I’ll cash all my accounts as lump sums, and pay Kapitalauszahlungssteuer (lump sum tax) on the cashed out amounts.

Since I’d like my tracking to be as accurate as possible, I’m estimating what would the average lump sum tax bracket be given my Canton of residence, current lump sum tax brackets, and total amount on my TDA accounts. I’m accounting for a 6.5% average tax rate for now (row 120, “Tax Percent on TDAs”).

This might change in the future, probably going up as the accounts values go up.

Anyway, I’m playing the pessimistic card as always. There are few tricks to pay a lower lump sum tax:

• moving your residence to a lower taxes Canton before withdrawing the money.
• moving your money into a vested benefits account registered in a lower taxes Canton
• withdrawing a portion of your money each year, avoiding higher marginal tax brackets.

I’ll investigate this deeper when either statutory retirement age gets closer, or leaving Switzerland (or buying a property) becomes an option.

Bonds subsection

I consider all our non-invested Pension assets as bonds. This is not accurate, I know. But not that much inaccurate either. They’re slowly growing, beating bonds expected returns in Switzerland.

I also consider rent deposits as bonds, and this is plainly wrong. But I like to consider as “cash” whatever is fully liquid, over which I have total control. I can’t withdraw my rent deposit tomorrow. Peanuts anyway.

Few more words on my Pillar 2 entries (rows 18 and 19): each month, their pre-tax total amount grows by monthly contributions, which are extracted from payslips and tracked in the Cash Flow section (rows 117 – 120):

As you can see, monthly contributions into my second pillar have been ~2k CHF between January and March, payments split 50% between me and Hooli, 500 CHF per month in mandatory portion (based on my age range 35-44), and the rest (~1500 CHF) into the extra mandatory portion. More on mandatory vs extra mandatory here.

Then I contributed zero during my unemployment months (April – August), then in September I’ll start contributing again to the new employer’s Pillar 2 fund, which at the moment sucks a lot (will negotiate it over the next weeks).

So, in the NW section, asset values of mandatory and extra mandatory increase based on the monthly contribution tracked down in the Cash Flow section:

My extra-mandatory Pillar 2 amount in February (cell E18) is the sum of the January value (cell D18) plus the February extra-mandatory contribution (cell E118).

When I receive a new pension statement (usually once or twice per year) I change things accordingly. For example, in January 2020 I received an amazing statement with better returns on Hooli Pillar 2 plan, thanks to a great year 2019 for the stock market.

The other assets in the bond subsection are bonds ETFs and Italian “Buoni Postali” (kind of CD).

“RIP, you’re wasting billions of words on irrelevant things, and just a sentence for what constitutes 65% (800k) of your Net Worth??”

Well, yeah, this is not a deep analysis on my NW, but a tutorial on how to write your own NW spreadsheet. My BND investments? Just a number here.

Cash subsection

I consider “cash” everything that’s liquid, not invested, and in my full control.

Bank accounts, cash sitting on Interactive Brokers, Revolut balance, Paypal balance, actual cash (banknotes and coins) on my wallets… you get the idea.

Like investments, this subsection is also at high risk of combinatorial explosion. What if I want to convert money on Revolut, and hold several currencies there? What if I do the same on my Paypal account? What if I hold cash in other currencies on IB? What if I open a Zak and/or Neon account? What about prepaid cards?

An alternative approach would be to have a separate sheet for cash, and only report here the totals, maybe split by currency. My complexity is still manageable, so… not yet!

As you see, everything is a trade-off 🙂

And yes, I have 100k+ CHF sitting on my PostFinance account. It’s called sleep-at-night-whatever-happens Money.

“Hey RIP, why sometimes I see strange formulas on cash cells? Aren’t you able to copy whatever number your bank accounts show?”

That specific case is a recurring one. Our Health Insurance (Mutuel) charges our insurance premiums for month #N on the last day of month #N-1. It pisses me off a bit, not because I’m paying a day before I actually use the “product”, but because of the accounting hell it generates.

For example, on August 31st, 2020 my PostFinance CHF checking account got charged 693.15 CHF for September health insurance premiums, and the balance at the end of the month was 103’724 CHF, not 104’418. I kept the value of 104’418 for August balance (cell K40), but make it explicit that it’s “= 103’724 + 693.15”. When I copied current column (col K, “August”) over to the next month, at snapshotting time, I put the actual amount (103’724) in the checking account cell (L40).

I know, I could have snapshotted the real balance on the bank account, and add a “credit” in the “other” section but it looked like an overkill.

“Couldn’t you just… consider this expense as an August one? It literally was!”

But it logically wasn’t! That’s just a cash movement, the real “expense” happened in September, which is when I actually got the service I paid for.

In theory, if I expatriated on August 31st I might had been able to recoup this expense.

“So you do the same when you book Hotels and Flights for vacation – especially when they’re refundable, right?”

Uhm… no, I don’t do that (but I could).

A recurring expense of this size makes the effort worth.

They started charging me on the last day of the previous month only somewhere mid last year. If I accounted for this regular expense when it physically happened, maybe I’d pay twice in a specific month, and zero on another month. Since it’s a sizable expense, that would mess my analysis. “Oh, wow, this month we spent 1.2k more compared to last month… wait… ah ok, it’s just that I paid twice the premiums last month, and none this one!“. I don’t want to do that.

“So you do the same with electricity bills that are coming every two month? Do you account half the bill on each month? And you do the same with SeRaFe (“TV tax”)? And same with the HalbTax abo (public transportation)? And why not with the Zoo yearly subscription?”

Because it’s a matter of trade-offs! And the trade-offs depend on the impact of the issue.

Electricity bill is 60 CHF every two months, I’m not going to account for 30 CHF per month. Health insurance is ~700 CHF per month, much bigger.

Even though I’m a spreadsheet nerd, and a bit perfectionist, I’m not trying to split neutrinos here 🙂

Btw, a couple of years ago I tried to add a “yearly expenses” entry on my expense tracking system, to account for non-monthly natural expenses like yearly subscriptions, vacations, furniture purchase, and so on… I killed the experiment two months later. Every expense could be seen as a yearly one, or part of a yearly budget – maybe except groceries and recurring ones like rent, childcare, health insurance. An accounting hell!

Taxes subsection

if you’re a Swiss citizen or a Swiss C Permit holder, you’re no more subject to taxation at source, and you have to deal with taxes in a unique and complicated way. Plus, both my Canton and the Federal government are slow to process tax returns. Which leads to…

I have 4 tax years still open, and if I won’t receive final 2017 calculations and bills by December 2020, in a few months I’ll have 5 tax years open!

Let’s start with current year.

Current Year – Income Tax

We’re in year 2020. Current year has 4 tax-related rows (row 53 to row 56) :

Row 53 is Expected Income Tax. At the beginning of each year I come up with an expected average income tax rate based on our expected total income for the year, expected deductions (child care, pillar 3, pillar 2 buy ins), and Cantonal and Federal tax brackets structure.

It’s a purely heuristic process, no huge formulas involved. But I’m getting pretty accurate!

In the past years I’ve always been too pessimistic, mostly because I love to get positive news at tax return time. Last year I’ve been aggressive with my estimated average income tax, and I almost nailed it. I was 900 CHF wrong on ~54.5k CHF total taxes. Within a 2% error margin 🙂

This year I’ve reduced the expected average income tax rate because I expected not to work too much. I was wrong, I’ve been earning either a salary or unemployment benefits without skipping a month. I expect my expectations to be wrong this time. But I also have more deductions for childcare, let’s see.

Anyway, the expected average income tax rate is stored in the Cash Flow section, (row 112), and it’s 15% 17% for 2020.

Each month, I calculate an expected income tax amount based on the expected income tax rate (row 110).

The expected income tax amount for January 2020 (cell D110) is the following:

“= – D112*(D106+D107   +D108*D91   +D109*D89)“

Negative sign of course. D112 is the expected income tax rate, and it’s multiplied by all the income entries that are subjects to income tax: salaries and dividends in our case. Salaries (cells D106 and D107) are expressed in CHF, dividends in EUR (cell D109) and USD (cell D108). We need some currency pairs: EURCHF (cell D89) and USDCHF (cell D91) to bring final currency for taxes to CHF.

The expected cumulative (YTD) income tax for current year (row 53) is the sum of those monthly expected income tax amount.

Row 55 account for cumulative taxes paid over the year. We’re talking about tax advances for current year.

The Canton sends me a document with their expected tax calculation at the beginning of the year, and I get to decide if I want to follow their suggestions, or do whatever else I wanted.

One could pay nothing today, for tax year 2020, and wait for their final calculation. One can also pay 1 Million CHF, and get it back with small interests at final calculation time. I’ve seen someone at Hooli claiming that this is a good strategy because none will give you 0.5% (even 1% on some Cantons) on a saving account in Switzerland.

Your tax office could be a good saving account 🙂

So far I’ve decided to stick with the Cantonal estimates. For tax year 2020, they estimated total Cantonal taxes ~42k CHF, and suggested 3 payments of ~14k each. As you can see I’ve submitted first one in April, and second one in July. I’ll probably pay the third one in October/November.

Sadly, you can only pay advances for Cantonal and City taxes. Not the Federal one.

Obviously this entry has a positive sign, it’s a credit for me 🙂

Row 56 is another credit for cumulative US withholding tax paid on US domiciled distributing ETFs.

As you know, all US domiciled ETFs (like VT, VOO, VTI, VYM…) distribute dividends.

When they do, the IRS (US Internal Revenue Service, their tax authority) withholds a portion of it. It’s 15% if you can file a W-8ben form, i.e. if you have “Foreign Status of Beneficial Owner for United States Tax Withholding and Reporting“. You can recover this money via filing a DA-1 form, assuming you can prove you paid dividend taxes to your local government. I filed the DA-1 form for tax year 2019 while filing for Swiss taxes for tax year 2019, i.e. in early September 2020.

For example, in March 2020 I accounted for 282 USD credit, that summed to the 28 in February totaled 310 YTD (back in March):

Where does this 282 USD credit come from?

Easy, from the amount IRS withheld on my 2024 USD I received in dividends and IB interests on USD cash (before the FED killed it).

“But 15% of 2024 is…”

Yes, I know, it’s not 282 my dear Mr. Precision…. That’s because the 15% cut on IB interests on idle USD cash have already been taken in February.

So the accounting flow for dividends coming from US domiciled securities is the following:

• An ETF/stock announced a dividend amount per share, an ex-dividend date, and a payment date.
• I add this expected amount multiplied by the number of shares I own of that security to row 108 in the Cash flow section. This immediately generates extra expected taxes according to my expected average tax rate. The dividend gross amount is also reported in the “Other – USD” cell for the same month (row 60).
• I don’t do anything at ex-dividend date. I usually just sigh, because at dividend announcement time my NW gets a positive bump (the dividend amount added as credit, ETF/stock value usually unaffected), while at ex-dividend date the ETF/stock value gets the negative bump, combined with other market movements.
• At payment date (or later, when I want to sync IB cash&taxes with my NW sheet):
  • The expected total dividend for that stock gets frozen: instead of NumShares * DividendPerShare I jut copy the actual amount issued, like you can see in the picture above.
  • The “Other – USD” credit disappears, 85% of it goes into “InteractiveBrokers – USD” (row 46), and 15% into “US Withholding Tax 2020” (row 56). I don’t use formulas, I put real number I see on IB Activity report (Forex, Withholding Tax, Dividends, and Interests sections).
• If ex-dividend and payment dates cross monthly borders, I keep the amount in “Other – USD”, but still attribute taxes to the ex-dividend month. A bit more complicated if it crosses yearly borders… but it’s just accounting porn 🙂

Let me repeat again: when a stock/ETF announces a distribution of dividends, I’m not immediately richer. This money is coming from businesses, and it’s going to have a mostly zero impact on my finances at dividend payment date. Actually, I’m a little bit poorer, because I will have to pay Swiss taxes on dividends and hope to recoup US withheld taxes.

Previous Tax Years

Before we jump into the most complex entry in my spreadsheet (Exp Wealth Tax 2020), let’s take a look at the previous tax years that are still open as of September 2020:

Usually the rows don’t change over the course of the year, until one of the following happens:

1) A new year begins.

At the beginning of the year I account for a 0.5% interest rate on my debts with the government. See Expected Taxes 2017 in January (cell D49).

The new amount is 1.005 (+0.5%) times last year value. Maybe this is an overkill, I heard that Federal tax bill doesn’t carry any interest (there’s no way you can advances Federal taxes!), but better to be pessimistic as always 🙂

2) I receive final calculation from one of the two the tax offices for a tax year I’ve already submitted.

For example, in August 2020 I’ve received final Federal final tax calculation for tax year 2018. It was better than expected (they confirmed my tax return, but I expected them to add some) so the cell K50 went from -10k to -7k, Which is a nice +3k bump – also accounted as income. More on this later.

Anyway, Federal taxes due for 2018 = 14k, while Cantonal and City taxes due for 2018 = -7k, i.e. they should give us some money back because I paid more than due in 2018 tax advances.

But…

3) I pay/receive money after the final calculation for a tax year I’ve already submitted.

The Federal tax office sent me the 14k bill, while the Cantonal and City tax office is playing dead.

I paid my Federal bill in August, and the cell K50 went from -7k to +7k. I’m now in credit for tax year 2018.

Funnily, both tax offices hadn’t calculated my 2017 taxes yet…

4) I file tax return for the previous year.

In September 2020 (few days ago, while writing this post series) I filed my 2019 tax return. I asked for help, I admit. One day I’ll do my tax return on my own, promised 🙂

My expectations for total 2019 taxes delta was -13k (taxes due: -55k, taxes paid +42k, as you can see in 2019 NW sheet, row 44 and 45) but the tax return said -12k. Hurrà, 1000 CHF less than expected (actually 900 CHF, accounted as income in 2020). So the cell L51 went from -13k to -12k.

The tax return says I owe 16k to the federal Federal office, and overpaid 3k in City and Cantonal tax.

Maybe it’s a good idea to split expectations in Federal and Cit&Cantonal…

5) I receive the payment (or denial of) of a DA-1 form.

This is new to me. I heard that DA-1 are quick to get accepted/denied. Let’s see.

Anyway, as soon as something happens I bring the credit value to zero and the money should pop in my bank account (probably with a shitty USD to CHF conversion, I didn’t indicate my Postfinance USD checking account anywhere in the form), or I get denied the restitution for some reason.

Current Year – Wealth Tax

Row 54 says “Expected Wealth Tax 2020“. This is a new entry! It’s complicated to explain… and it contains circular formulas on a spreadsheet! We’re both tired, I assume.

“Yeah RIP, do you really think there’s someone who’s going to read this post in full? give us a brea….”

But let’s try anyway!

“ 😐”

In Switzerland you pay each year a tax on your total Wealth (Vermögenssteuer).

You sum you assets and liabilities, and pay City and Cantonal tax (no Federal wealth tax) based on a progressive taxation system. There are assets (your pension funds) that are exempt from the Wealth tax. For Zurich Canton, you can find the Tax Brackets for Wealth tax in a pdf you can download from this page.

Let’s say that if your taxable NW is in the low 6 digits or less, you shouldn’t care much about this tax. it will be irrelevant. But as soon as your taxable wealth reaches say 500k or more, the Wealth tax becomes non-negligible.

In my (freshly submitted) 2019 Tax return, the amount of Wealth tax was 1.5k CHF, out of 54.5k total taxes. Which means ~3% of my total taxes, but rapidly growing.

I expected to work less this year, and pay more wealth tax. That’s why I modeled the expected Wealth tax differently from the expected income tax in the 2020 NW sheet.

How did I model it? The only thing that actually matters is the taxable net worth on December 31st, and my expected average Wealth tax rate. Heuristically, like I did with expected average income tax rate, I estimated my wealth tax rate given the tax bracket for my Canton, and my City multiplier. I estimated 0.15%, which I raised to 0.17% on September, after having filed tax return for year 2019, where my effective (filed) average Wealth tax rate was 0.168%. Scary.

I didn’t want to just drop a 2k CHF bomb on December’s balance, so I decided to attribute a piece of Wealth tax to each month.

Is it an overkill? Probably yes.

Row 54 in the NW section shows how the expected total wealth tax grows each month. It will reach final expected value for the year in December.

The formula for March, for example, is:

” =  ( -F66 + sum(F10:F23) ) * F115 * F168/12“

Ok, stay with me…

• -F66 is total Net Worth in CHF, made negative.
• The range F10:F23 contains all assets that are to be removed from the Wealth tax calculation, i.e. Pension funds. Ok, there are other deductions but I’m ok with skipping them (pessimistic).
• The algebraic sum of the previous two is the taxable Net Worth, with minus sign.
• F115 is the expected average Wealth tax rate, 0.17% in my case.
• The product of the two above is the expected total Wealth tax amount if the year was going to end on this month.
• F168 is the number of the current month. March is 3. Divided by 12 is the fraction of the current year that’s already gone.
• The product of the two above is a good way (for me) to attribute each month a fraction of total Wealth tax for the year.
• It’s normally growing in absolute value over time, unless my taxable net worth goes down by more than 1/12 (8%) in a single month. The difference between the monthly values is something I can consider “wealth tax quota for month X“. Which is tracked in row 111, in Cash Flow section. It’s usually negative, unless – as above – my taxable Net Worth goes down by more than 1/12 in a month. it’s also a measure of how volatile my NW is. The lower the variance of this sequence of values, the more stable my NW is.

Ok, it was tough. But we made it 🙂

“Hey RIP, I copied your formulas and my spreadsheet is telling me there’s a circular something…”

Yeah, there’s a circular reference in the formulas 🙂

Sadly, it’s not something that spreadsheets love.

Row 54 (Exp Wealth Tax 2020) needs row 66 (Total NW in CHF) and vice versa.

Anyway, Google Sheets let you decide how to handle this. Since I expected that few iterations would make both values converge within a 1 CHF approximation, I edited the document setting to enable Iterative Calculation.

It’s a bit of a hack, but a pretty innocent one 🙂

Ok, done with taxes! Let’s move on…

Other subsection

In “other” I account for credits and minor (temporary) debts.

The entry “Cumulus Mastercard” (row 58) is for the monthly credit card debt. I and my wife only use this credit card for CHF payments. Mind that this is not a debt I carry over. I pay it in full, automatically, at the beginning of the next month. The fact that I pay month #N debt at the beginning of month #N+1 required this extra accounting step. I wish my CC balance would have been cleared on last day of current month!

The other three entries (CHF, EUR, USD) hold credits/debts in those currencies.

For example, we’ve seen how the “Other – USD” (row 60) holds USD dividends announced and not paid yet. Take a look at the “accounting flow for dividends” that I discussed above.

The Other-CHF (row 59) holds salaries expectations, and several mini credits or debts in our main currency while we live in Switzerland. Sometimes salary for month #N gets paid on month #N+1 (unemployment payments work this way), and that needs to be accounted for.

Anyway, at snapshotting time, when I create a new column, I already account for that month salaries in “Other – CHF”. I also account for the known fixed expenses coming over the following days like rent, child care, internet, and health insurance (if not paid yet).

So when snapshotting I see our NW boosted up by “expected salaries – fixed expenses (~4k CHF)”. A nice feeling 🙂 I know that all else being equal, it’s going to be just 2k worse for variable monthly expenses.

So if salaries for month #N are paid in month #N+1, at snapshotting time the “Other – CHF” cell contains up to 4 salaries (row 106 and 107, same and previous column) at the beginning of the month!

The “Other – EUR” cell contains EUR dividends not paid yet, and sometimes minor credits and debts in EUR currency.

Usually, the comments in those cells provide the missing details on the actual numbers.

“RIP, any crazy accounting ideas that you didn’t implement and kind of feel ashamed for having thought about?”

Yeah sure, tons of them! But I don’t feel ashamed, it’s just that it’s not practical to account for everything.

For example, if I buy a multiple entrance tickets for the local Swimming pool that doesn’t expire (or expires in years), I hate to account for the full cost at purchase time. I mean, I pay 80 CHF but I have a carnet of 12 tickets! This is not a cost, it’s a currency conversion! I should account for both the expense and the credit. Then, when I consume a ticket, I “spend” 80/12 = 6.66 CHF. Since individual tickets are sold for 8 CHF, I’ve actually made a profit when I buy a Combi12 carnet! I could technically sell individual tickets (with some caveats) for 7.99 CHF each and cash the profit! Wait, since I skip the line with a carnet, I could also charge more than 8 CHF to the annoyed “Sunday Swiss swimmer” who hates lining up to buy tickets! Same for public transportation “6x” tickets!

“I kind of regret having asked you this question… -.- ”

Totals

Finally the totals!

What’s our total Net Worth?

I thought this is a straightforward section, but I got asked more than once “do you have a Million EUR, AND a Million CHF AND a Million (and half) USD??”

I thought it was obvious but it actually isn’t.

No, the totals are shown in three currencies but they refer to the same asset list. They’re just converted in each one of the currencies that I handle.

[Note: there was a time when I used to sum the same-currency assets first, and report partial sums in each currency, between 2012 and 2015]

Why do I track totals in three currencies?

EUR and CHF are both potential FIRE currencies for us. I mean, we don’t know, really, if we’re going to stay in Switzerland or move back to Italy in retirement. If I had to bet, it’s a 50%/50% at the moment.

Keeping track of our NW in those two currencies seems mandatory.

USD is there just for fun. We don’t plan to retire in US. Maybe I started tracking total NW in USD to join some challenge, or get listed in the now agonizing rockstar finance directory. I don’t remember. It’s there for forgotten historical reasons 🙂

Ok, the yellow background rows (rows 63, 66 and 69) hold total Net Worth in EUR, CHF and USD respectively.

How is Total NW calculated?

I used to have an App Script for that, but few years ago I switched back to a “simpler” spreadsheet formula. Take September 2020, current month at the time of writing (cell L63)

= SUMIF($B$3:$B$62, “=F”,L3:L62)/L89 +

SUMIF($B$3:$B$62, “=E”,L3:L62) +

SUMIF($B$3:$B$62, “=D”,L3:L62)/L90

It’s the sum of three components. Each component is a SUMIF  (sum if) expression.

The SUMIF takes three parameters:

• A range where to verify the “condition”
• The “condition”
• Another range (same cardinality of the first one) with values to be summed together in case the condition is true.

Essentially, the SUMIF loops thru both ranges at the same time, checks the condition on the n-th element of the first range, and if the condition is met, then adds the n-th element in the second range to the current sum.

The way I use SUMIF is:

• range = currency column (col 2, fixed)
• criterion = is this a EUR asset/liability? – same for other currencies
• sum_range = asset values (target month column, moving)

The three components of my “Total NW” expression are the partial sums of all EUR, CHF, and USD assets.

Row 63, which is “Total NW (EUR)“, needs to convert the two non-EUR entries in EUR to avoid summing apple, pears, and oranges together. That’s why you see the “/L89” (EURCHF) and “/L90” (EURUSD) in the “F” and “D” component.

The “$” signs in the formula are there to keep the currency column fixed while the asset/liability column moving when I create the new month copying the current month column over, at snapshotting time.

Actually, only the “$” before column letter B are needed, because I only translate the formula horizontally, never vertically… and it wouldn’t work anyway, unless the sum_range had “$” before the row numbers. Ok, fixed. removed some “$”! Thanks rubber duck debugging 🙂

Btw, I could have simplified two out of three formulas by using currency pairs. For example the “Total NW (CHF)” (row 66) could have been just “row 63 multiplied by row 89 (EURCHF)”. I don’t remember why I wanted to explicitly have all the SUMIF formulas explicit though.

Deltas

Row 64 (and 67, and 70) is the “Monthly NW Delta“. If you’ve read some of my monthly/quarterly financial reports, you know I love these numbers. It’s the total change since the previous month. Easy.

Cell P64 (and P67, and P70) is the Yearly NW delta: total NW change over the year, or YTD if it’s current year sheet. So far, it’s slightly less than 80k (September 9th, 2020).

The background color tells us if the delta is positive or negative in that currency.

Row 65 is the monthly rate of change, and of course cell P65 is the yearly rate of change.

There are also some weird green numbers in columns R and S (average, and forecast).

Those cells are:

• Cell R64 and R65 are the average monthly NW Delta and Delta%
• Cell S64 and S65 is the projected yearly NW delta (and its %) assuming linear growth based on YTD average (cell R64).
• Cell R63 is the projected NW at the end of the year, assuming linear growth.

The above are the EUR cells, same is true for CHF and USD few cells below.

Subtotals

There’s also a couple of subtotals that are interesting few rows below, right before the Cash Flow section: the simple & currency split.

The “simple” subsection is a split by asset class. These numbers are pulled by the Net Worth by Asset Class sheet for some cool graphs.

The “currency split” subsection, as the name suggests, is a split of our NW by currency – equalized in EUR to compare apples to apples. These numbers are pulled by the Net Worth by Currency sheet for some more cool graphs.

That’s all for today!

<– Part 2 | Part 4 –>