personal rate of return

[Guest 401k guru]

If anybody has the actual formula for determing Personal Rate of Return, please post it as a reply to this message. I am familiar with many approximations (Modified-Dietz, etc); however, I want an exact formula which fully takes into account the timing and value of participant's contributions/disbursements. Any information which you can provide may be helpful.

thank you, Mark

[rcline46]

Best way is daily balance. TO do this multiply each balance by the days left in the period. IE for a quarterly val - opening balance times 90 (or 91 or whatever) plus first deposit in period by days left plus....

This gives denominator. Numerator is total earnings over the period. Result times 365 gives rate.

[MGB]

The above is an approximation only. All methods are only approximations (unless it is a simple solvable equation such as one deposit).

The only true method is solving for a very complicated equation. It gets complicated if there are more than two deposits/withdrawals (one deposit is straightforward, two deposits produces a quadratic, threee produces a third-order equation, etc.). Once you get to these higher orders, it is nearly impossible to solve (I taught university courses in numerical analysis that did this through various approximation techniques).

With multiple deposits, an iterative solving program is needed. Excel's "solve" does this very easily.

Use x© as each contribution or withdrawal:

Example: x(1) = first contribution; x(2) = first contribution or withdrawal (where withdrawals are entered as a negative), etc. If you only want to do it during a time period instead of since the inception of the fund, x(1) = beginning balance.

The equation is:

(sum over c of (x©*(1+i)^(time in days from each c to end of period)) = final balance

If you set this up with i in a cell with a seed value (i is daily rate of return), then use Excel's "solve" routine, it will return the value to 16 decimal places very rapidly (not an exact solution, but as close as a spreadsheet can get due to size of storage for any particular number; and is a LOT more accurate than other simplified methods).

The answer is a daily rate. Using (1+i)^365 will give you the final annual rate.