TWR report

[mfmelb]

Hi Mark,

I ran a TWR report and there is an investment which was sold a few months back which is still showing the 1 month performance figure eventhough we are not holding it anymore.
I want to keep the investment in the report because I want the 3 and 6 months performance figures.
It seems to give a N/A for longer term performance figure if I have not held the investment that long and I was expecting that it would show a N/A in the 1 month column since it is no longer held but the longer term column should still be showing the appropriate values based on the exit date. How do you configure the report so that it does the above?

Thanks

Matt

[mfmelb]

Hi Mark,

Further to my earlier post on the TWR report, I notice that if I have the prices of an investment updated eventhough it has been sold, the TWR report would continue to show returns. Is this the correct?

I would like to keep open some of these investments as we do buy and sell the same investment at different times but would like to ensure that the reporting are correct during those time that we are out. For example, if we sell down all of it and then buy in again after 6 or 12 months, we would like the report to show that the 1,3 and 6 months figures to be N/A as there was no investment. However, for the 12 months, 2 years figures should be there since we did hold it during those period.

Regards

Matt

[Mark]

Hi Matt,

It sounds like you may really want the ROI yields, instead of TWR. TWR ignores external buy/sell transactions. For an individual investment, only pricing data is necessary to obtain TWR. It doesn't matter when you bought/sold.

ROI is both time and money weighted, so it factors in your purchases and redemptions, and shows what your money earned.

TWR is only time weighted (not money). It ignores when you added or removed money to whatever item you're calculating TWR for.

For more reading on this issue, please see:

http://www.fundmanagersoftware.com/help/yields.html

[mfmelb]

Mark,

Thanks for the reply.

I have one case where I bought a stock on 7 Mar 2011.
When I run the ROI report for period ending 31 Mar 2011, it is giving me a N/A for all the time frame including the 1 month.
I was expecting to see a value under the 1 month column since it was bought in that particular month for the report.
Is this a bug in v.11? or am I doing something wrong here?

Thanks

Matt

[mfmelb]

Hi Mark,

Further to my earlier message, I would to add the following:

I think I am trying to get a performance figure which is somewhere between the ROI yield and TWR. Basically I want to get the actual performance of a stock without the cashflow impact. In the case of a stock purchase on 7 Mar 2011 and doing a report for end of Mar 2011, I want to know the actual performance of that stock without the impact of cashflow. If I choose the TWR report, the 1 month performance figure will be base on the stock price at 1March and 31 March which is not the actual case since I bought it on the 7 march. If I choose the ROI yield report, it will give me a performance figure which includes the cashflow impact as well. How can I get a report that will give me the performance figure just from the stock alone. Normally, one would just use the purchase price and the 31March price to do the calculation. Which report would give me what I am after?

Tks

Matt

[mfmelb]

Mark,

I think to get what I want is to use %gain and to do this under customised reporting.

Am I able to produce a report similar in format to TWR or ROI report from the customised option? Can't seem to find the option to do the 1 mth, 3 mth, 6 mth, 1 yr, 3 year and 5 years all in a one page wide option for all investments.

Basically I want to be able to get the same report like the TWR and ROI except that the field to use is %gain.

Matt

[Mark]

Hi Matt,

I have one case where I bought a stock on 7 Mar 2011.
When I run the ROI report for period ending 31 Mar 2011, it is giving me a N/A for all the time frame including the 1 month.

Since the 1 month term runs from Mar 1 - Mar 31, and you didn't own it over that full period, by default the ROI will report N/A. If you want, you can turn off "Options / Yield Interpolation Range... / Only Report ROI Yields When Item is Owned for Entire Yield Term", and then you will get a number. ROI is time and money weighted. Since you didn't own it for the first 7 days, those 7 days have no weight. Basically, only the performance from Mar 7 - Mar 31 will be used. The rate reported is scaled to the full period though, so if you gained 10% in this time, the ROI for the full month will be higher than this.

Am I able to produce a report similar in format to TWR or ROI report from the customised option? Can't seem to find the option to do the 1 mth, 3 mth, 6 mth, 1 yr, 3 year and 5 years all in a one page wide option for all investments.

Basically I want to be able to get the same report like the TWR and ROI except that the field to use is %gain.

You could create a Custom report with %Gain (between) and use 6 date ranges, setting them to the date ranges you want. If you're using the Professional version, you could also use the new feature to define your own fields, and create 1M, 3M, etc %Gain fields to include in your report.

[mfmelb]

Mark,

I have used the customised report and used the different date ranges for the %Gain.
Would be great if I can do the 1 mth, 3mth etc like in the TWR, ROI reports where you only need to specify the end date - can you point to the section of the manual that tells me how to do this? I have the advisor version so the features you mentioned for the professional version should be available as well.

Thanks

Matt

[Mark]

Hi Matt,

From within a Custom report, choose "Edit / Custom Settings... / Define Fields...". To create a 1M version of %Gain (between), use this formula:

EVAL(DELTADAYS(ENDDAYS() , 0 , -1 , 0)+1, ENDDAYS(), PGain_btw)

It may take some getting used to, but that equation evaluates the PGain_btw field (% Gain (between)) for 1 month back from the ending report date. Press the "Help..." button in this dialog to see all the documentation on this. A 3M version, would be very similar, and just be:

EVAL(DELTADAYS(ENDDAYS() , 0 , -3 , 0)+1, ENDDAYS(), PGain_btw)

You can define as many custom fields as you want. A 1 year version, would be:

EVAL(DELTADAYS(ENDDAYS() , -1 , 0 , 0)+1, ENDDAYS(), PGain_btw)

[mfmelb]

Mark,

Equation works.
Now to fine tune a few more things.
How do I get it to prompt for the end date so that it can calc the period?
I.e. if I set end date to 31 Mar, it will pick 1 March to 31 March as the 1 month performance, 1 Jan to 31 Mar for 3 months performance etc. I think this will also fix the title area at the top. I currently use the edit setting to give it the end period but I also have to specifiy the start period which is then pickup and printed at the top. Would be good if a prompt comes up to ask when the end date is - like the other reports (i.e. TWR, ROI).

I notice as well two other inconsistencies - it is not giving me a N/A for the period when I do not hold the investment - e.g. if I bought 1 months ago, the 1 month figure would be there but the others should return a N/A.
(I am using the PGain_btw). It is giving me the same value for all period.
Other one is if I sold 2 months ago, the 1 month should return N/A but the 3 months etc should have a value. I am not getting this - I am getting a N/A for all period if I have sold the investment.

So far, I am impress with what the s/w can do - just need to understand the terminology and how to do the varios things. Bought s/w recently.
Was really happy with the graph which shows when an investment is bought, sold and dividend on the price chart. It gives a quick indication of timing impact on the investment.

Matt

[Mark]

Hi Matt,

The Custom report prompts for both the start and end date, but for these user-defined fields we just created, only the end date is used. Some fields for the Custom report use both, so that is why both are requested. If you want to change what is shown in the report's header, you can do this with "Options / Report Headers...". For example, the default Custom report header is:

[[SUBPORT]][[ - -IF]][[FILTER]][[, -IF]][[DATERANGE]][[ -IF]][[PRINTPAGE#]]

but you could change it to:

[[SUBPORT]][[ - -IF]][[FILTER]][[, -IF]][[EDATE]][[ -IF]][[PRINTPAGE#]]

if you only want the end date displayed, and not the start date like the TWR/ROI reports.

As an aside, have you looked at the new "Monthly Performance" report? In that report you can choose the Performance metric, and one of the choices is "% Gain". It has a different format than the TWR/ROI yield reports, but thought I'd mention it.

The other 2 issues you brought up are because of how %Gain is defined. It is not time weighted. See the equation here:

http://www.fundmanagersoftware.com/help ... n_oop.html

In your first case where you just bought it 1 month ago, and are calculating a 3 month figure, the beginning value is $0, but the equation still calculates.

In the case where you have sold everything as of the end date, the %gain comes out to -100% because you have no more out of pocket invested money. FM reports N/A in this case.

[mfmelb]

Mark,

I setup a special portfolio similar to a benchmark or indices setup where it comprise a number of "investments".
One investment is called Cash and I just set up 1 share at $1 on 30 June 2007. I then give it a unit price on a weekly basis. As it is a cash investment, the unit price is increasing in small amount weekly.
However, when I run the TWR report, it is giving me a negative value for the 1 mth performance. If I run the ROI report, it gives me the expected positive value when calculated using the unit prices. As there were no transactions done except for the initial entry, I would have expected that the TWR and ROI to give me the same performance value.
What is causing the TWR to give an incorrect value?

Thanks

Matt

[Mark]

Hi Matt,

I would too, given those conditions... If you don't have any transactions within a yield term ROI and TWR will be the same.

For the TWR of this investment, determine what the starting/end dates are for the 1M TWR, and then figure out what the starting and ending prices are. Were there any transactions within this period? You can also turn on TWR logging, and take a look in the log file, to get a better idea of how that is being calculated, and why the number is being reported the way it is.

If you cannot determine the problem, you can email me this investment file, along with the date range in question, and I can take a look here.

[mfmelb]

Mark,

Am having problems getting the log file to work.
I went to Options/General Preferences/Yields and tick the Log calculations for ROI and TWR boxes.
However, I can't find the log files.
It doesn't appear to have been created. According to the help description, I should get a log text file. Can't find it - did a search on my whole hard drive and still can find it.

Am I missing a setting somewhere?

Thanks

Matt

[Mark]

Hi Matt,

I suspect the confusion is that they are in a hidden folder. From the online help:

This log file will be placed in the current user's Application Data\Fund Manager folder (or AppData\Roaming\Fund Manager).

The "Application Data" and "AppData" folders are marked as "hidden" by default, so you need to turn on the display of hidden files/folders, and then browse to this folder. Your search may have not been looking in hidden folders. Once you find that folder, the log files should be in there. They are created whenever a yield calculation is performed, so you may need to refresh a report to cause them to re-calculate.