I had covered portfolio return computation for lendenClub in last post.
Uptill now it was really difficult calculating I2I return using XIRR method and I have to resort to NARR method using R programming.With the release of “My Account ” feature it has become very easy to calculate portfolio performance.
Step1: Calculate the net income and NPA
I2I publishes the total income after deduction of the Fees under the heading
Now calculate the total amount of Principal under delay. For this go to “EMI Status” and sum all the outstanding principal which are more than 45 days past due. ie. if last payment date is 1 nov 2018 . It means 1 Dec 2018 should be next payment date and 45 days past is 15 Feb 2019. Or in short loan delay more than 75 days past last payment date are delinquent and you need to add the outstanding principal and consider them as default.
Obviously they can become regular loans in future but we will be conservative and consider them as default
Step 2: Calulate Return
We have the income and the defaults .Difference is the net earning. To get the cashflow go to “My Account”< Escrow Account Status and download excel.
Filter “Transfer to Escrow fund” entries .The Credit associated with these dates are cash inflows you had added to your account
We have now the dates with cash inflow. Covert text numbers into numbers using “text to column” excel function. Reverse the series so that the latest date is in the end. For inverting series add a number series before the other columns and reorder it from large to small while selecting “expand selection”.
Add the Net income with negative sign as a new row with today’s date and then use XIRR to calculate the return
In XIRR you need to put date series, cash series and a guess number (put anything 10% etc). You will get your XIRR number.
This is your portfolio return based on the conservative NPA assumption.
Points to Note:
- Calculate return after you receive bulk of your EMI payments
- In I2I it takes larger portfolio size to start seeing stabilisation in ROI due to higher minimum ticket size of 5000 compared to LendenClub
- Total portfolio return is average returns based portfolio weights.
Referral Links and code
- RupeeCircle (use code PIND145 while registering to get portfolio analysis reports)
- I2I (use referral https://www.i2ifunding.com/referral/ud8cwng83/invest ,add I2I50%DISCOUNT code while registering to get 50% discount )
- Cashkumar (Mail me for referral)
- LendenClub (use code LDC11989)