After spending many hours trying to calculate returns correctly using Excel, from different Excel formulas — XIRR etc.— to many formulas given to me by expert mathematicians, I concluded that Excel alone, as wonderful as it is, will not get the job done.

I tried a wide range of software, from free to very expensive, and found a wide range of answers for the same case; some close, most not even close.

I found that most of these programs did not take all of the performance calculation variables that affect the correct calculation of 'r' into consideration; the very few that did, did not do the calculations correctly:

The following is intended to help you understand the importance of applying correct and compliant performance calculation methodologies/algorithms when calculating and reporting returns; none of which take secondary variables into consideration and all of which must be programmed separately.

Arithmetic Average Vs. Geometric Average

Calculation of the arithmetic average of the S&P 500 Index Real Return indicates that the value of the portfolio will be $118,748.00 in 2005 (not shown).

Calculation of the Geometric Average return indicates that the value of the portfolio will be $95,421.00 in 2005.

$95,421.00 is the correct calculation of the future value of this S&P Index Portfolio.

Using arithmetic average will always overstate returns.

For this reason it is an unacceptable methodology for calculating R.

Know that the Geometric Average will always be less than the Arithmetic Average.

Sidebar

R calculation variances also illustrate the importance of being on the optimum point or ahead of the R calculation curve. As methodologies have improved, so has the quality of the calculation of R.

Investment Advisors and Stockbrokers need to know what R calculation methodology is being used by others when comparing Rs. Different Rs may not be a function of actual performance but in the R calculation methodology used.

A Very Simple Case

let's take a very simple case and use different performance calculation methodologies/algorithms to demonstrate the importance of calculating 'r' correctly:

Keep in mind that the more primary and secondary performance calculation variables used, the greater the distortion of performance calculations will be unless the correct methodology/ algorithm is selected and applied and unless the proper accounting for performance calculation variables is programmed in performance calculation software.

Simple R

Incorrect calculation methodology because you cannot properly account for cash flows; the $ amount and when they occur.

Original Midpoint Dietz Method

The Original Midpoint Dietz Method approximates when cash flows are received by assuming that all cash flows (CF) occur at the midpoint of the period and half-weights the total flows for the period:

Incorrect calculation methodology because algorithm does not calculate R based on actual date of cash flows and because there is no/incorrect accounting secondary performance calculation variables; Advisor Fees Taken (paid from the account), Advisor Fees Paid (paid directly from the client to the advisor; not through the account), and Accruals; Dividends, Interest, Income, and Fees.

Modified Dietz

The Modified Dietz Method improves upon the Original Dietz Method by assuming a constant rate of return on the portfolio during the period, thereby eliminating the need to know the value of the portfolio on the date of each cash flow. In an attempt to determine a more accurate return than the Original Dietz Method, the Modified Dietz Method weights each cash flow by the amount of time is actually held in the portfolio.

The chief advantage of the Modified Dietz Method is that it does not require portfolio valuation on the date of each cash flow. The estimate surfers most when a combination of the following conditions exist: (1) one or more cash flows occur, (2) cash flows occur during periods of high market volatility.

Correct calculation methodology because cash flows are accounted for when they actually occur; the weakness being, as with all of the methodologies/algorithms above, that secondary performance calculation variables — Advisor Fees Taken , Advisor Fees Paid, and Accruals; Dividends, Interest, Income, and Fees — cannot be properly accounted for within the methodology/algorithm itself.

PerfCalc programming does properly account for Advisor Fees Taken , Advisor Fees Paid, and Accruals; Dividends, Interest, Income, and Fees.

Periodic and Daily Valuation Method Geometric Linking and GIPS

Modified Dietz returns aren't as precise when you have large cash flows in a portfolio.

The estimate suffers most when a combination of the following conditions exists:

GIPS standard beginning January 1, 2010 require the use of calculation methods that use actual valuation at the time of large external cash flows.

The Periodic and Daily Valuation geometric linking methodologies break the total performance period into sub-periods:

The sub-period returns are then geometrically linked according to the following formula.

The chief advantage of this method is that it calculates the true time-weighted rate of return rather than an estimate.

The problem is that this algorithm does not take into consideration when small cash flows occur.

None in this case.

Performance = 8.35%.

Correct calculation methodology because cash flows are better accounted for by entering a Market Value whenever a large cash flow occurs; the weakness continues to be that secondary performance calculation variables — Advisor Fees Taken , Advisor Fees Paid, and Accruals; Dividends, Interest, Income, and Fees — cannot be properly accounted for.

Furthermore, this methodology/algorithm does not calculate small cash flows within sub-period returns

PerfCalc/Modified Dietz/Periodic and Daily Valuation Geometric Linking and GIPS

Takes into consideration when small cash flows occur.

None in this case.

Using the same 'Simple Case' data in PerfCalc = 8.35%.

Correct calculation methodology because cash flows are better accounted for by entering a Market Value whenever a large cash flow occurs, because small sub-period small cash flows are calculated, and because secondary performance calculation variables — Advisor Fees Taken , Advisor Fees Paid, and Accruals; Dividends, Interest, Income, and Fees — are properly accounted for.