value and cost basis report

[paulk]

Hi Mark

I have created a simple custom report which allows me to queicly check my client's portfolio values and cost basis by position against thier monthly statements. I simply took the portfolio value report and added a colum for avg cost to represent the basis. At first the reports generated fine.

Fast forward a month. Now if a run the report for November 30th the average basis for certain positions is incorrect. I have noticed that this happens only for positions which have activity in them after the date of the report, for example, a mutual fund has reinvested dividends dated December 1st.

Am I using the wrong colum for basis? Why is the program not generating the cost basis as of the date of the report?

Hope this makes sense and you can help me out.

Thanks
Paul

[Mark]

Hi Paul,

What is the exact name of the custom field you added? Is it "Tax basis, AVG (ending)"?

[paulk]

Exactly. That's the one.

[Mark]

Hi Paul,

Transactions recorded after the report ending date should not affect this field. Are you sure the report date is as you expect? If you do a test and delete a reinvestment dated _after_ the report ending date, does the report field change?

[paulk]

Mark

I did a test delete of the December 1st transaction and the report was then correct. I also verified that the custom field was indeed tax basis avg ending. I also verified that when I reentered the December 1 transaction the report was incorrect. Please help!

[paulk]

Also, to further confuse me when I reenter the December dividend reinvestment the avg tax basis is LOWER than when it is deleted although the share balance as of the report date is correct. I am at a loss.

[Mark]

Hi Paul,

Can you email me this one investment file (*.dat), along with the ending report date, what number you are seeing when the reinvestment is entered, and what number you are expecting to see?

To get a real email address to send an attachment, fill out our email contact form, submit it, and then you'll see a regular email address.

http://www.fundmanagersoftware.com/email.html

Also, are you using version 10.8?

[paulk]

Mark . I just emailed you the requested information.

[Mark]

Hi Paul,

Thanks, I received the file. When I tested it, I get the same number whether that reinvestment on 12/1/10 is deleted or not. I get the number you told me you were expecting to see for the correct basis, in both cases. I'm not sure why I can't reproduce the problem. Maybe it is due to some setting you have that I do not. Can you create a new portfolio file with only this one investment, and create the report that demonstrates the problem. Save this portfolio file, and then do a "File / Backup..." and send me this backup file. This way, I'll use your settings, portfolio file, and created report.

[paulk]

Mark

Sent it over. Please tell me it's just some stupid setting I have wrong....

[paulk]

Mark

I know this is a stretch but might it have something to do with the reconcile function? When I delete the December dated transaction I get a warning message about changing a previously reconciled item. I opt to allow the change.

Just a stab in the dark.

[Mark]

Hi Paul,

I was able to reproduce the same problem with your backup file. The problem relates to the use of "Options / Report Preferences... / Capital Gains / Use Wash Sale Rules". When Fund Manager calculates your cost basis for this report field, it is calculating what the tax cost basis would be if you sold all your shares on the ending report date. Just for the sake of calculation speed, Fund Manager is hypothetically selling the shares at a price of $1. In your case, this causes a problem because selling at a loss ($1 instead of a higher price that wouldn't cause a loss) and the fact that you re-purchase shares within the wash sale window, causes a wash sale adjustment, thus lowering your cost basis.

Thinking about this more, that field (Tax Basis, AVG (ending)) can sometimes depend on what you would sell the shares at when you are using wash sale rules. A more reasonable estimate on the part of Fund Manager (since it doesn't know what price you'd be selling them at) would be to use the market price on that date. Or, another possibility would be to ignore all recorded transactions past the ending report date, as if they weren't even recorded. I think this would make the most sense for this field, so we'll plan on this for the next release.

For now, you have 3 choices:

1) Turn off the wash sale rule option.
2) Report before recording future reinvestments
3) Use a pre-release of 10.9. Send me an email if you'd like this, and I'll send you out a pre-release with this change.

[paulk]

Thanks for the help Mark. I'm glad in a way it was a little involved and not just me being a bonehead.

I will temporarily disable wash sales and see if that fixes my problem.

I would need the wash sale option feature as this is one of the big reasons I switched to FM to begin with. So are you saying that with the 10.9 release I will be able to run this type of report and still keep my wash sale option active?

Thanks again for all your help.
Paul

[Mark]

Hi Paul,

Yes, with 10.9 you could leave on the wash sale option, and that field will calculate as you would expect. I'd be happy to send you a pre-release if you'd like.

[paulk]

Mark

As a follow up, is there any way I can run that same report to accurately reflect cost basis for both funds (accounted for on avg cost basis) and stocks / etfs (accounted for under FIFO)?

In the end, what I am trying to accomplish is to simply have the FM portfolio value report customized to show both my value and cost by position so I can quickly check that against my client's portfolio statements.