Exec Summary Reconciliation

[elliots]

Mark,

Sorry for continuing to bug you on this, but I'm stilling having problems reconciling the Contributions and Withdrawals. Here's the relevant data:

Default cash accounts in 2 subportfolios (Joint, and Fee) for a client subportfolio. The Fee subportolio only has the cash account. For calendar year 2010, there are 4 Transfers out from the Joint account and 4 transfers in to the fee account ($2355.42, 2923.25,3012.78, and 2919.48 -- the last 2 with the same date in the 4th Qtr -- no Transfers in the 3rd Qtr). Also in the fee account are 4 account fee transactions with negative amounts reinvested for those fees. All of these transactions show the same amounts in the shares, value, OOP, and basis fields.

If I run a transaction report for the CY 2010 with the show only external filter I get only the 8 Transfer In/Transfer Out transactions. (I understand that's as you intended --OK so far.)

If I run the Exec Summary for the Last Quarter with show YTD turned on I get the following.
Q4 YTD
Contributions 0 5,278.67
Withdrawals 11,864.52 22,421.86
Account Fees 5,932.26 11,210.93

If Transfers In are Contributions and Transfers Out are Withdrawals, the Contributions for Q4 should have been 5,932.26 and the YTD should have been 11,210.93 -- the last 2 Transfers In appear to be missing. It appears that the Withdrawals are counting both the Transfers Out and the Fees in the YTD amount, but the Q4 Withdrawal number is off by 653.59. The fee numbers seem to be OK.

If I run the Exec Summary for Q1, the numbers are OK. For Q2, the Withdrawal number is off by 567.83. Q3 looks OK.

Whatever is causing the discrepancy in the Withdrawal total is not showing up in the Transactions report as an external transaction. The fee transactions appear in the Withdrawal amount (which seems reasonable), but don't show on the Transaction Report (with external only filter).

As a separate issues, shouldn't Foreign Transactions show up in the Transaction report with the external only filter?

Elliot

[Mark]

Hi Elliot,

It may be easier for me to help if you can send me this sub-portfolio. If you're okay with this, just copy/paste this one client sub-portfolio containing the 2 subs, into a new portfolio file (*.MM4). Make a backup of this new/smaller portfolio file, and send me the *.FMB file. Please create the report in the .MM4 file that you'd like me to look at. Let me know if you need an email address.

What do you mean by "Foreign Transactions"?

[Mark]

Hi Elliot,

Thanks for sending the backup file. The short answer is that because you have the account fees recorded before the transfer in transactions, this is causing the problem.

To fix it, go into your '$Cash Account for Fees' investment, select both the account fees on 10/18/2010, and copy them to the clipboard (double down arrow, or right mouse menu). Then, delete them. Then, re-paste them back into the investment. If sorting by Date you should now see them listed as the latest transactions, and your report will calculate as expected.

Longer Explanation:
Your fees and transfers are recorded for the same date, but the order is important too, even within the same date. By having the fee first, your cash account for fees investment has a negative share balance after the fees are recorded. This leaves you with a short position. This means the definition of transfer in/out are essentially backwards at this point. A transfer in of shares when you have a short position is "closing" your position, not opening one. When you have a transfer of any kind (in/out) that closes a position, this is treated as a transfer out for the purposes of calculating the OOP cost. Whenever you have a transfer that opens or adds to a position, you specify the OOP cost. The Transfer labels of In/Out are really only appropriate for long positions.

To avoid this admittedly confusing issue, just make sure you Transfer In money to your fee account before you take it back out with the reinvested account fee transaction.

On your other question about Foreign Tax Withheld being treated as external... It depends how you have these recorded. If they are negative distributions, and the money comes out of the cash account, this is internal. Money went from cash to pay for a negative distribution. There was nothing external to the account. Alternatively, if you record these as only redemptions in your cash account (with no corresponding negative distribution in another investment), then yes, they will be external.

[elliots]

Mark,

Thanks for explaining the strange numbers on the report -- I fixed it and it's fine now.

Thanks also for the explanation on the foreign tax paid (FTP). I now see how I can get what I wanted all along with the FTP. I've been taking the data as downloaded from Scottrade which is the Foreign Tax Paid transaction in the investment (a negative distribution), and cash out from the cash account. I need to delete the FTP transaction in the investment, and leave the cash redemption in the cash account. This will take away the reduction in ROI on the investment and leave the FTP in the cash account (which is now unmatched) as a withdrawal. If I'm right this will give me the result I've wanted on the FTP issue. The only downside is the FTP transactions will no longer show up on the Distribution Summary (which I usually provide to the client's tax preparer to bump up against the 1099 info from the custodians). Do you see any other "unintended consequences" to my proposed treatment of the FTP?

Elliot

[Mark]

Hi Elliot,

Your explanation covers it fine. I don't see any other downside. As you pointed out, the only downside is that it won't be a categorized distribution anymore.