Price Performance Measurement Systems, Inc. (PPMS)
Start-Up Instructions
I. Initiating a new client
relationship with PPMS
Complete the
"New Client" screen on the PPMS website (www.PerfAT.com) in order to
gain access to the performance after-tax system. The Client Short Name that you select will be
associated with all of your portfolios.
(e.g. it refers to you, as the client of PPMS, rather than to your
investment management clients). It is
case-sensitive, so be sure that you log on and enter the name consistently in
your data transactions. Your full company
name as input here will appear on your portfolio performance reports if you
select that option. You must also
indicate whether you want cash flows to be treated under the default end-of-day
convention or on a start-of-day basis. A
contact name, phone number and email address are required in order to start up
a new relationship. The Period field is
optional but may be helpful since it creates a default to use when you add
additional portfolios in the future. The
Data Pass Method (detailed discussion in Section IV) is important for getting
your data into the PPMS system. The
default is Excel but other choices include CSV, Advent Axys, Schwab Centerpiece,
and Security APL. Additional automatic
interfaces will be added in the future.
If you choose Axys, Centerpiece, or Security APL you can change the pass
method temporarily during data uploads in order to use Excel or CSV for
necessary data additions (such as sub period valuations) or corrections (such
as muni bond interest).
II. Required data
Calculating
after-tax performance requires four types of data:
(1)
the
applicable tax rates for each portfolio,
(2)
the
valuation of the portfolio at various points in time (the major determinant of
pre-tax performance),
(3)
the
transactions which have occurred during the measurement period, and
(4)
a
linkage between transaction types and their tax status.
II.A. Portfolio
Set-Up including Tax Rates
The most straight
forward way to set up a new portfolio including applicable tax rates is the
on-screen "Add Portfolio" button.
A second option, batch processing to add hundreds of portfolios at once,
is discussed later. The 10-character Portfolio
Short Name input here will be used to connect all valuation and transaction
data and should probably be the same as used in your internal accounting
system. A full name is not required if
using Excel or CSV data pass methods but can be printed on reports, at your
option. If using Axys, Security APL or
Centerpiece, the full name MUST be identical to that printed on those reports
in order to automatically link your internal accounting system to PPMS. Separate portfolio passwords are not
recommended but are potentially available.
The Portfolio
Type field is important because it allows the system to default to the
maximum Federal and State tax rates for this type of client (pull-down menu for
choices). The 2-character state
abbreviation is likewise important to assure that the correct state tax is
applied. The default state is
"NO" for no state. If a state is selected, the current year
state tax is appropriately combined (not a straight addition since state taxes
are deductible for federal taxes) with the federal rates to provide the default
tax rates.
Period refers to the normal calculational frequency
for this client; it can be monthly, quarterly, or annually. The actual calculation will depend on the
valuation data supplied and can include any number of intermediate sub periods
if appropriate due to cash flow events during the period. In other words, you can supply monthly valuations
but print only quarterly performance reports.
The AMT
option is presently not implemented so it makes no difference what you
select. If a particular portfolio is
subject to AMT, you may wish to take this into account in adjusting the
applicable tax rates.
The OID
(Original Issue Discount) option is implemented but only in the sense that the
PPMS system will correctly process any OID accretion/amortization if supplied
via appropriate transactions. The AIMR
PPS/GIPS recommend rather than require the inclusion of OID
accretion/amortization.
The Amortization
field should be checked “yes” if you wish to claim compliance with the AIMR-PPS
standards. The PPMS system accepts
amortized interest (for any type of security) but it must have been calculated
elsewhere and included with other transactions.
If you do not check “yes” for this field, then you must respond “yes” to
the Cash-Basis field. Leaving both
fields “no” will result in no taxes being calculated.
The Cash-Basis
field indicates that this portfolio is interested in assigning taxes only based
on actual receipt of dividend and interest income. This may be appropriate for individual
portfolios where there is no intent to combine them in a PPS-compliant
composite or for composites consisting of equity portfolios with no fixed
income positions. It avoids the need to
compute amortized interest and is easier to explain to your clients. If you checked “yes” for amortization, then
you should answer “no” to Cash-Basis.
Cash transactions may be processed for other purposes (e.g. calculation
of the adjustment for non-discretionary withdrawals) even if you elect to use
amortization for taxes (see page 5).
The Inception
Date is optional but will be required if you wish to show comparable
benchmark performance or performance from inception. Historic default tax rates will be calculated
for each portfolio based on its inception date.
The default is to provide historic default tax rates since
The Fully
Liquidated Basis field should be left “No” unless you wish to show fully
liquidated performance in addition to the standard realized basis method. Although mutual funds are required to show
performance by this methodology for 1, 3, and 5 years, it has not been widely
accepted for separated managed portfolios.
This methodology taxes all unrealized capital gains as though they had
been realized during the period, even though no sales were actually made. It is an extremely conservative method of
calculating after-tax performance and may lead to distorted performance
comparisons between asset classes or very active managers vs. those who are
more tax aware.
The Performance
Net of Fees field should be left “No” unless you wish to show your clients
their performance net of management fees.
If you include fees in the transaction file (TX Type “FEE”), they will
be treated as a cash withdrawal in any case.
However, if you check “Yes” they will also be subtracted as an expense. If you would like to show performance net of
fees but the client pays fees from a separate account, they should be input as
TX Type “FEEX.” Such transactions will
not be treated as cash withdrawals and will only be used in the net of fee
calculation.
The Amortize Fees
field should be left “No” unless you have indicated that you wish to show
performance net of fees and you would like to amortize fees (recommended under
AIMR GIPS standards), In this case, you
(or your accounting system) will have to determine the appropriate amortized
values and enter them as TX Type “AFEE.”
These amortized fees are not cash flows and will only affect net of fee
performance. You may also include the
actual payment of fees as TX Type “FEE” since these transactions will be
treated only as cash flows and not be double-counted.
The Family
Relationship is optional (default is “none’) but selecting “Master Total”
or “Family Total” and “sub account” will allow calculation of after-tax
performance for both asset level and total portfolio level or other types of
aggregation. Selecting “Master Total”
will do the aggregation of performance by percentage weighting the sub accounts
according to start-of-period assets.
This methodology is suggested by the AIMR GIPS standards for composites
and is required when any of the sub portfolios have significant cash flows
(which resulted in sub-period valuation and linking) or experienced
non-discretionary withdrawals for which you would like to make an adjustment
(because such adjustments are non-linear).
Selecting “Family Total” will do the aggregation of performance by
summing all of the transactions and cash flows across all the sub portfolios so
that the family total portfolio performance will equal what it would have been
had there been no sub portfolios. The
two methods will yield exactly the same results for periods where there are no
cash flows.
The Master
Portfolio Name field should be left blank unless the family relationship
above was input as “sub,” in which case you should input the short name of the
related master portfolio.
II.B Portfolio
Tax Rates
PPMS allows for
eight different types of tax rates, although only seven are currently in use
because the IRS has removed the mid-term capital gain holding period.
|
Short term capital
gains |
Holding period
less than 1 year; ordinary income rate |
|
Mid term capital
gains |
Currently unused |
|
Long term capital
gains |
Holding period
greater than 1 year; long term cap gain rate |
|
Dividends |
Ordinary income
rate (with corporate exclusion if appropriate) |
|
Corporate interest |
Ordinary income
rate |
|
Treasury interest |
Ordinary federal
rate but no state tax |
|
In-state Muni
interest |
No federal or
state tax |
|
Out-of-state Muni
interest |
No federal but
ordinary state income tax |
The screen will
initially display default tax rates appropriate to the Portfolio Type and State
earlier selected and for periods since the portfolio inception. Both current and historical rates are supplied
for federal taxes (only current rates for state taxes) but all should be viewed
as simply a starting point. The user is
responsible for his/her own tax rate decisions, which
may involve rates either higher or lower than those shown. Simply blank out the dates of historic tax
rates if this portfolio is not expected to need historic analysis. These rates should be fairly constant for a
given portfolio unless there is a change in tax law or the client indicates a
difference in his/her tax situation.
These rates can be modified later or a new effective date added in the
case of a tax law change.
II.C Valuation
Data
Only five items are
needed for each valuation date: the Client Short Name and Portfolio
Short Name specified above, the period end date (PerfDate), the Period
Length in months (1,3,12), and the Portfolio Value. As noted earlier, valuation records may be
included for any number of sub periods if cash flows make such linking
appropriate. The Period Length should be
1 for month-end records and 0 for other sub periods. If sub periods are used, a separate sub
period record must be included for the last sub period in the period (e.g. a
duplicate record for month end with the Period Length equal to 0).
The Portfolio Value
field should include all cash flows and income received or accrued as of the close
of the date shown. Accounting systems
which do not include accrued interest in their portfolio value totals will have
to add such interest before importing data to PPMS. Users have the option of including accrued
dividends in their valuations (required by AIMR-PPS after
PPMS
supports several methods
of data transfer. The default is via
Excel spreadsheets. These five fields
should be the first five columns in a spreadsheet, as in the attached
example. Valuation data for any number
of portfolios and any number of periods may be contained on the same
spreadsheet. See the "Batch
Operations" section below for data exchange instructions. At the user's option, fields six through
nine, Unrealized Gains with a break-down by Short-term, Mid-term,
and Long-term, may be included in each record. There is no tax- or performance-related
requirement for doing so, but this has long been a recommendation of the
AIMR-PPS and it is required if the manager wishes to calculate an adjustment
for non-discretionary capital gains (see below) or after-tax performance on a
fully liquidated basis (The SEC requires post-liquidation returns for mutual
funds). These fields should represent
the difference between the value of securities in the portfolio and their cost
basis as of the period date. As such,
they should not include amortized income.
If you have chosen
the Axys, Security APL or Centerpiece data pass methods, you will be able to
send appraisal reports from those systems as CSV files. PPMS will determine the client short name
from your logon and look up the portfolio short name and date based on the
report title. Portfolio value and
unrealized gains will be determined from the bottom line of these reports. These methods reduce the risk of data entry
errors but are a bit more cumbersome since only one portfolio and date can be
transferred at a time.
II.D Transaction
Data
The PPMS system
supports 26 different types of transactions, which make up the most important
input to calculating the impact of taxes on portfolio performance. Additional types of transactions may be
included in the imported spreadsheet, just to facilitate the transfer of data
from your accounting system to PPMS.
However, transactions such as "BUY" or "SPLIT" will
be ignored since they have no direct tax impact.
When using the Excel
or CSV data pass methods, only five (or seven) items are required for each
transaction: the Client Short Name and Portfolio Short Name, as
specified above, the transaction (TX) type, the transaction date (TXDate),
and up to three more values with either an income amount or the proceeds,
cost basis, and original purchase date of a sale. The attached example includes two additional
columns for security identification (IDType may be either ticker(T),
cusip(C), name (N), or sedol(S)) but both may be left blank unless desired for
that purpose. The Income field is
required only for income-type transactions.
The Shares field is optional, again used primarily for record
identification. Proceeds, Cost, and
PurchDate fields are required only for sale transactions. The currency field is completely optional. Note that cash flow amounts should be
included as “Proceeds”, not income, and that all amount should normally be
positive. For example, a withdrawal or
management fee of $20,000 would be shown as “CshOut” or “FEE” with a value of
20000 in the Proceeds column (not -20000).
Note also that the aggregate trading transaction types, STCG, MTCG, and
LTCG should show values as “Income”, not “Proceeds”.
Supported
transaction types and the associated tax rates applied are:
|
Trading |
|
|
SELL |
Sell Security -
Capital Gain Tax Rate based on length of holding period and sale date |
|
SELLFX |
Sell Foreign
Exchange - Short Term Capital Gain Tax Rate, regardless of holding period |
|
SL1256 |
Close a Section
1256 transaction (index or futures) which requires automatic
60-40 Long Term/Short Term taxes |
|
Income |
|
|
DIV |
Dividend -
Dividend Tax Rate |
|
INT |
Cash basis
Interest (unspecified type) - Corporate Interest Tax Rate |
|
CINT |
Cash Basis
Corporate Interest - Corporate Interest Tax Rate |
|
TINT |
Cash basis
Treasury Interest - Treasury Interest Tax Rate |
|
MINT |
Cash-basis
Municipal Interest - Municipal Interest Tax Rate |
|
MOINT |
Cash-basis
Out-of-State Muni Interest - Out-of-State Muni Interest Tax Rate |
|
AINT |
Accrued Interest
(unspecified type) - Corporate Interest Tax Rate |
|
ACINT |
Accrued Corporate
Interest - Corporate Interest Tax Rate |
|
ATINT |
Accrued Treasury
Interest - Treasury Interest Tax Rate |
|
AMINT |
Accrued Municipal
Interest - Municipal Interest Tax Rate |
|
AMOINT |
Accrued
Out-of-state Municipal Interest - Out-of-state Muni Interest Tax Rate |
|
OID |
OID
accretion/amortization (unspecified) - Corporate Interest Tax Rate |
|
COID |
OID
accretion/amortization on corp bonds - Corporate Interest Tax Rate |
|
TOID |
OID
accretion/amortization on treasury bonds - Treasury Interest Tax Rate |
|
MOID |
OID
accretion/amortization on municipal bond - Municipal Interest Tax Rate |
|
Aggregates |
|
|
STCG |
Aggregated short
term cap gain – Short Term Cap Gain Tax |
|
MTCG |
Aggregated mid
term cap gain – Mid Term Cap Gain Tax |
|
LTCG |
Aggregated long
term cap gain – Long Term Cap Gain Tax |
|
Flows |
|
|
CshIn |
Cash In (not taxed
but required for performance calculation) |
|
CshOut |
Cash Out (not
taxed but required for performance calculation) |
|
ForTax |
Withheld tax on
foreign stock dividends (treat as CshOut) |
|
FEE |
Management Fee
(treated as CshOut) |
|
FEEX |
Management Fee
paid from external sources (used only in net of fee performance) |
|
AFEE |
Amortized
Management Fee (used only in net of fee performance) |
|
NDWD |
Non Discretionary
Withdrawal (Not taxed but used in Adjustment for non-discretionary capital
gains) |
|
ShrIn |
Shares In (Value
of shares added to portfolio; Not taxed but required for performance
calculation) |
|
ShrOut |
Shares Out (Value
of shares gifted or otherwise withdrawn from portfolio; Not taxed but
required for performance calculation) |
Important considerations:
Lumping Trading
Transactions
Because all trading
transactions are summed over the performance period, it makes no difference to
the PPMS system if the user inputs each security sale, each tax lot, or an
aggregation of all trades which are treated the same for tax purposes. If your accounting system provides such an
aggregation, and you are using the CSV or Excel data pass methods, it may be
simpler for you to enter the total portfolio short term realized gain as an
STCG record and long term realized gain as a LTCG record rather than individual
transactions. If so, be sure that the
net gain or loss (as a negative) are entered in the
“Income” column. If you use the Axys or
Centerpiece data pass methods, all individual transactions are read and entered
from the realized gain/loss reports of those systems.
Lumping Income
Received
Because all similar
income is summed over the performance period, it makes no difference to the
PPMS system if the user inputs each stock dividend or interest payment received
as a separate item or whether they are input as a single total for the
portfolio at the end of the month. Do
whichever is more convenient for your accounting system. This is true of all types of interest, cash basis
or amortized, but not true of cash flows which must be identified by the
correct date.
Definition of a
Dividend
The maximum
individual tax rate on corporate dividends was lowered to 15% as of
Amortizing Interest
and Dividends
The amortized
interest in transactions types AINT, ATINT, AMINT, or AMOINT is actually the
difference between the total amortized interest of these types (corporate,
treasury, municipal bonds) in the portfolio as of period end and the beginning
of the period. Total amortized interest
may change during the month due to the purchase or sale of securities which
include amortized interest. If entered
separately for each security, purchased interest should be included as positive
income and sold interest as negative income.
In the absence of trading transactions, amortized interest for a given
security during the period can be calculated as principal x coupon rate x
length of period (in days)/ 365. PPMS relies on the user’s accounting system to calculate
amortized interest and to properly include purchased and sold interest. Although no ADIV transaction type is
provided, users may elect to accrue dividend income simply by listing DIV
income as of the ex-date rather than pay-date.
If you use Axys or Centerpiece data pass methods, this election is
automatic.
Including both
Cash-basis and Accrued Interest
A
portfolio which is using
accrued interest accounting may, but need not, also include the actual cash
basis coupon interest payments as received.
The interest and taxes will not be double-counted; only one of the
income accounting methods will be used, depending on the calculation basis
specified for this portfolio. The
primary reason for including both would be to allow for the proper calculation
of the adjustment for non-discretionary capital gains (see below), which
requires that cash basis income be subtracted from client withdrawals.
Unspecified Interest
Income
The INT, AINT, and
OID transaction types do not specify the type of fixed income interest and
currently default to assuming corporate interest (ordinary income tax
rates). The user's accounting system
would therefore not have to separate out types of interest. The only way to assure that the proper tax
rates are being applied for Treasuries and Munis is to use the correct
transaction type for these holdings. If
you use the Axys data pass method, you may have to make corrections to back out
some INT records and re-add them as MINT or TINT, since Axys reports may not
correctly distinguish between types of interest income. Please see the separate note regarding fuzzy
logic used by PPMS to attempt to determine the nature of interest reported by
Axys and assign municipal interest to the correct state.
Cash Flows and
Gifting/Share Transfers
The CshIn and ShrIn
transactions are handled identically for performance purposes, both before and
after taxes. The CshOut, ShrOut, and
NDWD transaction types are handled identically for pre-tax performance but NDWD
is used uniquely for the after-tax adjustment.
Cash flows are assumed to occur on the end of the date
specified. In the cash of “significant”
cash flows (sometimes defined as greater than 10% of the portfolio), it may be
appropriate to revalue the portfolio as of the same date. If your internal accounting system assumes
beginning of day cash flow, then the revaluation date should be for the date
prior to the posted cash flow date. Note
that if you use sub period valuations, the weighted cash flows printed for the
full period will be much smaller than you might expect. That is because the weighting is an
approximation which uses the days remaining between period end date and date of
cash flow as a factor. By using sub
periods, you have dramatically shortened this weighting factor (to zero if you
do it for each such flow). A small value
of weighted cash flows is an indication of a much more accurate calculation
(see equations in Section VI below),
OID Amortization
Tax-wise, there is
no difference between income characterized as ACINT or COID, or between ATINT
and TOID, or between AMINT and MOID so these amortizations may be combined if
it simplifies data entry. The critical
difference on your internal accounting system is that OID amortization will
also increase the tax basis of the bond, thus reducing any eventual capital gain. PPMS relies on users
to track the tax basis of all securities.
Aggregating a Family
of Related Portfolios
A “family” of
related portfolios may consist of the sub portfolios of separate family
members, the asset-level portfolios (stocks, bonds, cash) of the same person,
or any other type or
group of portfolios for which you would like to create a total
performance record. All of the
transaction data must be input for one of the sub portfolios (which must be
designated as “sub” on the portfolio maintenance screen and must be linked to a
“master” or “family” total by indicating the short name of such total
portfolio). No valuation or transaction
data should be input for the total, but it must be “created” in the sense of
giving it a name, description, and particularly identifying it (in the portfolio
maintenance screen) as either a “Master Total” or “Family Total.” Selecting “Master Total” will do the
aggregation of performance by percentage weighting the sub accounts according
to start-of-period assets. This
methodology is suggested by the AIMR GIPS standards for composites and is
required when any of the sub portfolios have significant cash flows (which
resulted in sub-period valuation and linking) or experienced non-discretionary
withdrawals for which you would like to make an adjustment (because such
adjustments are non-linear). Selecting
“Family Total” will do the aggregation of performance by summing all of the
transactions and cash flows across all the sub portfolios so that the family
total portfolio performance will equal what it would have been had there been
no sub portfolios. The two methods will
yield exactly the same results for periods where there are no cash flows.
Data Transfer
If you have chosen
the Excel or CSV data pass methods, transaction data, including any optional
fields should be the first 11 columns in a spreadsheet, as in the attached
example. Transaction data for any number
of portfolios and any number of periods may be contained on the same
spreadsheet or CSV file. See the
"Batch Operations" section below for data exchange instructions.
III. Using the PPMS
After-Tax System
Once your portfolios
are set up and the required data in place, using the PPMS system at www.PerfAT.com is both fast and
straight-forward. Simply log on as a
registered user with your password, click "Calculate Performance" and
select the portfolio you wish to see.
The resulting report may be printed on any standard computer, and the
internet-based format makes it platform independent. Click BACK in your browser to return to the
prior menu and select a different month, quarter, or a full year. Performance is actually recalculated on the
fly based on your data to allow for any on-line transactions or valuation
maintenance that you might require.
Sample output, reprinted below, shows the application of maximum
individual tax rates to a portfolio based in
Maintenance (Checking
and correcting data errors)
Clients and
Portfolios -- From the Main Menu, click on the Account Maintenance button to
add a new portfolio, edit or delete existing portfolios, including tax rates or
edit your client record. The Main Menu
button brings you back to either batch operations,
calculating performance or maintenance.
"Home" brings you back to the first page, at which point you
will have to log in again.
Valuation – From the
Main Menu, click on View Valuation.
Select the portfolio and dates between which you would like to see
valuation input (which will include the performance output as well). Because each portfolio can have only a single
value at period end, PPMS was designed to allow automatic maintenance of
valuation records. Simply re-import a
valuation file, whether in Excel, CSV, Axys, or Centerpiece formats with
corrected values. The new Value fields
and associated unrealized gains will simply override the old ones. This also allows for recalculation with sub
period valuation by adding all of the sub periods including the one prior to
month end together with a new month end record.
Transactions – From
the Main Menu, click on View Transactions.
Select the portfolio and dates between which you would like to see
transactions. Because it is quite
possible for a portfolio to have multiple transactions involving the same
security on the same date, it is not possible to apply the same automatic
override capability. Instead, erroneous
transactions should be backed out by (1) changing your data pass method to
Excel or CSV, (2) reading in a spreadsheet with the reverse of the incorrect
transaction (i.e. a negative dividend, interest, or cash flow to back out the
earlier positive one), (3) entering the correct transaction. Steps (2) and (3) may be contained in the
same spreadsheet or CSV file. Don’t
forget to then change the data pass method back to your normal method. Newly discovered transactions including cash
flows can always be added via a new spreadsheet and performance recalculated.
Taxes – In the case
of periodic federal or state changes to the eight investment tax rates, PPMS
will enter a new default tax record for each affected entity (individual,
corporation, NDT, etc.). Users can
access and append these revised default rates or modify them as appropriate to
individual portfolios via the “Edit Portfolio, Add Tax Rate” capability under
maintenance. Such revised tax rates will
not be effective automatically on your portfolios because PPMS feels it
important for users to be aware of such changes and have the opportunity to
input “anticipated rates” as opposed to simply using the defaults, even though
these will be appropriately adjusted for current state tax rates.
IV. Exchanging Data with
PPMS
The Batch Operations
button on the main menu allows users to import portfolio, transaction and
valuation data to PPMS and export performance results back to their home
systems by using customized sub-directories and ASP Simple Upload, a protocol
similar to FTP. Although batches of new
portfolios can be created only via Excel spreadsheets, transaction and
valuation data may be imported via several different interfaces, depending on
your accounting system(s). Please see
the separate .pdf files for sample reports used in these interfaces.
In the case of
creating or adding multiple portfolios at once, the user should enter (or
download from his/her home system) all of the required fields for each new
portfolio to a spreadsheet. The
spreadsheet can reside in any directory on the user side and PPMS need have no
direct access to it. Instead, the user
simply enters the filename to be transferred (or Browses to locate it) and
clicks "Import New Portfolios" to send the file automatically to
their custom sub-directory on the PPMS server. Once there, the procedure will,
at the same time, assign appropriate tax rates based on the Portfolio Type and
State specified in each record. Users
will then have the opportunity to go back and modify any tax rates or other
data if necessary.
In the case of transaction
and valuation data, the process will depend on the data pass method
chosen. If Excel or CSV, the user
should enter (or download from his/her home system) all of the required
transaction fields to an Excel spreadsheet or CSV file named as you wish, with
the first row reserved for headings. All
of the required valuation data should be entered or downloaded to a second
spreadsheet. Both spreadsheets must be
the first sheets in their respective workbooks.
They can reside in any directory on the user side and PPMS need have no
direct access to them. Instead, the user
simply enters the filename (or Browses to find it) and clicks the "Import
Transaction/Valuation Data" button to send the files to their custom
sub-directory on PPMS (invisible to the user).
Note that the Transaction file name must be given first, followed by the
Valuation file. If only transaction data
is being entered, simply leave the second answer blank. No valuation file will be processed but the
transaction data will have already been accepted. In this case, there is no automatic
recalculation of performance, so you should click on “Calculate Performance”
for the months you want to change. If only valuation data is being entered,
click on the separate “Import Valuation Only” button. If your data pass method is set to one option
for transaction reports, e.g. Advent AXYS, but you want to use a different
interface for valuations, simply change the pass method on the batch operations
screen at the time of uploading. This
will cause PPMS to expect a file in the specified format for this upload only;
the default will remain as you had originally established it.
Once the new
valuation file is read, the procedure will automatically update your portfolios'
transactions and valuation(s). It will
at the same time calculate before- and after-tax performance for each of the
portfolios and each of the time periods specified. Importantly, if you have repeated any
valuation periods from prior uploads, the new ones
will automatically replace the old, thus allowing for any required valuation
maintenance and performance recalculation.
However, this is not true of transactions. All transactions in the file will be added to
your data (since it is quite possible to have multiple transactions of the same
type and even the same ticker on the same date), so BE SURE THAT YOU DO NOT
INADVERTENTLY CREATE DUPLICATE TRANSACTIONS; ALWAYS START WITH A CLEAN
SPREADSHEET FILE.
The Excel interface
determines the data type of each column based on the content of the first eight
rows of each spreadsheet. Therefore, be
sure to fill all columns of the first eight rows with zeros, even if the data
is not required. Leaving blanks for
“Income”, “Proceeds”, “Cost” or “PurchDate” on these rows may invalidate later
transactions in the same spreadsheet.
BE SURE THAT YOUR
CURSOR IS NOT LEFT BELOW THE ACTUAL DATA SECTION OF SUCH SPREADSHEETS WHEN THEY
ARE CREATED OR IT MAY CAUSE A READ ERROR BECAUSE THE
The CSV interface
does not have this data recognition problem, but commas are required for all
fields.
The Advent Axys and
Schwab Centerpiece accounting systems produce three reports which can be directly
fed into PPMS. PPMS actually makes
provision for two types of Axys interface, the first for equity portfolios, the
second for fixed income which includes a column for accrued interest. Make sure you select the interface
appropriate to your portfolios. First
run the reports on your accounting system for a single portfolio and time
period, saving them as separate CSV files.
Then, enter the name of the Recognized Gains/Losses report in response
to the first PPMS batch upload query (trading transactions), the name of the
Cash Account/Income report as the second (income transactions), and the name of
the Appraisal report as the third (valuation).
If you inadvertently use a different order, do not repeat a transaction
file; just start over at the batch upload point and add the remaining file;
then use the separate Import Valuation button to upload the third file. You may find it more convenient to use the
Axys reports for transactions (both types) but switch to Excel for valuation
uploads since the later will enable you to enter numerous portfolios for any
number of time periods in a single report and avoid the need to run separate
appraisals for each portfolio and each period.
The Security APL
Checkfree accounting system produces two fixed format files which can be
directly fed into PPMS. The first
contains all transactions, both income and cash flows. The second contains valuations. Both files can be created for multiple
periods and multiple portfolios at once, and PPMS can read all of the data at
once, so it is fairly quick to add large amounts of data.
Performance data can
be exported back to the user's home system as soon as transaction and valuation
data have been received (since performance calculation is done automatically
upon receipt). Simply click the
"Export Performance Data" button under "Batch Operations"
on the main PPMS menu to export valuation, summary income and cash flows,
summary taxes, and final calculated pre-tax and after-tax performance for each
of your portfolios to an Excel spreadsheet.
The performance data will first appear on your screen (in the same
format as stored on PPMS). The user must
click FILE SAVE
AS WEBPAGE and give it a filename and
location on your computer. Then open
Microsoft EXCEL and click FILE OPEN, making sure that the browser looks for the
file type "Web Page". Once the
performance data pops up in Excel, you can save it as a normal Excel or CSV
file, perform further calculations for presentation, or download it to your
in-house performance system. A sample
output spreadsheet is attached. All of
the data contained on the PPMS performance report is also in this spreadsheet
so users can customize their after-tax reports as desired, create portfolio
composites by utilizing other software systems, add graphics or print on
specially designed paper.
Performance
Comparisons: If you are attempting to
reconcile the pre-tax performance from PPMS with that calculated by your
internal accounting system, you should make every attempt to assure that all of
the same procedures are being followed in both places – (1) that all
transactions including cash flows have been entered both places, (2) that you
have selected the correct convention for cash flow timing (start of day vs. end
of day), and (3) that sub period revaluations are done on the same dates (no
more and no less). In general, the use
of more revaluation periods creates a better approximation to performance but
some systems may only revalue when the flow is more than 10% of portfolio
value.
V. Advanced Data Exchange
(FTP)
For clients with large
amounts of data to regularly import/export, PPMS has implemented an FTP
capability. Each such client will have
their own sub-directory. After FTPing
the two standard transactions and valuation files to their sub directory,
perhaps automatically at month end, the client will trigger PPMS to process
these files by starting a separate web page (not the normal perfat.com
page). At the conclusion of such
processing, PPMS will provide two output files in the same directory, one with
resulting performance data, the other an error file which lists any errors
during processing with sufficient explanation (including record numbers) to
enable the user to make corrections and reprocess those portfolios and periods
in the normal manner. It is suggested
that the input file names include the date in order for the client to keep
track.
VI. Adjustment for
Non-Discretionary Capital Gains
The AIMR-PPS
Implementation Committee, through their Sub-Committee on After-Tax Portfolios
in 1994, recognized that in order to create fair and comparable composites of
portfolio performance on an after-tax basis, investment managers should have
the option to adjust performance for capital gain taxes which were created by
client withdrawals, not as a result of the manager's own investment
decisions. The committee wanted to
assure that managers could not game such an adjustment and would continue to
make decisions in a manner favorable to the client, so this adjustment does not
focus on whatever gains the manager actually realized in a given period. Rather the adjustment is based on the ratio
of gains which would have been available, the amount of the client's
withdrawal, and the applicable capital gain tax rate.
PPMS implements this
option by computing a gain ratio equal to the realized gains during the period
plus unrealized gains at the end of the period divided by net client
withdrawals plus ending portfolio value.
Realized gains are already computed as part of the after-tax
calculation, but Unrealized Gains cannot be computed from the information
available to PPMS unless the user includes this field in the valuation record
(optional sixth field mentioned above).
Net client withdrawals start with the amount of "non-discretionary
withdrawals" (transaction type NDWD), summed over the period. This gross non-discretionary withdrawal is
reduced by the amount of any positive cashflows into the portfolio plus
cash-basis income from dividends or interest.
The existence of a NDWD transaction record automatically triggers this
adjustment calculation. An incorrect
(particularly zero) value for Unrealized Capital Gains will significantly
reduce the Gain Ratio and therefore the adjustment to performance.
The final adjustment
is the net client withdrawal x long term capital gain tax rate x gain
ratio. It was the intent of the PPS
Committee that this adjustment would only be positive, i.e. would add implied
taxes back to performance since they were created due to actions of the client. PPMS thus assures
that the adjustment will never be negative (as it might otherwise be if the
account started with large unrealized losses).
In the form of equations,
AdjustedAftertaxReturn = PretaxReturn - TaxBurden +
Adjustment
where
and
Adjustment
= 
The average invested
assets (sometimes called "weighted average assets") is equal to the
start-of-period portfolio value plus the sum of (cash flows x number of days
remaining in the period). This is a standard
approximation methodology attributed to Peter Dietz.
The net affect is
that if a manager is able to meet a client's withdrawal by selling higher cost
assets or taking money from existing cash (thereby holding realized capital
gains below the average which might have been expected), then the adjusted
after-tax-performance will be higher than without the adjustment. If the manager sells low-cost assets or
realizes short term capital gains when long-term gains were available, the
adjustment factor will be much smaller or even zero because he has not
minimized the client's tax hit while meeting the withdrawal.
VII. Error Messages
The following are
possible error messages and suggested corrections:
- Unregistered Client – The probable cause
is an inadvertent misspelling of the first field of the transaction or
valuation CSV files or Excel spreadsheets.
The client short name, as you created it when setting up your new
client, is case-sensitive. Be sure
that you log on and enter the name consistently in your data transactions,
i.e. abc is not the same as ABC.
- Client License not Paid – Account trial
period has expired or your service subscription has lapsed. Please call to renew.
- Client Has Too Many Portfolios – Your
service subscription is based on an expected number of portfolios. Please call to increase this number.
- Non-existent Portfolio – The probable
cause is an inadvertent misspelling of the second field of the transaction
or valuation CSV files or Excel spreadsheets. The portfolio short name, as you created
it when setting up the portfolio, is case-sensitive. Be sure that you enter the name
consistently in your data transactions, i.e. abc is not the same as
ABC. This error may be an indirect
result and follow Error Message 1, since an incorrect spelling of the
client short name would lead to non-existent portfolios for that client.
- Non-existent Transaction Type – The
probable cause is an inadvertent misspelling of the TxType field in the
transaction CSV file or Excel spreadsheet.
Check the list of allowed transactions in Section II.D above.
- TX Purchase Date outside reasonable
range – A purchase date is required for “SELL” type transactions, since it
is impossible to determine if the capital gain is long or short term
without it. The date field cannot be
null or earlier than
- Transaction Date outside reasonable
range – A transaction date is required for all transactions. It must be no earlier than
- Missing Cost Basis – A “SELL”
transaction must have an associated cost basis. If you don’t know, make it zero but it
cannot be left null. This error may
result from not putting zeros in this column in the first eight rows of an
Excel spreadsheet. Note the
instructions for Excel in Section IV above.
- Invalid Time Period (PerfIndic) –
Valuation records must include the time period for which performance will
be calculated. This field must be 0
for sub periods; 1 for monthly; 3 for quarterly; or 12 for annual. Duplicate records are required when the
same date valuation is being used as both the end of a sub period and a
full period.
- Valuation Date outside reasonable range –
A valuation date is required for all valuation/performance records. It must be no earlier than
- Month-end Record has non month-end date –
A valuation record with period indicated = 1 must have an actual month end
date. Check that you have used the
proper 30 or 31 or 28 (or 29 if the year was a leap year), regardless if
that date happened to have fallen on a weekend.
- Invalid Portfolio Full Name – When using
any of the accounting system interfaces, the full name of the portfolio is
used to link accounting system data to PPMS. Therefore, the correct full name must be
used when setting up the portfolio.
- Missing Proceeds – required for this
type of transaction --
A proceeds value is required for cash flow, tax withholding,
and sale transactions of all types.
Check that you have not put this value into the ‘income’ column
rather than the ‘proceeds’ column if using Excel spreadsheets.