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.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 15, 2016 17:04:19 GMT
Perhaps we need a new thread: poet's corner or similar.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 15, 2016 16:28:42 GMT
Observations based on their recent updates:
SS seem a little hazy on the definition of a week. (A fact pointed out by many.)
Even when they write '7 days' one is given to wonder if they can count that high. Long after I've reached the magical seven, SS still hasn't caught up.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 15, 2016 16:18:31 GMT
A festive limerick:
There once was a site called Ess Ess Whose rates never failed to impress But now they are falling It's all quite appalling And could end them up in a mess
|
|
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 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.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Lendy (L) in Administration
New reCaptcha
Dec 14, 2016 19:27:14 GMT
Post by twoheads on Dec 14, 2016 19:27:14 GMT
I notice that sushi is now making a regular appearance.
Not as easy as palm trees and store fronts but I'm hungry learning.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 13, 2016 16:14:19 GMT
Hi guys, I'm looking to put £150k on the secondary market at approx 3.15pm today Thanks for the heads up. I snapped up a couple of nice chunks to help me along with diversification.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 13, 2016 14:40:28 GMT
I am sure like me everyone reads all of the threads but just in case the above was a post from SS on this thread. Correction to thread link.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 10, 2016 12:02:43 GMT
Can everyone start screen capturing updates/information and upload them here if they are changed please? If each one of us does this for the loans they are mostly involved with, we should cover all of them. See this post from ilmoro. I think it's kept pretty well up to date.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 9, 2016 8:51:36 GMT
Other possibilities might include... - 2016 was a leap year. Do we earn 366/365 of the quoted rate this year?
- An occasional negative balance, during which time we earn interest on parts we haven't paid for yet.
- Edited to add: Parts sold on the first day of a month (the subject of this thread)
It's definitely based on 365 day year at present (at least since I started investing in September this year). If I change my spreadsheet to use 366 days then my figures do not match those of SS.
It would also not be unusual if the leap year day was inserted into the tax year Apr-2015 to Mar-2016. Of course, my investments in to SS don't go back far enough to check this.
SS sometimes round the sum of unrounded figures: e.g. the sums of your total interest at the bottom of your live, selling, sold and repaid loan parts. Of course, when you export to spreadsheet, each loan part interest is rounded individually meaning that the sum in your exported tables is not the same as that shown in the sums online in the website. In both of the cases above, the rounding always matches what I calculate.
They have another trick during the interest run: sometimes the SS system groups together your small loan parts (for one property) and pays all the interest for the group on a single, chosen, loan part, paying no interest on the others in the group. This allows them to reduce the accumulated rounding errors. I haven't worked out their system for doing this! Edit: the loan parts with no interest paid seem to be those created when some, but not all, of a loan part is sold. The 'partial' sale causes a new loan part to be created: the sold part, while the live part remains but with its value reduced. However, the interest due for the sold portion appears to be added into the live part during the interest calculation run, the sold part receiving no interest.
This can be verified by matching the interest payments downloaded from 'my account' against the loan parts for which they apply... easily done with a little spreadsheet magic.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 8, 2016 22:20:39 GMT
The figure I stated of 12.168% is the rate SS have actually paid (on my lending up to Nov-30) and is directly comparable to the quoted 12%. (I have only invested in 12% loans).
Imagine my investments should have paid exactly £120.00 in interest when each loan part is allocated the correct number of days (without any extra days).
With the extra odd days of interest that SS have donated on various loan parts, I have actually received £121.68.
This is the basis of my quoted figure.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 7, 2016 21:11:01 GMT
I haven't looked at mine but accept what you all say probably applies. I forecast a frenzy of buying bits of tat on the SM on Hogmanay just to see. We wish you... and a happy new year!
Perhaps I should sing 'prosperous'?
Well, it doesn't quite scan but you can just about get away with it.
Let's hope for prosperous. That would be good.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 7, 2016 16:47:45 GMT
If you put a loan part up for sale on the 1st August, are you not entitled to zero days interest as you haven't held past midnight? No because in general interest payments are upto the last day of the previous month. So parts sold on the 1st should post 1 day of interest; just as parts sold on the 2nd post (correctly) 2 days of interest etc. (I cross check every single interest payment on SS for accurcay as the previous software incarnation was riddled with errors) locutus is correct. You do not receive interest credited for the day on which you sell any loan part.
You receive interest for any loan part held as the clock ticks midnight and that interest is credited to the day that's just ended, not the day that's just starting.
A loan part bought on Nov-30 and sold on Dec-01 shows one day's interest in November (for Nov-30) and should show no December interest. In total, you should receive one day's interest.
There is currently a bug which means that such loan parts also receive an extra day's interest payment for Dec-01. I've received many extra days worth of interest due to this issue.
It could be that this bug is a result of SS 'fixing' the issues raised at the top of this thread.
I only started investing in SS in late September so I don't have figures from before then to test any hypotheses.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Dec 7, 2016 16:26:00 GMT
I think it might depend on whether you sell the part on the first of the month before the interest run has happened, or on the first of the month after the interest run has happened. The former probably doesn't pay the bonus day of interest in the subsequent interest run while the latter probably does. I don't think it matters because you get the interest for the extra day in the following month's run:
1. I buy PBLxxx on Oct-31. 2. I carry it into Nov-01. 3. The October interest run happens early on Nov-01 and I get 1 day's interest in the October run for holding the loan part at midnight at the end of Oct-31. This is normal. 4. I sell the loan part on Nov-01. 5. In the November interest run (early on Dec-01), I receive one day's extra interest which I shouldn't.
If I sell on Nov-01, I should get no interest in November: having bought on Oct-31, I've held the loan part for one day and already received that day's interest (assigned to Oct-31) in the October run. If I sell on Nov-02, I should get one day's interest in November. This will be the interest for Nov-01. I wrongly receive 2 day's interest. If I sell on Nov-03, I should get two day's interest in November. This will be the interest for Nov-01 and 02. I wrongly receive 3 day's interest.
...etc... If I sell on Nov-30, I should get 29 day's interest in November. This will be the interest for Nov-01 up to and including Nov-29. I wrongly receive 30 day's interest. If I sell on Dec-01, I should get 30 day's interest in November... and I do. (I should get none for Dec-01... but at the end of December I will get interest for Dec-01).
The daily interest is, I think, paid at the end of the day on any loan part held as the clock reaches midnight. The interest is assigned to the day that's just ended. You don't get a day's interest for the day on which you sell a loan part.
The one example I don't actually have is a one day loan which spans a month end. I have various two and three day loans that span a month end and also some which span two month ends. All these have had interest payments exactly as described above: the loan part interest amounts match precisely.
For every loan part you sell which was bought in a previous month, you receive an extra day's interest.
Next time a loan part comes up on the last day of the month I will buy it (if its big enough to show a one day interest payment) and then sell it the next day. I won't know the answer to this test until at least Feb 1st 2017. However, I expect a single day's interest in each of the two months involved.
Of course, SS may fix this sometime soon. My spreadsheet will tell me if and when that happens.
|
|