본문 바로가기

카테고리 없음

Asset Allocation Software For Mac



  1. Asset Allocation Software For Mac Free
  2. Asset Allocation Optimization Software

Jun 25, 2019  Some investors swear by portfolio management software to help solve this problem. In some cases, like for retail investors, dealing with this type of software. Apr 18, 2018 The PC version of Quicken enables you to see your portfolio by asset allocation. It should be noted, however, that the Mac version doesn't have the same functionality. As a long time Apple fanatic.

Investors should look at all of their accounts as a unified portfolio to construct a portfolio that is low cost, well diversified, and tax efficient. Those who are new to the concept are often overwhelmed by the complexity involved in maintaining such a portfolio. Using a spreadsheet to maintain a portfolio is intended to suggest one way of easing the burden.

  • 1Asset allocation
    • 1.3Asset allocation visualized
  • 3Tax lot tracking

Asset allocation

One of the easiest ways to maintain your portfolio is to categorize by asset allocation, like so:

Account Domestic
stocks
REITs International
stocks
Bonds Total
Taxable - - $14,000 - $14,000
Roth IRA - $9,000 $9,000 - $18,000
401(k) $49,000 - - $19,000 $68,000
Total $49,000 $9,000 $23,000 $19,000 $100,000
Desired $48,000 $8,000 $24,000 $20,000 $100,000
Difference ($1,000) ($1,000) $1,000 $1,000 -
  • Cells in the rows for Taxable, Roth IRA, and 401(k) should be manually entered. All other cells can be automatically calculated.
  • Each cell in the Total row represents the total amount of domestic stocks, that of real estate investment trusts (REITs), and so on.
  • The Desired row represents the desired amount of dollars in each asset class and can be calculated by multiplying the value of your portfolio by the percentages in your target asset allocation.
  • The Difference row tells you how far off your portfolio is relative to your desired asset allocation. It can be calculated by subtracting a cell in the Total row from the corresponding cell in the Desired row.

Sample spreadsheets

  • Here is a sample spreadsheet in this style. It includes the additional features above; it can also adjust for the different after-tax value of assets in different accounts, and combine multiple subclasses into one class (so that you may have a target allocation to foreign stocks, and separate targets for developed and emerging markets). A simpler version is also available; it does not track subclasses.
  • One important aspect of of maintaining your asset allocation is to do periodic rebalancing. A sample rebalancing spreadsheet is available from Google Drive (by forum member LadyGeek).[1] Three example approaches are used, which could be incorporated into any of the sample spreadsheets.
  • Asset Allocation Spreadsheet - a Google spreadsheet created by Hoppy08520, with a link to documentation, that can help setup a multi-account portfolio.

Suggested improvements

  • You might want to make a separate Target row to keep track of your target allocation; in that case, the Desired row would be the product of the Target row and the total portfolio.
  • Similarly, you might make an Actual row to compute your percentage allocation, so that you could compare your target percentages to your actual percentages.
  • If you have funds which cover multiple asset classes, you can put each fund in a different row, make an extra column for the total fund value, and have the spreadsheet automatically compute the amount of the fund which is in each class. This allows you to easily track the effect on your allocation of adding $10,000 to a balanced fund.

Asset allocation visualized

tfb's article, Cascading Asset Allocation Method, describes how to select a portfolio's asset allocation by visualizing the process as a top-down hierarchy. The most important selection is at the top (stocks/bonds), which then breaks down into smaller asset classes. Selection of the actual funds is done last, which is at the bottom of the chart. Two spreadsheets are available.

First, forum member pradador has created a spreadsheet which allows one to create a portfolio by following the advice in the article. The spreadsheet is available on Google Drive: Cascading Asset Allocation (The spreadsheet was further modified by forum member LadyGeek to include error checking.)[2]

This spreadsheet includes a fixed income major asset category, which is not mentioned in the article. There is no consensus on the role of fixed income in a portfolio; it's dependent on your situation. If fixed income is considered as a bond, then set the percentage of Fixed Income to 0.

There are two tabs:

  • Top Down: Starting on the left side and proceed to the right, enter the desired asset allocations to arrive at the percentage of each fund's contribution to your portfolio. The bottom row is reserved for error checking. If an entry error is made (the percentages do not add up to 100%), an error will be displayed.
  • Bottom Up: Use this tab to determine the asset allocation percentages of your portfolio when you know the funds. In the right-most column, enter the percentages and funds. The spreadsheet will work left and arrive at the asset allocations. The blank cell in the lower right-most corner will display an error if the percentage does not add up to 100%.

How to use a proxy fund

Google Finance offers historical and real-time securities information using the GoogleFinance() function in Google Sheets.

If you have funds held in a Collective Investment Trust (CIT), in which there is no ticker symbol, then you can use a proxy fund to keep your balance roughly up to date using the GoogleFinance functions in Google spreadsheets.[note 1]

For example, suppose you hold the government's Thrift Savings Plan C Fund, which is a collective investment trust (CIT) that tracks the S&P 500 index. Find out the closing price of the CIT on the current day, and also find a similar proxy fund, such as Vanguard's institutional index fund (VINIX).

For example, suppose the C Fund's closing price is $23.6602 and Vanguard's VINIX is $167.75. This means that a share of the C Fund is worth 7.08 times a share of VINIX (167.75/23.6602).

To maintain a real-time estimate of your C Fund balance without needing to update the C Fund's share price every day, you can instead take the daily price of VINIX and divide it by the multiplier to get an approximate share price for the C Fund.

For example, these could be the cell along a row in your spreadsheet and this is what you would enter into them.

Implementing a proxy fund
Cell Heading Enter Notes
A2 Shares 100 Enter the number of shares you have in the Collective Index Trust (CIT). As you add or subtract shares from contributions, withdrawals or exchanges, you'll need to periodically update this amount.
B2 Closing Price 23.6602 Enter the CIT's closing price. You'll need to look this up in your plan.
C2 Close Date 01/03/2014 Enter the closing date on which you got the 'Closing Price' of the CIT in B2. Make sure you enter the date and closing price are in sync, and be sure that the closing price has been updated in case there is a lag between the market close and the posting of the closing prices.
D2 Proxy Fund VINIX Enter the ticker of a fund that is similar to your CIT.
E2 Proxy Price =INDEX(GoogleFinance(D2, 'close', C2); 2; 2 ) This returns the closing price of the proxy fund you selected in D2, on the date C2, which is the same date you recorded the closing price of the CIT.
F2 Multiplier =E2/B2 This is the ratio of the Proxy Fund's Price divided by your CIT's price, on the same date.
G2 Proxy Fund Current Price =GoogleFinance(D2, 'price') The current price of your proxy fund
H2 CIT Approx Price =G2/F2 This is the approximate calculated share price of the CIT
I2 Balance =H2*A2 The approximate balance you hold in the CIT. This is the approximated price of the CIT multiplied by the number of shares you hold in the CIT.

To keep this reasonably up-to-date and to prevent the 'multiplier' from drifting over time, from time to time you can replace the date in cell C2 and the actual closing price of the CIT in cell B2.

Real-time tracking from a website

Another technique is to find a website that outputs the price of the trust in real-time (or near real-time) and then use the GoogleFinance ImportHmtl() function to 'scrape' the HTML from the page.

If the ImportHtml() function can scrape that data cleanly into defined rows and columns with the price landing in a single cell, then you can then use the INDEX function to get the value of a particular cell by referencing its row and column. Two examples are described in the Bogleheads forum:

Asset Allocation Software For Mac

Rate of return

Asset Allocation Software For Mac Free

See also: Internal rate of return, Calculating personal returns

A spreadsheet to calculate investor and portfolio returns is provided in Calculating personal returns. It allows Bogleheads to report their personal returns in a uniform manner.

If you know the dates you made your investment contributions and your withdrawals, the XIRR (Internal Rate of Return) spreadsheet function is the best and most accurate method for calculating your rate of return. This method is far more accurate than annual average returns, as contributions and withdrawals (cash flows) are weighted for both time and value.[3][note 2]

The forum discussion thread XIRR function again has details and a working example.[note 3]

Tax lot tracking

Another use of a spreadsheet is to keep track of share lots in a taxable account, so that you can automatically compute the capital gain or loss when you sell, and easily see which shares should be sold for tax loss harvesting or to minimize the tax cost of selling. A spreadsheet for tax lots could look like this:

Date
bought
Amount
invested
Share
price
Shares Date
sold
Share
price
Amount
received
Capital
gain
2/1/08 $5000 $20.00 250.000 10/31/08 $15.00 $3750 -$1250
5/1/08 $5000 $18.00 277.778 - - - -
7/1/10 $10000 $30.00 333.333 2/1/11 $33.00 $11000 $1000

Sample spreadsheets

  • Here are three versions of a spreadsheet for tracking tax lots, and computing the capital gains and losses on each sale. There is a version for stocks or ETFs, most mutual funds, and funds with purchase and redemption fees. In a brokerage account, you would make one sheet in a workbook for each holding. The worksheets adjust for stock splits and share class conversions, but if you sell part of a lot, you have to split the lot into two purchases manually.

Portfolio tools

Main article: Portfolio tools

In addition to the spreadsheets there are various utilities to design and manage portfolios that span multiple accounts. Most tools cannot automatically compose your ideal portfolio, but you can use the tools to enter two or three models that you composed, and then you can compare and contrast the results, looking in particular at the blended expense ratios and the totals of each asset class. Two popular tools are:

  • Vanguard Portfolio Watch is an online tool for Vanguard clients to track asset allocation.
  • Morningstar Instant X-Ray is a tool especially useful for breaking down a portfolio into asset allocations and style boxes. Requires a free subscription to use.

Software

Notes

  1. Proxy fund: A fund used to represent another fund. The C-fund does not have a ticker symbol. In its place, the Vanguard institutional index fund (VINIX) is used to represent the C-fund. The Vanguard fund is the proxy for the C-fund.
  2. Internal refers to the fact that its calculation does not incorporate environmental factors, e.g., the interest rate or inflation. Reference: Internal rate of return, from Wikipedia
  3. XIRR function again also describes a bug in MS Excel's XIRR() function. This bug is not present in LibreOffice or Google Drive Sheets.

See also

References

  1. To download, select File --> Download As --> Excel or OpenDocument.
  2. Cascading Asset Allocation Spreadsheet, forum discussion.
  3. Using XIRR in Excel, forum discussion

Asset Allocation Optimization Software

External links

  • LibreOffice Calc, a free open-source spreadsheet that's compatible with Microsoft Excel. Available for Microsoft Windows, Linux, and Mac OS X.
Articles
Spreadsheets
Accumulation-dynamic decumulation • Asset class returns • Calculating personal returns • Check register • Effect of expenses on a portfolio • Household budget • Retiree Portfolio Model • Simba's backtesting spreadsheet • Social Security tax impact calculator • Variable percentage withdrawal
Vanguard data spreadsheets
Accounting data spreadsheets • Statistical data spreadsheets

Asset allocation software free

Sample portfolios
Approximating Vanguard target date funds • Lazy portfolios • Three-fund portfolio • Two-fund portfolio • Slice and dice • Slice and dice international
Portfolio management
GnuCash • How to build a lazy portfolio • Investment policy statement • Lump sum vs DCA (Dollar cost averaging) • Passively managing individual stocks • Prioritizing investments • Rebalancing • Short term portfolio management (6 months) • Short term portfolio management (1 year) • Short term portfolio management (5 years) • Using a spreadsheet to maintain a portfolio • Systematic investing • Tax loss harvesting • Using open source software for portfolio analysis • Value averaging
Portfolio withdrawals
Matching strategy • Safe withdrawal rates • Withdrawal methods
Asset classes
US stocks • Non-US stocks • Real Estate • Bonds • Money markets • Alternate asset classes
Retrieved from 'https://www.bogleheads.org/w/index.php?title=Using_a_spreadsheet_to_maintain_a_portfolio&oldid=69341'

Modern Investment Technologies
Asset Allocation | Portfolio Optimization | Risk Management

Our portfolio analysis software, SmartFolio, is a highly advanced, easy to use analytical tool to assist and enhance the management of investment portfolios according to the investor’s risk profile. SmartFolio is a state-of-the-art asset allocation software aimed at all types of investors and investment professionals. Active SmartFolio users include institutional portfolio managers, investment advisors and sophisticated private investors. SmartFolio contains advanced asset allocation, portfolio optimization and risk management techniques, based on the most recent achievements in portfolio theory . The software combines highly advanced and innovative analytics with a user-friendly, intuitive interface, perfectly suited to any level of expertise and experience.

Feedback from SmartFolio users now available!


Screenshots

Further information

 

Existing SmartFolio clients aren't affected: they will be able to roll their annual licenses and get support as before. If you'd like to roll your license please contact us directly.Non-commercial users (university professors and researchers) continue getting access to SmartFolio without any limitations both for themselves and their students, with limited support. If you are a student and you'd like to use SmartFolio in your project, please ask your tutor to contact us.

Current SmartFolio release: 3.2.4 - December 5, 2013


What's new in SmartFolio:
Risk Parity portfolios
Maximum Diversification portfolios
Exponentially weighted sample estimates
Expanding in-sample window in walk-forward optimization
Free upgrade from previous versions!

SmartFolio competitive edge

The standard approach still taken by most investment management and portfolio optimization software packages is essentially based on the one-period model proposed by Harry Markowitz in 1952. This model is overly simple, and while it is computationally efficient, it also suffers a number of serious drawbacks. These drawbacks are listed below with information on how SmartFolio handles each of them.
SmartFolio
Since the Markovitz model is single-period, you are not allowed to rebalance your portfolio during its lifespan. This might be OK for short-term investments, but is a serious problem for long-term investment plans.
We have placed continuous-time portfolio theory at the heart of SmartFolio. This approach is much more realistic as it enables you to rebalance your portfolio from time to time.
Using our built-in tools, you can construct portfolio strategies that not only benefit from rebalancing, but also
minimize rebalancing transaction costs.
The Markowitz model assumes that the parameters which define market state are known. This applies to the expected returns and volatilities of assets, as well as asset interdependencies, measured using correlations.
This assumption together with the common practice of replacing these values with their sample counterparts leads to unjustifiably risky portfolios with weights concentrated in a small number of assets. As a result, in the real world, portfolios obtained in such a way often perform quite poorly.
SmartFolio implements various techniques to handle this problem, known as parameter uncertainty. These techniques include:
  • several advanced statistical methods for parameter estimation
  • robust portfolio optimization (finding the portfolio that is optimal under the worst possible market conditions)
  • direct calculation of optimal portfolio in the presence of parameter uncertainty (the so-called three-fund optimal portfolio)
  • incorporation of investor's subjective views into the estimation process by means of the Black-Litterman model.
  • Walk-forward optimization that tests 'optimal' portfolios out-of-sample.
Markowitz assumed that asset returns are normally distributed. Unfortunately even such 'regular' assets as stocks and bonds deviate slightly from normality. This is particularly true for more complicated financial instruments such as derivatives or hedge funds.
Along with standard analytical methods that utilize normal distribution of returns, SmartFolio includes tools that enable you to test and optimize your portfolios directly on historical data.
'Normal model' approach is by far more computationally efficient and stable, while the 'historical simulation' approach gives you more realistic results especially when performing risk analysis using Value-at-Risk. Thus, combining these two methods allows you to achieve the best result.
Portfolio selection in the Markovitz model is based on the Risk-Reward criterion: first you define an acceptable level of portfolio variance, and then you find the portfolio that maximizes return for the given level of risk.
The problem with this approach is that variance is highly inadequate as a measure of risk; even when a portfolio’s returns satisfy the 'normal distribution' condition, its financial sense remains obscure for the investor.

An alternative approach that exploits utility functions suffers from another weakness; an investor’s risk-aversion coefficient, which defines his utility function, is very difficult, if even possible, to estimate.
Along with the standard utility-based approach, SmartFolio incorporates another technique of determining your investment goal. This is based on the assumption that what you care most about when selecting your portfolio is the probability that your portfolio beats the target growth rate.
Rates and probabilities are well-defined and very intuitive notions; therefore there is a much lower chance that you will not be satisfied with your investments just because your goals were estimated incorrectly.