The Excel Portfolio Performance Tracking Templateallows the performance of financial investment portfolios to be monitored and tracked on an ongoing basis with periodic valuation. Investment transactions can be entered during reporting periods and are accounted in calculating individual investment and overall portfolio performance. The portfolio performance tracking template can be refreshed at the end of the specified reporting period to archive transactions, reset start values and begin a new period for tracking the portfolio value and return.
Key features of the Excel Portfolio Performance Tracking template include:
The input for investment and transaction information is assisted with input forms, help icons and explanations of calculations.
Investment distributions, acquisitions and divestments and their dates are accounted for accurate security, asset category and portfolio performance calculations under time weighted return methodology.
An unlimited number of transactions can be entered for each investment with the ability to handle reinvestment of dividends and entire investment liquidation during the period.
The portfolio can be rolled over for a new reporting period whereby the current values replace starting values and transaction data archived into a separate transaction history list.
Monitored portfolios can accommodate up to ten financial asset categories which can be renamed and hidden or shown for customization and display purposes.
Automatic charts present current portfolio weightings by asset class. Portfolio performance can be tracked and visualized over time using snapshot charting whereby periodic portfolio value and return are plotted and displayed as trend lines.
The Excel portfolio performance tracking and valuation template is trusted and used by many financial advisors to manage investment portfolios for multiple clients.
Requirements Windows: Excel 97-2013
Mac OS X: Excel 2004 or 2011
USD22.00 Secure Processing
(Download updated on 2012-11-02)
Portfolio Valuation Data
The periodic number of units and corresponding prices for each investment are set in the Value sheet. New investment products can be added and removed to each asset class of the portfolio by using the prompted functions provided. Asset class category titles can be renamed for customization to portfolio dynamics and unused categories hidden and shown throughout the portfolio analysis as required. Start and end dates for the reporting period along with portfolio name and numeric denomination are entered here for display purposes through the portfolio tracking model. The capital contribution and initial investment date is used for calculating the returns since inception in the portfolio performance. The starting and ending values for each investment drive the initial performance calculation and overall portfolio valuation. A button is also provided to roll over the portfolio to a new reporting period whereby investment transaction data is saved to a history log and investment end values copied over to starting values.
Portfolio Investment Transactions
Each investment in the portfolio can have an unlimited number of transactions during the reporting period that affect the overall performance. Transactions are divided into two types which are distributions and investments; however the combination of transactions can be utilized to account for all types of cash flows to and from the investment. The reinvestment of dividends, for example, can be entered as a positive distribution and corresponding positive investment on the same date. New input rows can be added to accommodate more transactions for an individual investment by using the button provided for this task. Calculated information regarding the timing and magnitude of investments is stored in separate columns to the right for the portfolio performance analysis for the period.
Portfolio Performance Analysis
The portfolio performance analysis accounts to the intra period transactions for each investment to track the time weighted returns over the reporting period. The capital add or withdrawn as well any distributions are used with the start and end values to calculate the net return over the period and annualized return since inception for each investment. The net return over the period analyzes the investment and distribution transactions and their timings that were recorded for each investment. The annual return since inception uses the prior period return that is saved when the portfolio is rolled over to a new reporting period. Help icons for the performance returns are provided to detail the calculation method used for analysis.
Portfolio Tracking and Trend Analysis
The current weighting of the portfolio by asset category is presented by a pie chart and the associated data table is display with capital allocation values. The line chart tracks the trends of the portfolio valuation and performance over time by the values saved a snapshot intervals. Portfolio tracking snapshots can be taken at any time and interval to monitor the portfolio and historical performance trend on an ongoing basis. Snapshots are saved by clicking the button provided and can be removed selectively from the historical time series.
Many financial advisors employ this template for managing multiple client portfolios.
To facilitate the update of price data to multiple client portfolio files,
a batch update module has been separately developed and is freely available here.