[imsd]

Interesting formulas to take my spreadsheets up a level.

For anyone interested, I made a Google Sheets template that I share with my friends. It has been well-received.

The crypto section can be ignored for those not involved with that sector.

If it's useful, would love to hear your feedback.

https://docs.google.com/spreadsheets/d/1qYLOAjzaIIcFLFw_j-P4...

Of course, much can be automated using Google Finance and relevant pricing APIs to auto-update position values.

[settrans]

This looks great; it nearly identical to a sheet I developed for my personal use.

The biggest improvement I'd like to make to mine is to implement some approximated form of risk parity[0]. That is, instead of comparing nominal allocations, to compare weighted risk allocations by asset class. This is useful because (for example) equities will contribute significantly more volatility to your portfolio than, say, fixed income, so to the extent you are trying to capture the diversification benefits of allocating across different risk buckets, you may want to scale your exposure according to volatility[1].

There is a modeling challenge here, of course, because asset classes will never be independent risks, but I'd prefer something directionally indicative rather than econometrically optimal.

[0] https://en.wikipedia.org/wiki/Risk_parity [1] https://www.ipe.com/risk-parity-the-truly-balanced-portfolio...

[xorfish]

I would caution against using risk parity as it assumes that you know the volatility and correlation of different asset classes.

Look at the Figure 1 of this paper:

https://www.casact.org/sites/default/files/old/01pcas_scheel...

[shapefrog]

I would second this - I have no fancy papers or citations etc but eventually risk parity will blow up the world.

There are broadly 2 regimes that dictate volatility and correlation, normal and shit-hitting-the-fan. Risk parity models skew heavily towards the everyday, when prices tick up / down by small amounts, and diversification exists.

On adverse market wide event, there is (generally) no diversification, and leveraged portfolios in particular can face significant losses.

There is no silver bullet, but portfolio wide value at risk, i.e. what the outcome on the day/week/month on any given day in the last 5 years (or more) had I held these exact same positions is as good a measure as any. The distribution of outcomes being something worth understanding and tuning risk to.

[imsd]

That'd be a great addition. I plan to update this with historical charting and tracking of position values over time. I'll see if I can find a way to add risk parity. Makes a lot of sense.

[xorfish]

Here is my sheet that is mainly used for rebalancing ETFs according to some simple rules. It is currency agnostic (base currency can be changed) and should work fine for European investors as well.

It automatically pulls MSCI market cap information and determines the allocation based on that.

Then the number of shares that you need to buy or sell is calculated based on the target allocation.

If you use Interactive Brokers, then buy and sell texts for the IBOT are also generated.

https://docs.google.com/spreadsheets/d/1yJSF7tBZpJPvRf7tja-7...

[SeaWhales1000]

Very nice. I need something like this incorporated into my workbook since I mainly use ETFs over mutual funds. Not sure if you know, but Sheets has a function "GOOGLEFINANCE" where you can input a ticker symbol and retrieve a lot of useful info, including price.

EDIT: I commented too soon. You have useful info in some hidden columns. Thanks again for this! I'll be incorporating it into my spreadsheet.

[theNJR]

Thank you!