It seems each marketplace has its own way of calculating returns, none of them fully satisfying.
Maybe it's time to settle on something consistent...
So, here's our proposal:
http://marotte.net/LendingRobot_CalculatingReturns.html(Still a draft, I'll move it to LendingRobot.com once it's final)
Any comments, suggestions or thoughts ??
I think this is how BetaMax started, Emmanuel. Best wishes.
Not that I don't have beta tapes that I cherish. Just saying.
In general, if you can get returns on dollar-weighted average, you should be able to get returns on arithmetic average.
The cash flow in the image is not complete for calculating XIRR for this period. You should assume the whole account will be withdrawed at the end. So, you should add 5000 into cashflow in 4/1/2014. This cash flow assumes that net gain ($166.79) was withdrawed in every month.
I normally use a different approach. Treating the LC account as blackbox, the net cash flow would include only the following:
* Net account value at the beginning of the period
* Cash added to the LC account in the months within the period.
* Cash withdrawed from the account within the period
* The account's total net Deposit/WD in last month at the end of the period. (ignoring production from new money, and adding back withdrawals in the last month)
An example is given in the image: (Account total is col Y)
Formula for
1st line: =-(Y13+Z13+AA13) -- Except Deposit/WD, Y13, Z13, AA13 are as of END of September, 2013.
"Val of Bad..." is estimated loss from non-performing loans.
other lines: =IF(W16=$X$9,Y16+Z16+AA16+AB16, IF(W16<$X$9, AB16,"")),
where $X$9 = 47
I was reading this thinking what is wrong with the XIRR? Here is a interesting link where the guy shows problems with some XIRR results.
https://www.mhj3.com/excel.htm I doubt these type of 'wierd' results occur in most simple examples where the account balance never goes negative.
Also the paper mentions the Dietz method which gives somewhat similar results to the XIRR but not exactly the same.
There is an interesting write up and excel showing both XIRR and Dietz used on an investment portfolio and comparing results.
http://www.financialwisdomforum.org/gummy-stuff/Dietz.htm The results in this example are very similar, 8.19% for Dietz Annual Return and 8.40% for XIRR.
Thanks for pointing to these examples!