Calculating Yield with XIRR

This is a guest post by German investor Martin R..

P2P Loan Yield

On most p2p platforms (all of mine except Ablrate and Estateguru) principal is paid back monthly during the loan term. The remaining principal decreases every month, the interests do so
accordingly. Inexperienced people are frequently confused by that – a loan over 100 EUR, a term of 5 years and an interest rate of 10% doesn’t yield a profit of 50 EUR, but roughly half of that.
When you think about it for a moment the reason is evident: On average, the capital was only lend for 2.5 years, a part of the debt was already paid back with the first instalment. In exchange, the instalment – as sum of interests and payback – stays the same for the whole running time – minor deviations can occur because of dues of the platform.

Which leads us to a good approximate formula: The obtained interest is about half as high as they would be for a fixed deposit with the same conditions. As already mentioned, the stated yield is still right, though. There are many websites to calculate instalments on the internet you can use to play that through.

Admittedly, such calculations made beforehand become useless if losses or early paybacks occur. And actually, they always occur. How is it possible to stay informed about the current yield in that case?

Mostly, the provider offers calculated ROI calues in the account overview. The shown figures are rarely particularly meaningful, though. Auxmoney for example displays values which
noticeably exceed the interest rate of the lent money – of course that is impossible. There are bookings being conducted wrongly and early paybacks are taken into account as earnings –
that has been happening systematically for years and was never addressed or fixed.

Two ways of calculating yield

In principal, you have to distinguish between already obtained yields ( this is the figure shown by most providers) and the total yield expected at the end of the running time.

The first figure is a good review of the past, but could only be realised if you sold all
your remaining loan parts for their remaining nominal value. Usually, no losses are being considered, not even the already failed repayments. This means the calculated yield is generally too optimistic.
A yield (XIRR, RTI) shown by Bondora or Omaraha of 25% or even more may not be technically wrong, but is not the whole truth either.

Of course, the expected total yield is currently not definite. After all, both future losses and payments due to defaults can significantly affect the yield, meaning the values can only be estimated.
Many refer to a worst-case-scenario when they fully depreciate all credits in defaults and depreciate 50% of all credits that are overdue. But not even that is the whole truth, because usually some of the loans that are current now will fail as well.

The XIRR-function

Thus, you won´t be able to avoid doing your own calculations. Admittedly, it is not possible to do those manually or with help from a calculator for a single loan part with irregular paybacks, let alone a large number of credits. Continue reading

P2P Lending Site Isepankur Improves Loan Quality

Estonian p2p lending service isePankur successfully improved the loan quality in 2010. The company added several validation steps for loan applications. On top of the existing credit bureau checks and it’s own scoring model isePankur introduced several manual checks on the borrowers in May and July. Since September borrowers need to submit bank statements which isePankur uses to verify information presented in the loan application. The measures implemented are listed in detail here.

CEO Pärtel Tomberg told “…  these [new] loans will … be the most profitable social banking loans for investors across the globe”.

To prove this claim Isepankur is publishing real time performance data on its marketplace.

(See Isepankur site for larger size interactive charts)

Unlike other companies you can see the quality of loans per month so the growth rate of the portfolio will not hide the actual returns from loans issued previously.‘ says Pärtel Tomberg.

The charts reflect the drastic reduction of bad debt from loans issued after May 2010 and the very positive impact on lender returns. Continue reading raises fees

In Germany p2p lending service will increase its fees in February. Smava management said the fee increase is necessary to build a sustainable business and points out that now 20 months after the launch the value proposition of Smava has been verified. Median ROI is given as 9.4% with 90 percent of lenders (best and worst 5% cut off) earning between 4.7% and 12.9% ROI per year.

Old fee structure:

Lenders: no fees
Borrowers: 1% of the loan amount

New fee structure:

Lenders: 4 Euro (approx 5.30 US$) for each successful bid

  • 2% of the loan amount, minimum 40 Euro, for 36 months loans
  • 2.5% of the loan amount, minimum 60 Euro, for 60 months loans

This increase will more than triple Smava’s revenues from 8,000 Euro per month to 27,700 Euro per month (estimate based on 800,000 Euro loan volume per month; 5,500 Euro average loan amount; average bid amount 330 Euro; 50% of loans for each loan duration).

While the fee height is tolerable for most lenders, I am somewhat sceptical whether Smava is attractive enough for borrowers under the new fee schedule and will be able to continue its required growth.

7.9% ROI on my MyC4 loans in the past year

MyC4About a year ago I started lending money to African entrepreneurs via  So far it did meet my expectations. The process of uploading money worked without problems, there were always enough loan applications to select from and the offered interest rates were high enough to allow for good returns despite the risks. The only point, where the usability need to be vastly improved is a better status overview for the payment status of all loans in the portfolio. MyC4 does show the payment status in detail for each loan, but its hard to get an aggregate overview.

I invested at 13.8% average nominal interest rate. 194 loans are currently running, 29 are repaid in full, 12 are open/pending (not yet disbursed), 1 defaulted and 4 were cancelled.

To roughly calculate estimate my ROI I looked at my account display at MyC4


About 12-13 months ago I uploaded 1,506.94 Euro. Now my account value is 1,625.24 Euro (9,09 Euro available + 124,62 Euro pending bids + 1.491,93 Euro Outstanding principal). That results in an ROI of 7.9% so far. Naturally it would drop, if the outstanding principal is not repaid in full due to defaults.

The ROI is much lower then the average interest rate, since it does take weeks before an investment in a loan becomes active – and unlend money does not yield interest. A further point is that several late loans affect the ROI.

Read all MyC4 posts from the past months.

New academic study estimates average Prosper ROI at 6%

The new study "Dynamic Learning and Selection: The Early Years of" by Seth Freedman and Ginger Zhe Jin, both at the Department of Economics, University of Maryland analyses Prosper data in a time frame from April 19th 2006 to December 31st 2007.

The study analyses the development of the marketplace and how lenders refined their strategies as a result to own experiences and changed settings.

They write:

Overall, we conclude that Prosper is evolving from a comprehensive market to a market that primarily serves the borrowers who have access to traditional credit. This implies that Prosper will compete head-to-head with the traditional banks rather than pick up a missing market. Assuming away any cost in information processing, we estimate that the average rate of return of a Prosper loan is 6% if Prosper loans continue to perform according to what we have predicted from their existing performance. From the lenders point of view, this number compares favorably to 6-month certificate of deposit and 3-year Treasury bill, but less favorably to the rate of return implied by the S&P 500 in the same time period.

Other findings are that high interest loans yield lower returns due to high default rates and that the probability for defaults of Prosper loans peak at month 10 and the edge down.

The main uses of Prosper loans are:

33% of all previous Prosper listings have mentioned credit card consolidation, which is higher than the mention of business (23%), mortgage (15%), education (22%), and family purposes (20%) such as weddings.  

Cited from the conclusion chapter of the study:

The first two years of Prosper has enlivened the concept of P2P lending, but the road towards success is full of challenge. While it is tempting to expect P2P lending to alleviate credit rationing for near- or sub-prime risks, we find Prosper evolving from a comprehensive market toward a market that primarily serves borrowers who have access to traditional credit. This implies that Prosper will compete head-to-head with the traditional banks, rather than pick up a missing market. This pattern is not unique to Prosper. …

How can Prosper compete with traditional banks? Our study suggests that the microfinance approach, as implemented through Prosper groups, has failed to select good risks or enhance loan performance. But on the up side, lenders are learning fast about the pitfalls of P2P lending thanks to the transparency of Prosper. Our calculation suggests that, if the loans continue to perform as what we have predicted from the market performance, Prosper loans could yield an average return of 6%.

See related post on the Prosper blog.