Advanced Portfolio Tracker

Description

Keep track of your portfolio and its historical and expected forward-looking performance with a wide array of useful indicators such as CAGR, XIRR, Value-at-risk, Covariance matrix, and much more.
While the basis of this Advanced Portfolio Tracker is similar to to Simplified Portfolio Tracker, as both provide you with a portfolio analyzer and a comprehensive dashboard with key economic indicators, the Advanced version also gives you the opportunity to track your buying and selling, with the result that you now can follow up on even more data of your investments. A Personal Finance Dashboard has been added with key data of your portfolio, as well as even more graphs to help you visualize your finances.

Summary of contents:

Portfolio Tracker
In the Portfolio tab, you can keep track on your portfolio allocations and get reminders when it is time to rebalance your portfolio.
After you have entered your tickers and aimed allocations, based on the amount you enter in Account Balance cell, you will receive a suggested number of units to purchase to reach your aimed allocation based on your portfolio amount. The sheet has built-in support for ETFs denominated in several currencies, and the Aimed Hold Amount column is based on the ETF’s/asset’s trading currency, while Hold Value and Unit Price (EUR) columns automatically convert unit prices to EUR. All allocation percentages are based on EUR.

In the Monthly Savings column, you will receive advise on where to add funds, based on the Monthly Savings amount you stipulate in the corresponding cell.
The Rebalancing sensitivity steers the Rebalancing during the year column and is a measure of how much any asset is allowed to deviate from its aimed allocation before you should rebalance the portfolio on an ad hoc basis, i.e., in addition to scheduled rebalancing (annually, semi-annually or quarterly, etc.).

Personal Finance Dashboard
Based on your transactions, this tab shows you a good selection of data and key indicators of your portfolio.

For example, you will easily find your return over several periods, as well as your portfolio’s CAGR and XIRR. Your portfolio is also compared to that of S&P500 and of a typical 60/40 portfolio.

Moreover, you also find Value-at-Risk measures, based on your current portfolio value, that is to say how much your portfolio is at risk of declining with 95% and 99% certainty in any week, month and year. These numbers are also compared against those of the S&P 500 and a 60/40 portfolio.

This tab also helps you keep track of dividends received as well as paid transaction costs.

Transactions
The Transactions tab is that basis for much of the data generated by the spreadsheet. Tabs that depend on what you insert here are the Portfolio Tab, as well as the Personal Finance Dashboard tab where you find data over your actual portfolio’s performance, including XIRR, CAGR, dividend yield and paid transactions costs, etc.

To track your transactions, just enter the date of the transaction and the ticker (make sure that it is a ticker that you have manually listed in the “ETFs” tab), whether the transaction is a buy/sell/dividend, price per unit (excluding transaction costs), currency, and number of units. This will then update your aggregate hold of the ETFs and will be reflected elsewhere in this spreadsheet.

Economic Dashboard
The Dashboard tab gives you a quick overview of several elements:

i) Your portfolio and the performance of it, based on the current composition
ii) Key portfolio data including expected return, volatility, Sharpe ratio, and VAR, compared against the S&P 500 and a 60/40 portfolio
iii) Latest inflation rates and GDP growth rates of select key major economies
iv) Historical data of certain stock markets/indexes, and EUR/USD and BTC/USD exchange rates
v) US Treasury yields of various terms, together with interest rate expectations and inflation expectations
vi) Historical prices of 12 important commodities, together with 3 key commodity indexes.