Home → Investments →

Investment Performance Report

• Edit

mhoggie

16 Feb, 2024 12:04 AM

Can MD support provide me the formula that is used to determine the Return % and the Annual % (ROI) on the Investment Performance Report. I'm not able to obtain those numbers in a spreadsheet using a typical ROI and Annual ROI Percentages
Thanks, Mike

1. 1 Posted by Stuart Beesley ... on 16 Feb, 2024 03:17 PM

   

   In simple terms:

   - get all txns for the selected investment/security account, and process according to the selected date range

   - as you process, build a timeSeries list of cashflows… for each txn add the cashflow into a list on the txn date as:
   - if Div, DivXfr, MiscInc: -div amount + fee
   - if MiscExp: -expense + fee
   - else: -securityAmount + fee (adjusted for splits as of the date of the txn)

   Then:
   - returnAmt = (endBalance + results.sales + results.income) - (startBalance + results.buys);
   - divisor = startBalance + results.buys
   - Return% = returnAmt / divisor

   NOW… Compute the Return On Investment, or Internal Rate of Return, or Annual Return Percent:
   - uses the Secant method to iteratively find the percent to 0.00001 accuracy,
   - The equation for secant iterative rate computation is:
   - Rn+1 = Rn - NPVn*( (Rn - Rn-1) / (NPVn - NPVn-1)
   - Where R is the rate and NPV is the net present value

   In code terms..:
   recalculate the start date to ensure it aligns to a point where the account existed
   Insert the security’s balance as at the adjusted start of the date range (less 1 day) to the start of cashflows timeSeries
   Insert the security’s balance as at the date range end date to the end of cashflows timeSeries
   Now iterate the cashflows timeSeries to calculate ROI using the Secant method….

   So, the Return and Return% is easy to check/work out.. I won’t pretend that I understand the ROI calc or know explicitly how to check it….

   Does this help?

   (I am not IK support!)

   • Edit

2. 2 Posted by Stuart Beesley ... on 16 Feb, 2024 07:48 PM

   

   Don’t ask me to explain!! 😝

   https://en.wikipedia.org/wiki/Secant_method

   • Edit

3. 3 Posted by mhoggie on 16 Feb, 2024 08:12 PM

   

   Thanks Stuart,

   I think I'm going to experiment with a test database and investment to test that report to see if it follows typical ROI calculations. I tried to take a spreadsheet and reproduce the performance report calculations without success. With a test database and investment account I should be able to set up the history and transaction amounts to a specific amount that would help me determine what the performance report is doing. I'm already determined that the ROI% on the report is about 3% higher than actual. But the annualized is way out of wack, so I'm curious how they are getting that result. I have one security that has lost 8% of it's value but the ROI on the report shows 5% loss. The annualized ROI is really strange, it shows 139% gain. I've looked at all the transactions and historic prices and I have no clue how the MD report comes up with that amount of gain when I've actually lost 8% of it's value. Very weird.

   • Edit

4. 4 Posted by Stuart Beesley ... on 16 Feb, 2024 08:26 PM

   

   Well. If you setup a simple dataset with not too many txns then I could probably get it to spit out the values per txn that make up the totals.

   • Edit

5. 5 Posted by dwg on 17 Feb, 2024 01:40 PM

   

   @Stuart

   The main issue I have with the report as it is, is the Annual % column. You can easily get some nonsensical data, especially if you have set a limited time frame for the report. Say for example you are looking at a week, just for interest sake, it looks like the report takes this period and tries to annualize it. Now a steep change in the price of a security that week could show as a dramatic change once annualized. It can also be the seen if a dividend was paid that week, typically these are paid each 6 months, so calculation of an annual figure based on one week with such a payment is totally misleading at best, especially if it is a substantial payment.

   As I said in the original thread I think having extended performance data in a report is more useful than just a ROI report.

   • Edit

6. System closed this discussion on 18 May, 2024 01:40 PM.

7. Stuart Beesley (Mr Toolbox) re-opened this discussion on 18 Sep, 2024 08:46 AM

8. 6 Posted by Stuart Beesley ... on 18 Sep, 2024 08:46 AM

   

   FYI - really the ROI% an IRR calculation.

   It can certainly go wrong; I’ve already identified one scenario.

   Interestingly it also sometimes seems ‘right’. You can model the result using the excel XIRR() function and provide a table of cash flows.

   … digging…

   • Edit