Price-Return Index

[pstead]

Hi Mark,

I'd like to create a price-return index for a sub-portfolio of equities. I have made a working sample using a custom report and manually entering data into a spreadsheet, but with several years of daily data I wonder if there is an automated way.

As you will recall, a price-return index strips out the effects of cash flows by the formula:

index beginning value of a day = index ending value of the day before plus purchases minus sales on a day before

Adapting this to the syntax of FundManager, I make a custom field: value_change-EVAL(ENDDAYS(),ENDDAYS(),Contributions_btw+Withdrawals_btw)

The speadsheet has three columns for each day:

Column A - End_value of subportfolio (imported from FundManager); Column B - custom field (above); Column C- calculation of index number with the formula A1 + (A1*(B2/A2)) where row 2 is the day of the report and row 1 is the day before.

I then import the values of column C as prices of a benchmark investment. Now this technique creates a correct index, but it's slow because a report can only calculate six days and the values of cell B (custom field) must then be entered into the spreadsheet a week at a time. I'm wondering whether there is some way to export a user-defined field for all dates of a sub-portfolio, the way that prices can be, or in some other way to circumvent the six-day limit to reports.

Thanks very much in advance for your help, and sorry for the verbal descriptions. I can upload my excel file and custom report is that helps.

I read with interest your suggestion to williamg85 on March 7, 2022, but the solution involved sixty reporting periods, whereas I have daily returns for years.

[pstead]

Sorry, incorrect spreadsheet formula. It should be =C1+(C1*(B2/A2))

[Mark]

Hi pstead,

Have you considered just using TWR for the sub-portfolio? TWR is doing the same thing as what you're describing I believe. It factors out external contributions/withdrawals. See the documentation here. You can also turn on logging of TWR calculations, if you'd like to compare the low level details to your spreadsheet.

To answer your question directly though, no, there isn't an automated way to export a user-defined custom report field for all dates of a sub-portfolio. If you still want to do this, you could make many user-defined fields, so you can do a bunch at once, something like:

Field 0 = value_change-EVAL(ENDDAYS(),ENDDAYS(),Contributions_btw+Withdrawals_btw)
Field -1 = value_change-EVAL(ENDDAYS()-1,ENDDAYS()-1,Contributions_btw+Withdrawals_btw)
Field -2 = value_change-EVAL(ENDDAYS()-2,ENDDAYS()-2,Contributions_btw+Withdrawals_btw)
etc...