twoheads
Member of DD Central
Programming
Posts: 1,089 
Likes: 1,192
|
Post by twoheads on Dec 14, 2016 23:20:52 GMT
The spreadsheet does not distinguish between interest paid and interest accrued (owed but not paid) so the interest calculated is the total, including that which is accrued but not paid. I have uploaded an update which includes the new non 12% loans and has the date range set to calculate the interest for August. The 'Setup' tab now also includes provision for the loans which have had a rate hike. However, the main tab with the calculations does not yet take this into account and simply calculates with the current rate for the entire period of the loan part.
There have been many posts asking about the interest calculation - especially for the current month, a figure which cannot be easily determined directly from the Lendy website.
Apologies to any previous threads/posts which have addressed this and have a similar solution... I have looked for, but not found a spread sheet for this.
This is my solution (August version): Lendy_Interest.xls (131.5 KB)
Instructions: Open the file above, Lendy_Interest.xls, in Excel. Log in to Lendy, click on the MY LOANS tab and then click the EXPORT TO EXCEL link. Open the downloaded CSV in Excel. Normally this can be done by double clicking on the saved CSV file. If this doesn't work then the file must be manually opened in Excel, in which case it is easier to use Excel's File->Open to read the CSV file; if you use Excel's Data->Import external data then it is more tricky as the import wizard needs some questions to be correctly answered. Select cells A2 to J<lastRowOfYourData> in the downloaded Lendy data, copy and paste this data (Paste Special->values is best) to cell A11 of the Lendy Data sheet of the Lendy_Interest spread sheet. The formulae for the calculation are in columns K to O and are only present for 100 rows to save space in the downloaded file. These formulae need to be copied to all the rows your data requires. You can now fiddle with the start and end dates at the top of the sheet (G4 and G5) and also you can decide whether to include (or not) the extra day's interest which is donated for loan parts sold which were bought in a previous month (G6). [These extra days are included in the monthly interest but not in the displayed totals on the website for your 'live loans', 'sold loans', 'repaid loans' etc.] You can sort your loan parts by asset details and, if you like, also by loan ID. This makes things look better in the table without changing the interest calculations. New: The most accurate figure for the monthly interest is from the Int rnd column and the Rnd, Sum boxes in the same column, in particular the Total in that column. This is because, in the interest run, the interest for each loan part is calculated and rounded and then added to your balance.
If your downloaded data is complete then the spread sheet will calculate the interest for any time period you care to input. If the end date is in the past then it will reflect historical data. If your end date is in the future then the calculations will reflect the interest payments based on your current portfolio assuming no further sales or purchases prior to specified end date. [The initial dates in the download are set to calculate your interest for March 2017 based on your current investments.]
This spread sheet gives almost exactly the interest paid by Lendy.
There is second worksheet called Setup which contains the interest rates used. Any non-12% loans must be added to the list as they appear in the pipeline.
I would describe this solution as a simple solution because the exact interest calculation (that Lendy make each month) is more complex due to the way they round the various figures from loan parts. Reproduction of the exact Lendy calculation requires that the small loan parts created by partial sales of larger parts can be referenced back to their 'parent' loan parts. This cross reference information is not directly accessible from the Lendy site but it can be deduced. When you have all the cross reference information, the exact Lendy interest amounts can be precisely reproduced. If anyone is really interested in the precise details of this 'Numberwang' calculation [thanks to skippyonspeed for introducing me to the delights of numberwang] then I will write a document describing the precise - to the penny - calculation: precise at least for all my monthly interest payments so far, until they change the rules and rectify the 'extra day' bug.
Some extra information: There are two columns for the calculated interest in the spread sheet: Int calc is the unrounded interest for each loan part. The totals of these unrounded figures are then rounded and appear in the Sum,Rnd boxes. Int rnd is the rounded interest for each loan part. The totals of these rounded figures appear in the Rnd,Sum boxes.
The reason I show both is because Lendy, in the infinite wisdom (?) of their IT team, use one method when viewing loans on the web-site and the other method when doing the interest run. Here's what they do:
If you are looking at your portfolio via MY LOANS you can view four categories: live, repaid, selling and sold. At the bottom of each list is a total amount of interest. This total is calculated by adding up the UNROUNDED interest of all loan parts in the category over their entire life. The interest DOES NOT include any spurious extra days. The calculation is extremely simple and straight forward. If you use the spread sheet and download your loan parts in to it and set: Start date: 01/01/2010, End date: <today's date> and Incl extra days: No then the figures in the Sum,Rnd boxes will exactly match the total interest shown at the bottom of the various category pages of MY LOANS.
When doing the interest run, the calculation is different: Here Lendy calculate the interest on each loan part and ROUND each one. The total interest is the sum of these ROUNDED interest payments. They also add a spurious extra day of interest for loan parts sold which were bought in a previous month. Thus to calculate your monthly interest (e.g. for March) you should set: Start date: 01/03/2017, End date: 01/04/2017 and Incl extra days: Yes. Then read the figure from the Rnd,Sum boxes. The Rnd,Sum total will be within a gnat's whatsit of the total interest figure from Lendy.
Where my sheet does not exactly match with Lendy is in the calculations for partially sold loan parts. If a loan part is partially sold then a new loan part is created for the sold portion and the existing loan part is devalued. For example, you sell £40 of a £100 loan part: the original live loan part is devalued to £60 and is still live, while a new loan part worth £40 is created which is put in your sold list. In the interest run, the total interest for the group of all sub loan parts originating from partial sales of a single loan part are added before rounding the total for the group. My published spread sheet rounds all sub loan parts separately and thus the total interest calculated can vary from Lendy's figure slightly.
I have a personal, enhanced, version of the sheet which takes into account this grouping of sub loan parts. So far it has exactly matched my interest payments from Lendy every month to the penny (after their famous April 2nd 'corrections'). However, the enhanced version is too complex to publish and requires a chunk of extra information in order to establish which partial sale loan parts belong to each group.
I hope that clears things up! Many will say 'clear as mud'. I'm merely trying to explain Lendy's own calculations as best I can!
Call it 'sad' (or something stronger if you like), but for some reason I enjoy working out how stuff works. If it happens to be the details of the Lendy calculations then so be it. It's in my interest after all.
Your faithful servant,
twoheads
August version
- The 'Setup' work sheet includes new non-12% loans and also makes provision for those loans that have had a rate hike although this information is not used yet and the interest calculated is based entirely on the current rate.
- The initial date range for interest calculation has been changed to calculate for August 2017.
|
|
lobster
Member of DD Central
Posts: 636
Likes: 467
|
Post by lobster on Dec 15, 2016 8:35:16 GMT
Very useful - many thanks for sharing. I don't know much Excel, but I see in the "Age" column, it seems that the age is given in days, up to about 60 days or so, but if the number of days is greater than this, it's just rounded to the nearest month. I assume that is intentional ?? Personally I would have thought it more useful simply to display the age in days regardless. Anyway this spreadsheet is handy to say the least - especially for those who aren't too techie themselves. Nice one twoheads Edit - Ahhh , looks like "age" is copied direct from the SS input file. No problem. |
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 15, 2016 8:40:51 GMT
Very useful - many thanks for sharing. I don't know much Excel, but I see in the "Age" column, it seems that the age is given in days, up to about 60 days or so, but if the number of days is greater than this, it's just rounded to the nearest month. I assume that is intentional ?? Personally I would have thought it more useful simply to display the age in days regardless. Anyway this spreadsheet is handy to say the least - especially for those who aren't too techie themselves. Nice one twoheads That's the age (column E) as reported on the SS website... their choice about changing to '60 days' to '2 months', not mine! I do not use the data in this column.
The actual number of days interest (column K) is used for the interest calculation. This number of days is calculated from the start and end dates of the loan, also taking into account the start and end dates that are entered in cells F4 and F5.
Okay... crossed posts... I see your edit!
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 16, 2016 10:34:38 GMT
I've replaced the original download with a new one which includes loan parts for sale. Some temporary farmland came in useful for this addition.
The latest version is available in the original post.
|
|
|
|
Post by trilby on Dec 19, 2016 15:19:03 GMT
I'm not using Saving Stream any more, but while I was I wrote a Python script to do reconciliation between actual interest payments (derived from the transaction list) and expected interest (derived from the loan parts list). That includes doing the "Numberwang" task of recombining split loan parts. The Excel sheet is much more user-friendly of course but the code may be of interest: github.com/mocmocamoc/SavingStreamRec |
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 19, 2016 16:29:38 GMT
I'm not using Saving Stream any more, but while I was I wrote a Python script to do reconciliation between actual interest payments (derived from the transaction list) and expected interest (derived from the loan parts list). That includes doing the "Numberwang" task of recombining split loan parts. The Excel sheet is much more user-friendly of course but the code may be of interest: github.com/mocmocamoc/SavingStreamRecHi trilby ,
My personal version of the spreadsheet sheet includes pretty much the same calculations you describe: reconciliation of the split loans from the transaction list etc. and matching of the interest payments to what is expected from the loan parts. This is done with macros (VBA code) which are triggered to run by various manual actions.
The sheet I posted is vastly simpler to use and contains no macros while remaining accurate to within a few pence of the final SS interest figure. The SS figure varies only with accumulations of odd pennies due to where the rounding is applied during interest calculations of the split loan parts.
I posted the simplified version so that it may be more easily understood and used, with a minimum of instructions. In my experience, understanding and using a spreadsheet of someone else's design can be very tricky.
Many thanks for the pointer to your Python script. I'm sure it will help me iron out any inconsistencies in my personal effort to exactly reproduce the SS calculation.
P.S. Very nicely written code if I may say so.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 30, 2016 17:06:17 GMT
Hi @hor1997,
I'm sorry it doesn't seem to be working for you. I've just downloaded the sheet and tested with my SS account and it worked okay.
Question: are you trying this on Windows, Mac, iPad/iPhone or Android?
If you could PM me, and attach the spreadsheet with just the first few rows of your data (i.e. delete everything below, say, row 20 so there are just a few rows of your data remaining) then I should be able to see what's wrong.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 30, 2016 19:15:24 GMT
Hi @hor1997,
I too use Excel 2003 with Windows but I do not have this problem.
Two possibilities: 1. I always use 'paste special -> values' when pasting the data. To do this: Copy the values to be pasted as normal but then: right click on the top left cell of the region you are pasting into, select 'Paste Special...' from the drop down, select 'Values' and click 'OK'. 2. Otherwise, it may be that your local date format causes Excel not to recognise the date values as dates when reading the CSV file downloaded from SS.
I will do some messing about and see if I can reproduce your issue.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 31, 2016 12:01:25 GMT
Hi @hor1997,
The only way I can reproduce your problem is by changing my date settings to the U.S. style: MM/dd/yyyy.
- When opening the SavingStream CSV download in Excel, it is essential that Excel recognises the dates correctly as dates.
- The CSV contains dates in the UK style: dd/MM/yyyy e.g. 31/12/2016.
- If your Windows regional settings for the short date format have the order dd/MM/yyyy then Excel will correctly recognise all the dates in the downloaded CSV.
- If short date format is MM/dd/yyyy then Excel will recognise some of the dates in the CSV, but incorrectly with the day and month interchanged; other dates are seen as invalid and become text strings. E.g. 05/03/2016 will be incorrectly parsed as May 3rd, and 29/03/2016 will not be recognised and become a text string because 29 is not a valid month.
It would be possible to modify the spreadsheet to cope with this but would require some extra columns and some quite nasty formulae to cope with some incorrect dates and some text strings.
The simplest way to make it work is to ensure your regional short date setting is dd/MM/yyyy.
If this isn't the reason for your difficulty then I'm temporarily stumped!
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Jan 2, 2017 19:01:03 GMT
The date style is correct (and was correct from the start). The only thing I did slightly differently from your instructions was importing the .csv from LibreOffice, as my Excel does not open csv files with columns (it has been like this right from installation). Maybe that is causing the problem. In any case the +1-1 trick in the formula does force type conversion in Excel and the result is a working calculus. Not Always very close to the SS one, but a good approximation anyway. Success or failure depends how you get the CSV file into Excel.
Via another program relies on this program correctly importing the data and also exporting it in such a way that Excel can properly recognise the data... in particular the dates.
Opening the data directly into Excel circumvents all issues introduced by any intermediate program.
To get the downloaded CSV directly into Excel:
Usually, Excel registers itself to open CSV files so that if you double click on a CSV then it will automatically open in Excel.
If another program has superseded this 'CSV automatically opens in Excel' function then double clicking on a CSV will open it in the superseding program and not in Excel. In this case you must open the CSV in Excel using Excel directly:
To Open the file in Excel, then start Excel and use the File->Open menu function. In the dialogue, there is a combo at the bottom in which you can select file types. You must select Text Files, which includes *.csv and then navigate to the downloaded CSV file and open it. This should recognise the CSV format and export it correctly to multiple columns as you would expect.
If the above method doesn't work then you can use the Data->Import External Data->Import Data menu function but this is more complex. There is a wizard and you MUST select the Delimited option (radio button) in step 1 and check the Comma checkbox in step 2 in order for the data to import correctly into distinct columns. Step 3 allows the date format to be specified but if your system default date format is set up for UK then nothing is required in step 3.
One other point is that the 'Extra' column (column L) is vital to getting the correct amount of interest as certain loan parts are credited with one more day of interest than you might expect. The formulae in this column also rely on dates being correctly recognised and as such must be modified in the same way as you fixed the problem in other formulae (in column K).
|
|
|
|
Post by wottalot on Jan 2, 2017 19:47:35 GMT
This is excellent - it works perfectly for me in Excel 2007, and corresponds with December's interest to the penny ! ......  ....... Well done and many thanks. |
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Jan 9, 2017 11:03:50 GMT
SS introduced a new column to their downloaded CSV format on 9th January. This new column contains the remaining term.
Version 4 of the spreadsheet (in the original post) includes this new column for the remaining loan term so that the pasting of the data from the downloaded CSV into the interest calculation spreadsheet remains simple.
Also in version 4 I changed the interest calculation dates to calculate interest for January 2017.
|
|
|
|
Post by stevec2x on Jan 17, 2017 15:53:14 GMT
Hi guys, new to this forum
Can anybody help me make this spreadsheet work on Android?
Tia
Steve
|
|
|
|
Post by stevec2x on Jan 28, 2017 18:55:25 GMT
I guess that means no! Lol
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Jan 28, 2017 20:41:00 GMT
I guess that means no! Lol Sorry stevec2x , I saw your query a week or more ago but I have been travelling on business and, to be honest, your post slipped my mind (poor excuse, but honest at least).
I do have an Android phone but I've never tried Excel on it. I always travel with a (Windows) laptop so I run spread-sheets (and the SS site) on that instead.
paul123 already has more knowledge than I, in that he has clearly tried to work out copy/paste on android, whereas I have not, except simple stuff in text messages, e-mails and the like. And thanks paul123 for replying to the post.
stevec2x , I will have a look at how to make my spread-sheet work on Android but I am completely green in this respect.
Apologies again for not replying sooner. I will invest some time in an attempt to give you a more informed response but, don't hold your breath: the answer to your original question will likely be that I have no idea.
|
|
....... Well done and many thanks.