FundingSecure has become one of my favourite peer-to-peer lending platforms over the past few months. At first I was put off by the complicated secondary market, but over time I realised that there are also plenty of good opportunities. I’ve been buying loan parts on both the primary and secondary markets based on each individual opportunity, but lacked any overall strategy or understanding of my overall position. So, over the weekend I spent some time getting to the bottom of my account history, it was not straight forward.

The Problem with FundingSecure’s Account History .csv

You can go to www.fundingsecure.com/myaccount/activity and download a .csv or .xlsx file with a list of your transactions to date. At first this looks quite promising:

I wrote an excel formula to convert each description in column A into a transaction type:

=IF(LEFT(A2,20)=”Investment into loan”,”Loan Purchase”,
IF(LEFT(A2,20)=”Bank Transfer Credit”,”Bank Deposit”,
IF(LEFT(A2,7)=”Sold £”,”SM Sale”,
IF(LEFT(A2,8)=”Cashback”,”Cashback”,
IF(LEFT(A2,9)=”Purchased”,”SM Buy”,
IF(LEFT(A2,14)=”Capital Return”,”EOT Capital”,
IF(LEFT(A2,15)=”Interest Earned”,”EOT Interest”,””)))))))

This uses a nested if statement to look for each possible option and map each transaction to a transaction type. When writing an excel formula you can add a new line by pressing ‘ALT’ + ‘ENTER’ to make the formula easier to read. Using these categories I was able to create running totals for total lending, net deposits and my own calculation to check the balance. What troubled me was that my total balance came to around £400, but when I look at ‘my available funds’ on the website it was £0. Did £400 of my money disappear!?

I realised that the difference was due to ‘Investments Awaiting Activation’. None of these numbers are included in the csv download until they are activated, even though my money has been assigned to the loan and it is accruing interest. One of my investments awaiting activation has been sitting there for almost 3 months, so this difference can become substantial over time and makes the csv download useless.

The second problem is that this download has no detail on the loans themselves: their end date, rate of return or accrued interest.

My Workaround

The only way I found to build a complete picture of my holdings was to go to www.fundingsecure.com/myaccount/current-investments and copy and paste the whole page into excel. In this there are two tables: ‘current active investments’ and ‘awaiting activation’. These two tables handle accrued interest differently and have a different layout so you need to treat them slightly differently.

Current Active Investments

Once you copy and paste it into a new tab, the ‘Current Active Investments’ table looks like this:

The interest to date is the accrued interest. Since loans that get paid pack in the first 30 days have the full month’s interest paid regardless, here a loan that is just 1 day old would have the full 30 days interest given in the interest to date. I don’t like this as I think it artificially inflates your returns, so I added this formula for adjusted interest:

Adjusted Interest =ROUND(IF(F2<31,H2/30*F2,H2),2)

It just says if it is 30 days old or less, then only give the pro-rata interest for the number of days active. So, a 1 day old loan only would have 1 day of accrued interest rather than 30 days. I also created a ‘days remaining’ calculation as not all loans are 183 days, this uses the ‘date ending’ column G2, the NOW function (your computer’s current date) and a rounding function:

Days Remaining =ROUND(G2-NOW()+1,0)

Finally I added a Loan Category Column, this was a semi manual categorisation of all loans into either property or pawn. You could speed up this labelling by filtering on the Title column (click on cell B1, then ‘Data’, ‘Filter’, then the filter icon), then searching for obvious keywords like ‘property’, ‘development’ or ‘land’ and dragging down the description.

If you wanted to be really professional and create a reusable mapping you could create another tab with 2 columns: ‘title’ and ‘loan category’ with a list of distinct loan titles and your categories, with a vlookup or INDEX/MATCH from your main table to this.

Awaiting Activation

The awaiting activation table has slightly differently organised columns and calculates accrued interest on the number of days you’ve held it (not using the 30 day rule as the other table). There is no ‘date ending column’ so I chose to just manually give each loan 200 days remaining as a rough estimate. Once you do these changes you can paste onto the end of the first table.

Analysing the Data

If you work with excel regularly you are well aware of Pivot Tables and can skip this last section, but for those who may not use Excel that often they are a great way to aggregate and drill into data:

After I had the data organised as described in the previous paragraphs into one single table, I selected the top left hand cell of the table. I held down SHIFT + CTRL then pressed the right arrow and down arrow on the keyboard after one another to select all the data in the table. With all the data selected, I then selected ‘Insert’ and ‘PivotTable’ from the excel toolbar and clicked ok. Excel then creates a pivot table in a new tab. By dragging and dropping different fields onto the columns, rows and values field boxes on the right hand side you can break down your FundingSecure investments now by their category, rate of return and expected end dates: