Taxable vs. Non-Taxable Income.

[aviator]

Hi Mark,

I need a report that shows taxable vs. non-taxable projected annual income, preferably at the account level.

An investments properties, under the Other tab, has a tax free flag (which can be checked or unchecked by me). I believe this flag will tell me what I need to know at the account level. How does one create/run a report using this flag?

Thanks in advance.

[Mark]

Hi aviator,

This property is only for use in a couple reports and graphs. You cannot sub-total or list by this property in general, like you can for investment type for example. For the Distribution Summary and Capital Gains reports you can exclude tax-free investments. See the "Options / Report Preferences..." dialogs for these options. For graphs, you can do a pie chart or a stacked value graph by "Tax Status".

[aviator]

Thanks, Mark, but I'm not sure I'm making myself clear on what I need, so let me try this a different way.

My investments throw off income in the form of dividends and interest. The Projected Annual Income field is fairly accurate, but it doesn't account for taxes (meaning that it's overstating how much income actually flows into my wallet).

My investments break down as follows:
- IRAs (essentially untouchable until I reach 65)
- Other accounts which include a mix of taxable and non-taxable investments.

I'm looking for a projected income report that will:
a) Exclude the IRAs for now (until I can tap into them);
b) Take the remaining investments and, if they are taxable, subtract 40% for tax liability; if they are non-taxable, use 100% of the Projected Annual Income.

I've tried to define a field to do the above, but I'm having a heck of time passing validation. The formula I'm using, which is obviously incorrect, is:
IF(Tax free=="No", (Projected_annualincome * .60), (Projected_annualincome * 1))
Any suggestions?

[Mark]

Hi aviator,

Unfortunately, the user-defined fields only make use of number and date fields. They can't utilize property fields, like tax status to make a decision. This would be a good improvement, but it isn't there today.

One other possible idea you could consider is to make use of the new "link" sub-portfolios. You could organize your accounts into taxable and tax-free sub-portfolios. For example:

Code: Select all

- Master
  - My Holdings
    - Taxable Account 1
    - Taxable Account 2
    - Tax-Free Account 1
    - Tax-Free Account 2
  - Accounts by Tax Status
    - Tax Free
      - Link to Taxfree-acct1
      - Link to taxfree-acct2
    - Taxable
      - Link to Taxable-acct1
      - Link to taxable-acct2

This way, you could report on "Accounts by Tax Status", and get sub-totals for each kind. You could write your user-defined fields to multiply by 0.6, but only look at it for the tax-free sub-portfolio.

[aviator]

Thanks for your quick response, Mark, but there's a small problem with your suggestion. Some of the taxable accounts contain a mixture of taxable and non-taxable investments. So I don't think that reporting at the account level will work, which is why I was trying to use the tax-free flag.

I'll play around with this to see if I can make this work. I like the idea of using linked accounts but my mixture of taxable and non-taxable investments within the same account is somewhat of problem.

A suggestion for a future improvement would be to add a tax-handling module (perhaps fee-based?). My portfolio contains a wide variety of investments. Some are exempt from both state and federal taxes, others just federal but not state, and still others that are exempt from neither. Wrapping my hands around what's what -- and making sound investment decisions -- is getting more difficult.

[Mark]

Hi aviator,

Yes, my suggestion wouldn't work if your account is a mix. If you wanted, you could create another layer of hierarchy underneath your account sub-portfolio, like:

Code: Select all

- Account
  - Taxable
  - Tax-Free

If you're retrieving, leave the account number/retrieve options set at the "Account" level. As you get new investments, you would need to move them into the appropriate lower level sub-portfolio.

Thanks for the feedback/ideas.

[aviator]

Mark, maybe I'm trying to be too clever, but I'm so close to making this work that I feel it's worth one more shot.

I took your advice and created two linked accounts, one containing tax-free investments and the other containing taxable investments (just as you described above).

Then, based on what you said about defined fields working only on numeric/date fields, I did the following:

- For each of my taxable investments, I created a dummy note with a dummy date. (I used 1/1/1900 and text of "test".) The tax-free investments got nothing, and since I haven't previously used the notes field, their note count remained at zero. The result of this step is that each taxable investment now has a numeric field (notes count), on which I can perform a calculation.

- I then defined a field called Post Tax Annual Income with the following formula:
IF( Notes_all > 0, Projected_annualincome * .60, Projected_annualincome*1 )

- So far so good. The Post Tax Annual Income field (column) correctly calculates net income depending on whether or not an investment is taxable or tax-free. This field contains both tax-free income and taxable income, with the taxable investment income appropriately reduced by 40%.

But, as you might have guessed, there's a small problem. The grand total, at the bottom of the report, is incorrect. Since the grand total notes count is > zero, the software is calculating 60% of the grand total income. But the income grand total is a sum of taxable and tax-free income and should not have the formula applied to it. Instead, it should merely sum the Post Tax Annual Income.

Sorry for the long post. I'm one field away (the grand total amount) from this giving me exactly what I need. I'd appreciate any suggestions you might have to make this work.

Thanks,

[Mark]

Hi aviator,

Very clever... I never thought of doing it that way. I don't have any suggestion to get the portfolio line correct though. One work-around would be to use "File / Export to CSV..." and then sum that column in Excel.

[aviator]

Thanks, Mark, I'll give that a try. I was hoping to avoid using Excel... not as handy as having a report with FM.