Using Excel for Investment Performance Measurement

Microsoft Excel is a popular spreadsheet program used by amateur and professional investors alike. This program provides simple cells, formula and graphs in a user-friendly format. For investment performance measurement, simply set up the cells according to the asset types and change in asset prices. This spreadsheet can be continually updated so that you will always have the portfolio return every time you update the prices.

  1. Gather all your data for investment performance measurement by accessing your stock, bond, cash, real estate and other asset information. If necessary, you might have to create separate sub sheets to calculate the return on each asset class.
  2. Enter the column titles for investment performance measurement across the first line of the sheet by typing in the formula box and pressing enter after each word: “Asset Type,” “Market Value,” “Weight,” “Return” and “Weighted Return.”
  3. Enter each asset type under the “Asset Type” column. Most investors just include stocks and bonds, though you can use any asset type you want to include in the investment performance measurement. Enter “Total” on the cell below the final Asset Type.
  4. Enter the current market value of each investment under the “Market Value” column. On the cell below the final item, enter “=SUM(“ then highlight all the market values and add a “)” at the end. The total investment performance measurement will be calculated in this cell.
  5. Enter an “=” in the “Weight” column then highlight the cell immediately to the left. Enter “/” then highlight the total market value cell. Repeat this investment performance measurement step for every asset type.
  6. Insert the return of every asset for investment performance measurement over the given time frame under the “Return” column. Under the “Weighted Return” column, enter “=” then highlight the Weight. Under the Weighted Return column, enter “=” then highlight the Weight. Enter “*” then highlight the cell in the Return column.
  7. Enter “=SUM( ” on the cell below the last Weighted Return. Next, highlight all of the weighted returns and add a “)” at the end. The total weighted return will be calculated in this cell.