Custom Report by Investment Goal.

[aviator]

Hi Mark,

I'm having trouble with a custom report where the rows don't add up to the subtotals. As you might have guessed, this has to do with investment income. Here's what I've done:

1) I created sub-portfolio called "Tax-Free vs. Taxable Investments", and below it I created two more sub-portfolios, one for taxable investments and the other for tax-free investments.
2) I copied my taxable investments to the taxable sub-portfolio, and the tax-free investments to the tax-free sub-portfolio.
3) The totals for the "Tax-Free vs. Taxable Investments" sub-portfolio are correct.
4) I created a custom report to run over "Tax-Free vs. Taxable Investments", with the following settings: Subtotal by sub-portfolio is checked (this gives subtotals for tax-free and taxable investments); list by Investment Goal (this further breaks down taxable vs. tax-free investments into categories like muni bonds, money market funds, etc.)
5) The problem is that the investment goal rows don't add up to the sub-portfolio subtotals. The subtotals are correct, and so is the grand total. But the investment goal rows appear to be pulling data from the entire portfolio, even though I've selected only the sub-portfolio called "Tax-Free vs. Taxable Investments".
6) The incorrect rows are all multiplied by a factor of exactly 3, which also happens to be the number of occurrences that each investment appears in my portfolio.

Am I doing something wrong, or have I discovered a glitch?

Thanks.

[Mark]

Hi aviator,

I tried, but couldn't reproduce this problem. I assume you are using version 11.3? What field are you seeing this problem with in the Custom report? I tested it with End Value and Total distributions (between) as you can see in this picture:

[aviator]

In your example, it appears that you have only one occurrence of each security. In other words, your portfolio doesn't contain duplicates like mine does. (In my portfolio, I have securities in three different sub-portfolios.)

There are two fields that are "tripling up" in my portfolio:
Value (ending)
Projected Annual Income.

I have a couple of user-defined fields, and both are correct.

I also have other standard fields that are also correct. The other standard fields are:
Distributed distributions (between)
Reinvested distributions (between)
Total distributions (between)

[Mark]

Hi aviator,

I do have duplicates. I have 2 investments: Goal1inv and Goal2inv. Goal1inv is located in both the "Copies" and "Lev2a" sub-portfolio. The Goal2inv is located in both "Copies" and "Lev2b" sub-portfolios.

Are your copies outside of the sub-portfolio being reported on, like in my example here? That was my assumption.

[aviator]

Yes, my copies are outside of the sub-portfolio being reported on. Let me explain further how I've set this up:
1) My portfolio, at the highest level, is set up for my wife and me. Her accounts are virtually identical to mine (there might be a share or two off, but for the most part she owns everything that I own). So we have a "his" and "hers" accounts.
2) For this discussion, I've taken all her taxable securities and copied them to the "taxable" sub-portfolio. I did the same for mine.
3) I then took all her tax-free securities and copied them to the "tax-free" sub-portfolio. I did the same for mine.
4) These two sub-portfolios, sit right below my "Tax-Free vs. Taxable Investment" sub-portfolio, which is what I report on.
5) So now, we do have duplicates in the taxable and tax-free sub-portfolios -- one for her and one for me. But that is correct. The sub-portfolio amounts in the portfolio editor are all correct.
6) But when I run my custom report, each investment goal is tripled in value even though the subtotals and grand totals are correct (that is, they match the totals in the portfolio editor).

I don't know if this makes a difference, but virtually all the investment goals I use have been modified. I've added investment goals (and labels) to suit my needs and therefore all of my securities do not use the standard goals provided with the software.

Edit: While on the report screen, I hovered my cursor over an investment goal that's incorrect (one called "Individual Municipal Bonds"). I clicked on this investment goal to "drill down" and see what is being summed for this ending value. The drilled down report lists all of our individual muni bonds, for his and hers, which is correct. The total in the drilled down report is correct, but does not match what my custom report is showing.

Edit 2: While on the report screen, I clicked on Settings and ran the report using different "list by" criteria.
Of the five options available to me -- Investment, Symbol, Investment Type, Asset Type, Investment Goal and Totals Only -- only two displayed the correct data: Investment and Totals Only.

[Mark]

Hi aviator,

I believe my sample is set up the same as what I think you're describing. Maybe you could either email me a backup (*.FMB), so that I can reproduce it here, or put together a simple sample demonstrating the problem, and post the backup on the forum.

[aviator]

Hi Mark,

I'll create a sample subset this weekend and email it to you. Please send me your email address.

Thanks.

[Mark]

Hi aviator,

I've just now sent you an email.

[aviator]

Hi Mark,

Thanks for your email address. I sent you a reply, but please disregard it.

This morning I had a breakthrough in what's causing the problem. It has to do with my defined fields. I removed them from the custom report and everything is correct. I'm still not sure why the defined fields are the problem.

I have two defined fields, which are as follows:
Projected Income % with the equation (Projected_annualincome/End_value)*100
and
Total dist % (btw) with the equation (Totaldist_btw/End_value) * 100

Both show 2 decimal places. And both pass validation.

My custom report has 5 fields, in addition to the two above. They are (as they appear from left to right on the page):
Value (ending)
Projected annual income
Distributed distributions (between)
Reinvested distributions (between)
Total distributions (between)

Any ideas what could be causing this problem?

[Mark]

Hi aviator,

Thanks, this enabled me to reproduce the problem. It is a bug in Fund Manager. It has been fixed for the next release. If you'd like me to email you a pre-release of 11.4 with this fix, please let me know.

[aviator]

Hi Mark,

Thanks for ferreting out this problem, especially on a weekend.

Yes, I would like a pre-release of 11.4. You have my email address, please send it at your convenience.

[Mark]

Hi aviator,

I've just sent it...

[aviator]

Hi Mark,

Thanks, I received it and tried it this morning. All is well, everything works as it should.

[Mark]

Hi aviator,

Excellent, thanks for your help in finding this bug.