How to audit transfer in/transfer out

[flight]

Mark,

I have recently discovered a performance miscalculation due to the transfer in/transfer out (or transfer between) transactions in FM. As such, I am not comfortable with my accounting in FM and would like to audit (review & confirm) all the the transfer in & outs in my account. Can you advise how to do this? I will also provide details below on what caused the miscalculation.

On February 1st of 2016, Mens Warehouse (ticker MW) changed its name to Tailor Brands (ticker TLRD). In FM, I recorded a transfer out of MW and transfer in of TLRD on February 1st.

The 1/31 price of MW was $13.71 per share.
The 2/1 price of TLRD was $14.13.

However, FM somehow downloaded a price of $11.10 on ticker MW for 2/1, and when I did the transfer there was a performance hit in the amount of shares x different in price ($14.13-$11.10). Please note the market values and the OOP was exactly the same for the transfer in and transfer out.

When I looked at the price history for MW, I noticed this weird price on Feb 1st. When I adjusted the price to $14.13 on MW, the TWR for the month was corrected.

Can you please advise on this on:
1) Why this is happening?
2) What can I do to investigate and audit this among other Transfers?

This hit (negatively impacted) my TWR by approximately 50 basis points and I am not uncomfortable w/ my calculations in FM.

Sorry for the long winded topic, but I really need to get to the bottom of this.

I previously assumed if the market values on the transfer were the same, there would be no performance hit. This is NOT the case, and now I have proved it twice with the example below and the AMC/CKEC merger (where selection of date on the transfer changes performance/TWR).

[Mark]

Hi flight,

1) How are you updating the price when it gets the incorrect price? There are many ways a price can get updated, so I can't say how it got updated.

2) The actual market value needs to match the market value you enter for the transfer. If they are different it is like you bought/sold above/below market and thus the return will be different.

If you're still unsure of this, you can create a simple 2 investment portfolio, and do a transfer between on a particular date. If you do it at market value, the portfolio TWR should not change no matter the date you do the transfer, as long as you end up with the same value, and you do the transfer at the market value on that date. If you notice the problem with these 2 investments, you can email them to me along with the date range of the TWR calculation in question, and I can take a look.

Make sure the transfer out market value/date is the same as the transfer in market value/date and the actual market value. For example, if you transfer 100 shares on a date that has a closing price of $10, the market value of the transfer should be $1,000.

[flight]

Prices - I use the retrieve prices or retrieve historical prices function in FM. Source is yahoo.
Market values - The MV used in the transfer between matched the MV of Tailored Brands on Feb 1st. 1,000 shares x $14.13, $14,130.

However, the Transfer Out had a bad downloaded price, separate from the MV used in the Transfer Between function. This is the source of the problem.

And, when i go to delete the 2/1 price of Mens Warehouse, the system will not let me. So I had to copy the price of Tailor Brands ($14.13) even though Mens didn't traded that day (didn't exist).

I am less worried about the price discrepancy, stuff happens.

I more worried about using the intracacies of the transfer and transfer between functions, and IF there are any other incorrect things in my data. When you find one, you wonder what else is out there. The transfer stuff is very sensitive and can easily get messed up.

[Mark]

Hi flight,

I'm not sure of a way to easily check all your transactions. Of course, all recorded data is important in order to get correct performance results. Making sure all your data reconciles is one good first step to make sure you're not missing any transactions that affect share balance. This doesn't verify the transaction prices are all correct, but at least all the transactions are there with the correct shares.

You do have to have a recorded closing price on any date that has a transaction.

If you're worried more about the transfers, you could run an Investment Transaction report, and filter "Include Only Portfolio External Transactions", which will show all transfer type transactions (and any other portfolio external transactions). This will at least give you a list of transfers you have recorded, so you can go through them, and verify they are recorded properly.

[flight]

Thanks Mark, that is what I plan to do.

Can you provide me the rules of the road when you do either a transfer in/out or transfer between:

1) the market value of the Transfer In must = the market value of the Transfer Out.

2) the market value of the transfers above must equal the underlying market value, based on the closing price in FM of which security: the transfer in or transfer out?

3) Each security involved in the transfer in & out must have a price in FM. If there is no observed price for the transfer out, then by default it has to be equivalent to the transfer in?

Trying to catch all of the intracacies which can trip up performance calculations.....

[Mark]

Hi flight,

There aren't any absolute rules on what you must do, but in general yes, the transfer out value would equal the transfer in value when you're moving shares/value. This transferred market valued is generally also equal to the market value of the shares at the time of the transfer. These aren't requirements placed by Fund Manager, as you may need to record something unique.

Fund Manager does require that a closing price be recorded on any date where you have any type of transaction. If you record a transaction on a date without a closing price, Fund Manager will update the closing price using the transaction's price.

[flight]

While I am trying to learn how to do these properly, they still perplex me.

Here is another example, of an "exchange offer" between GE and Syncrony Financial (SYF).

My account went from $68,101 to $64,167, for a change of -5.78%. All else equal, i would expect FM to show this. There are no cash deposits/withdrawals.

FM shows: -6.10%.

I have only done three transactions during the month, including the above mentioned exchange offer on November 23rd 2015. It is obvious I have done something wrong, but cant figure it out. Any ideas?

I check the log:

Cash Flow Period: 11/ 1/2015 - 11/23/2015 BMVi: 68100.642054 EMVi: 63438.725146 Yieldi: -6.85 Yield: -6.85

Cash Flow Period: 11/24/2015 - 11/30/2015 BMVi: 63659.965146 EMVi: 64166.648890 Yieldi: 0.80 Yield: -6.10

Annualizing... Calculation Span (years): 0.081967 Adjusted to Term (years): 0.081967 Annualizing Result: -6.10 -> -6.10
Result: Success
Yield Result = -6.10

[Mark]

Hi flight,

There was an external flow on 11/23/15, as you can see Fund Manager broke up the yield term on this date. If there was a net 0 added/removed the ending value of one period would equal the beginning value of the following period. In your case 63438.73 is not equal to 63659.97. Did you have some transfers? For the TWR to be the same as the value change in your portfolio, any transfers should done at the market value.

You can view all your portfolio external transactions by creating an "Investment Transaction" report, and turning on this filter option to only show portfolio external transactions. The TWR log file should also show the adjustments made due to portfolio external transactions. All transfers are always considered external.

Since you are thinking in terms of your portfolio value change you may want to use ROI instead of TWR. ROI shows how your invested money did. TWR shows the underlying performance, but is not money weighted. If you change the weighting of your invested money in the investment, then the ROI will differ from the TWR. You are changing the weighting of your invested money if you do not have transactions at the closing prices. So, if you're trying to get your TWR to match your ROI when you have no external flows, make sure your transfer values match. The number of shares transferred out times the closing price of those shares should equal the number of shares transferred in times the closing price of those shares.

[flight]

The different in the ending market value (63438.725146) of period # 1 and the beginning market value of period #2 (BMVi: 63659.965146) is $221.24

This is precisely the difference between the value of the shares received in the Exchange offer.
I exchanged 99 GE trading $30.59 (3,028.41), and received 103 shares of SYF trading at $31.55 (3,249.65). Difference is $221.24.

Once again, I am not receiving "credit" for the favorable exchange (appreciation) of stock.

The MVs used in the transfer are identical at $3,028.41 for the Transfer In & Transfer Out.

It continues to be perplexing.....

[flight]

I will just add that I do have the official closing prices in FM for each security, $30.59 for GE and $31.55 for SYF.

And, thus I have checked off the unofficial rules of the road for the Transfer In/Out function.

[flight]

It looks like when you have 2 different/independent securities exchange for each other:

1) the market value of the transfers should be = to the MV of the Transfer In

2) the market price in FM for the Transfer Out needs to be manipulated to the implied trading price based on the value of the Transfer In

In my example:

I changed the MV of the transfers to $3,249.65 for both the In/Out based on the market value of the transfer in (103 shares SYF @ $31.55).

I then manipulated the closing price of General Electric (GE) in Fund Manager based on the implied value of the Transfer In ($3,249.65 / 99 GE shares = $32.825. This compares to the observable (official close) market price of $30.59.

When you do that, the TWR becomes -5.78% instead of -6.10%.

Any thoughts there?

I appreciate all your help Mark, but hopefully you can sympathize why I am going through this review & exercise. 50 basis points here, 30 basis points there. You do this 5-10 times a year, and your performance is remarkable different.

[Mark]

Hi flight,

TWR is reflecting what happens when you re-weight your portfolio from GE to SYF on that date. It doesn't care what you paid/received for the shares. TWR ignores your invested dollars, and the timing of your buys/sells in the portfolio. TWR looks at the underlying portfolio, which it sees being comprised of GE up until this date, and then SYF. The fact that you essentially bought below market value by trading 3028.41 worth of GE for 3,249.65 SYF will not be reflected in the TWR. It is like you bought a stock below the closing price. The benefit of buying below market value is not reflected in TWR, it would only be reflected in ROI.

In order for your TWR to match your ROI, the value of the shares removed would need to match the value of the shares added. If you recorded a closing price of SYF for (3,028.41 / 103) then the TWR and ROI would match.

TWR sees the removal of 99 GE at $30.59 and an addition of 103 SYF at $31.55. It doesn't see that you actually only essentially paid 3,028.41 for these SYF shares. TWR ignores the market value of the transfer in/out transactions. It ignores if you paid/received above/below market value, and only considers the makeup/performance of the underlying investments in the portfolio.

You might take a look at ROI, it sounds like you are more interested in this performance figure. ROI is generally what individual investors care about, as it is the performance earned on your invested money.

[Mark]

Hi flight,

I had another thought on how you can record this case. In your example you had $3,028.41 worth of GE exchange for $3,249.65 worth of SYF. The following seems to more accurately track what happened, and maintain a consistent TWR and ROI without recording fake closing prices:

1) Distribution from GE for $221.24
2) Transfer Out GE at a Market Value of $3,028.41
3) Transfer In SYF at a Market Value of $3,249.65

$221.24 + $3,028.41 = $3,249.65

By adding the distribution you are matching the value out/in, and getting credit in your TWR returns. You didn't add/receive any cash, but you received $3,249.65 of benefit from GE, so to accurately account for that you need the distribution since the market value of the transferred out shares is less than this benefit amount.

It doesn't matter what type of distribution you record, but you might define another distribution type, and put it there, so you can keep it separate from other distribution types.

[flight]

Thanks Mark. I appreciate you continuing to look at this.

In my opinion, this should definitely be a part of the TWR, that's why I did the trade to begin with because there was a profitable "arbitrage" opportunity between the two securities. And it is part of the appreciation of the overall portfolio.

For the 1st method, when you have two independent securities, one of the stock prices in FM will need to be manipulated versus the observed trading price. If you book the transfer using the Transfer Out market value, the implied price for the Transfer Out will need to be recorded in FM. Alternatively, if you use Transfer Out market value, the implied price for the Transfer In will need to be recorded in FM's price register.

Do I have the above correct?

It's doable but still a bit complicated. I'd have to think there are many people on this board are potentially impacted by this, particularly around mergers & acquisitions. But at least we got to the bottom of this!

For the 2nd method, you would record a distribution for the difference in the market values of the Transfer In/Transfer Out. Does this distribution hit the cash ledger? Would I be breaking on cash?

I am leaning towards the 1st method, although slightly more difficult.

[flight]

I meant to say:

If you book the transfer using the Transfer Out market value, the implied price for the Transfer In will need to be recorded in FM. Alternatively, if you use Transfer In market value, the implied price for the Transfer Out will need to be recorded in FM's price register.