Change in unrealized gains

[kathy2cats]

1. I am trying to create a custom report to show the change in unrealized gains from the beginning report date to the ending report date. The data from this report is used by the treasurer to book the investment on an accrual method. I've tried many things, but because there isn't a field for the beginning unrealized gain, I can't even make a custom field.

2. Is there a way to combine several reports into one report. I work for a charity and we have about 15 accounts where I track the investments. Currently, I have to print out 6 reports for each account.

Thanks,
Kathy

[Mark]

Hi Kathy,

1) You can get the change in the unrealized gain between the starting/ending report dates by defining your own field with this equation:

Taxunrlzgain_Assigned - EVAL(STARTDAYS() ,STARTDAYS() , Taxunrlzgain_Assigned)

That calculates the tax basis unrealized gain on the ending report date, and calculates it again on the report's starting date, and subtracts the starting value from the ending value to give you the change.

2) You can't combine different report types into 1, but if you're using multiple reports that have data available in a Custom report, you can consolidate the information into a single Custom report. You couldn't for example combine an Investment Transaction report that lists by transaction with a Custom report that lists by Investment though.

You can use the "Batch Print/Email" feature to help print all these out. Set up a client for each account. Use "Edit / Client List...". Each client is assigned to own a particular sub-portfolio. Now, create your reports that you want to print for each client (all 6 if necessary), and then use "File / Batch Print/Email...". You'll see all your clients listed there. Just put a check mark next to each one you want printed. Press the "Options..." button to check the output settings you want. You can have it go to a printer, sent by email, dumped to images, etc... Press "Print/Email" and all of those 6 reports will be printed for each of your selected clients.

[kathy2cats]

Two questions:
1. Shouldn't it be Taxunrlzgin_assigned - EVAL(STARTDAYS(),ENDDAYS(),Taxunrlzgain_Assigned)?
2. Should I be entering the start/end dates into the equation?

I have actually tried all sorts of combinations and the result is always zero.

Thanks again.
Kathy

[Mark]

Hi Kathy,

No, it should be as I originally posted:

Taxunrlzgain_Assigned - EVAL(STARTDAYS() ,STARTDAYS() , Taxunrlzgain_Assigned)

and then also set your report date range to start on 1/1/14 and end today.

The Taxunrlzgain_Assigned gives you the gain as of the ending report date.

The EVAL(STARTDAYS() ,STARTDAYS() , Taxunrlzgain_Assigned) evaluates Taxunrlzgain_Assigned assuming you had a report date range that started and ended on whatever your report's starting date is (STARTDAYS()). This is why you want to set your report start date to 1/1/14, and then this will evaluate to whatever the gain was as of 1/1/14.

You don't need to enter any dates into the equation. The STARTDAYS() function evaluates to whatever your report's starting date is. The EVAL function is evaluating the last parameter (Taxunrlzgain_Assigned) as if the report were run from your report's starting date to your report's starting date (the first and second parameters set this, which is why they are both STARTDAYS()). So, use the above equation, and set the date range of the report to 1/1/14 - 10/24/14 and you will get the change in unrealized gain from 1/1 to 10/24.

If you want this change to include any changes that happen on 1/1 you really should start the report on 12/31/13. You could either start your report on 12/31/13, or modify the equation to:

Taxunrlzgain_Assigned - EVAL((STARTDAYS()-1) ,(STARTDAYS()-1) , Taxunrlzgain_Assigned)

This is a complicated feature, so let me know if you have any unanswered questions.

[kathy2cats]

Hi, Mark,

To make sure I got the equation correct, I copied and pasted what you posted. I changed the start date of the report to 1/1/14 and the result on the report was 0.

The ending unrealized gain on 12/31/14 was $65,543.66 and the ending unrealized gain on 9/30/14 was $75,638.52. So, by my calculation, the change in unrealized gain over this time period should be $10,094.86. I'm not sure why the equation isn't working.

Additionally, we work on a fiscal year of April 1 to Mach 31. Will I be able to run this calculation using those dates?

Thanks!
Kathy

[Mark]

Hi Kathy,

This equation will work for any dates you want, just set the report date range to whatever you want. I thought you wanted it from the beginning of the year, which is why I told you to set the start date to 1/1/14. If you want the change from 9/30/14 to 12/31/14, set your report date for 9/30/14 to 12/31/14, and use the equation:

Taxunrlzgain_Assigned - EVAL(STARTDAYS() ,STARTDAYS() , Taxunrlzgain_Assigned)

This assumes you want the difference in the built-in custom report field:

Tax basis unrealized gain, Assigned Method

[kathy2cats]

Mark,
(Aren't you glad it's Friday!)

I'm still getting the value of zero using the equation you provided. To check my work, I ran a custom report looking for the Taxunrlzgain_Assigned for report ending 6/30/14 ($77,747.58) and again for report ending 9/30/14 ($75,638.52). If the equation was capturing these two numbers, the result should be -$2,109.06.

Apparently I'm doing something wrong. I even deleted the old custom report field, and created a new one. But still, the answer is zero.

Help?!?!

Kathy

[Mark]

Hi Kathy,

Please give me a call at 480-705-0129. We can do a quick web meeting, and we can take a look together.