3

I'm looking for a calculator that takes into account rebalancing when estimating total returns using historical data.

For example, I'd input the following into the calculator:

  • I have an initial $10,000 investment.
  • I put 50% in fund A.
  • I put 50% in fund B.
  • I rebalance every year.
  • Start in the year 1990.

Then the calculator would output the following using both funds' actual performance data since 1990 to the present:

  • Your portfolio total value in 2015 would be X amount of dollars.

Does anyone know of such a calculator?

closed as off-topic by GS - Apologise to Monica Mar 12 at 11:11

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions seeking product or service recommendations are off-topic because they tend to become obsolete quickly. Instead, describe your situation and the specific problem you're trying to solve." – GS - Apologise to Monica
If this question can be reworded to fit the rules in the help center, please edit the question.

4

My answer is Microsoft Excel.

Google "VBA for dummies" (seriously) and find out if your brokerage offers an 'API'. With a brief understanding of coding you can get a spreadsheet that is live connected to your brokers data stream.

Say you have a spreadsheet with the 1990 value of each in the first two columns (cells a1 and b1). Maybe this formula could be the third column, it'll tell you how much to buy or sell to rebalance them.

=((a1+b1)/2)-a1

then to iterate the rebalance, set both a2 and b2 to =C1 and drag the formula through row 25, one row for each year. It'll probably be a little more work than that, but you get the idea.

  • A start, but price data by itself won't suffice, so you're right re: "a little more work than that." Most funds pay distributions that would also need to be tracked and reinvested. Over a long period, distributions would represent a significant portion of portfolio value. A price-only approach works only in the simplest case where there are no distributions or other significant events (e.g. splits or consolidations). The scenario is unlikely for mutual funds, but possible for certain stocks. – Chris W. Rea Mar 26 '15 at 13:01
2
+25

R has really good package that lets you calculate the return of rebalanced portfolios. The package is called: PerformanceAnalytics (see: http://www.inside-r.org/packages/cran/PerformanceAnalytics/docs/Return.portfolio).

I quickly wrote a small script for you that lets you do exactly what you want.

Code:

startAmount <- 10000
library(PerformanceAnalytics)
#a small function to ensure that the data is of the correct type for the package to work
cleanData <- function(df) {
  if(ncol(df) > 0) {
    df <- df[,-1]
    df['Date'] <- as.Date(df[['Date']], format = '%Y-%m-%d')
    #set all the data to numeric data.
    #the first column is the date.
    cols <- c(2:ncol(df))
    df[,cols] <- lapply(df[,cols],function(x) as.numeric(as.character(x)));
    df <- xts(df[,-1], order.by=df[,1])
    return(df)
  } else {
    return("Error; the data.frame does not have any columns.")
  }
}
# use csv2 if you use use a comma as decimal point and a semicolon as field separator
# enter the path to your file where it says: portfolio.csv
yourPortfolio <- cleanData(read.csv('portfolio.csv'))
# the frequency with which you want to rebalance your portfolio is set to yearly. You could also set this to daily, weekly or monthly.
vectorReturns <- Return.portfolio(yourPortfolio, rebalance_on = c(NA, "years"))
dollarAmount <- startAmount*prod(1+vectorReturns)
print(paste("Your portfolio total value in 2015 would be ", dollarAmount, " amount of dollars.", sep = ""))

By default the portfolio is rebalanced to an equally weighted portfolio. It is also possible to rebalance your portfolio using custom weights. See the documentation on how to do this.

In order for this code to work you need to have your data already in return terms. You can do this easily in Excel. Make sure your data in excel looks like this:

             option 1     option 2
2003-10-30 -0.002014099 -0.002014099
2003-10-31 -0.002018163 -0.002018163
2003-11-03 -0.029322548 -0.022605170
2003-11-04 -0.032291667 -0.032128514
2003-11-05  0.019375673  0.017634855

Than export your data to a CSV file.

Note: before you run the code make sure you have installed the package PerformanceAnalytics. You can do this as follows:

install.packages("PerformanceAnalytics")

Let me know if you have any questions regarding the above.

Not the answer you're looking for? Browse other questions tagged or ask your own question.