Understanding the Model Structure

Help for understanding the model structure in the portfolio performance tracking template for Excel

User Guide Navigation: Business Spreadsheets User Guides: Project Planning and Management Multiple Regression Analysis and Forecasting Investment and Business Valuation Real Options Valuation Portfolio Optimization Portfolio Performance Tracking Customer Invoicing Portfolio Performance Tracking Help Topics: Understanding the Model Structure Managing Portfolio Products Entering Transactions Executing Reports Understanding the Reports

The 'Value' sheet calculates the net gain or loss over the valuation period. It is also provides buttons for critical functions including adding new products, deleting existing products, hiding or unhiding product categories, and rolling over the portfolio to a new reporting period. Details of new products are entered into this sheet, as are market valuations of products at the time of reporting. Parameters entered and displayed on this sheet are:

• Product Name. The name of the investment product.
• First Invested. The date that the investment was first invested
• Capital Contribution. The amount invested on the first investment date.
• Value at Start of Period. The value of the investment at the start of the reporting period.
• Current No. of Units. The current number of investment units (as at the reporting date)
• Unit Price. The current price of each unit (as at the reporting date)
• Current Valuation (formula). The current number of units, multiplied by the unit price.
• Maturity Date. (Optional) The date that the investment will mature or redeem.
• Gain/ (Loss) since Start (formula). The difference between the value at the start of the reporting period and the current valuation, divided by the value at the start of the reporting period.

 

The 'Trans' sheet is for manually entering transactions on products as they occur. It also calculates incremental returns from distributions. This sheet records transactions for the reporting period which are then taken into account when calculating the performance of each investment. Parameters entered and displayed on this sheet are:

• Product Name (pre-populated). The name of the investment product.
• Description. (Optional) A description of the transaction.
• Type (pre-populated). The type of transaction - either 'Distribution' (interest/dividends received or equivalent) or 'Investment' (purchases, withdrawals [negative], or reinvestment).
• Date. The date of the transaction.
• Units. The number of investment units transacted.
• Unit Price. The transaction price per unit.
• Net Amount (formula). The transaction units, multiplied by the unit price.
• Tax Amount. (Optional) The amount of tax attributable to the transaction.
• Gross Amount (formula). The tax amount subtracted from the net amount.

 

The 'Perf' sheet calculates and displays the performance of the portfolio over the reporting period by taking into account the transactions over the period and the valuation parameters. Information on formulas utilized to calculate performance is provided in corresponding help icons. There is no manual entry in this sheet. Parameters displayed on this sheet are:

• Product Name. The name of the investment product.
• Value at Start of Period. The value of the investment at the start of the reporting period.
• Capital Added or Withdrawn In Period. The sum of all investment (or divestment) transactions made during the period.
• Current Valuation. The current reported valuation of the investment (as at the reporting date).
• Capital Invested Over Period. The sum of all investment (or divestment) transactions made during the period (repeated for logical display purposes).
• Income Distributed Over Period. The sum of all distribution transactions made during the period.
• Net Return Over Period. The net return made from the investment during the reporting period (see below for detailed explanation).
• Annual Return Since Inception. The annualized return of the investment since the date it was first invested (see below for detailed explanation).

 

The 'Graph' sheet automatically creates a pie chart illustrating current portfolio weightings. Performance 'Snapshots' can be created here to visualize the overall value and periodic return of the portfolio over time. Clicking the 'Take Snapshot' button populates the table and performance chart with a new observation consisting of the reporting date, total value and overall return from the 'Value' sheet. Snapshot observations can be removed from the chart by selecting the row to be removed and clicking the 'Remove Selected Snapshot' button.

 

The 'History' sheet stores all transactions prior to the current valuation period, in chronological order.

Back

Next