|
|
Post by stevec2x on Jan 28, 2017 22:17:21 GMT
Thanks guys, don't worry about it, I just thought that somebody might have an easy answer. Yeah, the CSV files will load into pretty much any spreadsheet app, but it's the formulae that are the problem I think. Might have a go at doing it myself now that I understand SS much better.
|
|
SteveT
Member of DD Central
Posts: 6,875 
Likes: 7,924
|
Post by SteveT on Jan 30, 2017 13:17:12 GMT
(re-stickying)
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Feb 8, 2017 21:01:32 GMT
February version uploaded in OP.
New non-12% loans added.
Initial date range modified (unsurprisingly) to calculate the month's interest for February 2017.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Mar 22, 2017 10:00:51 GMT
The version for the March 2017 interest (and subsequent) requires some rework in view of the recent changes from SS.
The current version will still calculate the correct amount of interest but makes no distinction between interest that will be paid and interest that will be accrued for later payment (if you're lucky).
I will publish a new version in the coming days.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Mar 31, 2017 18:24:03 GMT
As you see from my previous post, I had hoped to modify the spreadsheet to distinguish between interest paid and interest accrued (owed but not paid).
Alas, I have not had time to do the rework... it's still in my 'to do' list.
I have uploaded an update which includes all of the new non 12% loans and has the date range set to calculate the interest for March. However, the interest calculated is the total, including that which is accrued but not paid.
|
|
vmail
Open image in a new tab.
Posts: 457
Likes: 217
|
Post by vmail on Apr 1, 2017 19:57:17 GMT
Would the next version correct the interest issue, or maybe I am doing something wrong. I only have 12% loans and none of them have defaulted. The are under by 5.94 (Sum, Rnd) and 5.32 (Rnd, Sum)
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Apr 3, 2017 11:37:33 GMT
Would the next version correct the interest issue, or maybe I am doing something wrong. I only have 12% loans and none of them have defaulted. The are under by 5.94 (Sum, Rnd) and 5.32 (Rnd, Sum) I presume that by 'they are under by 5.94' you mean that the spreadsheet calculates less interest than you received.
The date range should be set to: Start date: 01/03/2017 and End date: 01/04/2017 (this means it covers the 31 days of March). You should select Incl extra days: Yes, because you get an extra day of interest in the run for loan parts sold during the month which were purchased in a previous month.
If the spreadsheet is still calculating below what you received then it is most probably because the formulae have not been copied to all your data rows. (The formulae are only in the first 100 data rows to save space in the download.)
If you have any loans which are IA (interest accruing) or DEF (defaulted) then the spreadsheet will overestimate your interest because, currently, it does not take into account that those loans will not have interest paid. That is the correction I want to add when I have time.
|
|
|
|
Post by chielamangus on Apr 4, 2017 10:52:14 GMT
The main value of the calculator, it seems to me, is in checking whether SS have got the calculation correct. So the real question is - has anybody ever found a discrepancy between their calculated figure and the SS one (apart from a few pennies that are rounding errors)? Personally, I don't have the time to check on all interest on all loans across all platforms, so this info is of interest to me (yes, the pun is incidental). I limit myself to broad assessments and only go into detail if something looks wrong.
|
|
|
|
Post by chielamangus on Apr 10, 2017 10:29:09 GMT
So ... has anybody ever found a discrepancy between their calculated figure and the SS one (apart from a few pennies that are rounding errors)? And what size was it in percentage terms?
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Apr 10, 2017 12:56:37 GMT
So ... has anybody ever found a discrepancy between their calculated figure and the SS one (apart from a few pennies that are rounding errors)? And what size was it in percentage terms? You mean: apart from the famous April 1st shenanigans!
The rounding errors should be very small. Here is some detail if you're interested:
There are two columns for the calculated interest in my 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 'MY LOANS' you can view the separate 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 my 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 is wrong 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. In the interest run, the total interest for the group of all sub-parts which stem from one original part are added before rounding the total for the group. My published spread sheet rounds all sub-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-parts. So far it has exactly matched my interest payments from Lendy (to the penny, after any April 2nd 'corrections'). However, the enhanced version is too complex to publish and requires a chunk of extra information in order to establish which loan parts belong to each group.
Hope that clears things up!
I'm sure some will say 'clear as mud'. I'm merely trying to explain Lendy's own calculations as best I can!
EDIT - Much of the above information has now been added (slightly modified) to the OP.
|
|
|
|
Post by chielamangus on Apr 11, 2017 11:52:11 GMT
Thanks, twoheads. People like me depend upon people like you to do the random checks, and it is reassuring that the SS calculation is OK. |
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Apr 30, 2017 7:55:41 GMT
April version uploaded to OP.
Added the latest non-12% loans to the list. Date range set up for calculating the April interest.
|
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on May 30, 2017 19:45:44 GMT
May version uploaded to OP.
Added the latest non-12% loans to the list.
Date range set up for calculating the May interest.
And no... it still doesn't exclude interest from IA and DEF loans - it calculates the interest you are due, not necessarily what you will receive if you hold IA or DEF loans.
And yes... it's still called SS_Interest.xls... I'm living in the 'unrebranded' past.
|
|
|
|
Post by Cashback Chaser on Jun 7, 2017 13:54:43 GMT
Can you mod this spreadsheet to work with Collateral? I have tried but the problem is that the start and end date of the loans are on separate lines. Sample data | Date | Transaction | Loan Number | Fund ID | Loan
| Start Date | End Date
| Amount | Balance | | 20/05/2017 | Loan Part Sales Purchase | COLBB00519 | 8***2 | 18ct White Gold Diamond Ring | 20/05/2017 | | 100.00 | 175.00 | | 19/05/2017 | Loan Part Fund | COLBB00519 | 8***2
| 18ct White Gold Diamond Ring | 19/05/2017
| | 100.00 | 10,530.39 |
Purchased on 19/05/2017 and sold on 20/05/2017 |
|
twoheads
Member of DD Central
Programming
Posts: 1,089
Likes: 1,192
|
Post by twoheads on Jun 7, 2017 15:43:41 GMT
Can you mod this spreadsheet to work with Collateral? I have tried but the problem is that the start and end date of the loans are on separate lines. Sample data | Date | Transaction | Loan Number | Fund ID | Loan
| Start Date | End Date
| Amount | Balance | | 20/05/2017 | Loan Part Sales Purchase | COLBB00519 | 8***2 | 18ct White Gold Diamond Ring | 20/05/2017 | | 100.00 | 175.00 | | 19/05/2017 | Loan Part Fund | COLBB00519 | 8***2
| 18ct White Gold Diamond Ring | 19/05/2017
| | 100.00 | 10,530.39 |
Purchased on 19/05/2017 and sold on 20/05/2017 Hi Cashback Chaser,
I don't have an account with Colleateral at present so this is not something which I can do easily. I think the simplest way to make it work is to calculate interest only on the 'Loan Part Fund' lines and for each one, use a lookup function to find if the matching fund ID appears in a 'Loan Part Sales Purchase' line and fill in the end date from that sale if it is present. It would get tricky if you can sell partial loan parts... but I don't know what is possible and what's not with the Collateral system.
|
|
