gt94sss2
Member of DD Central
Posts: 281 
Likes: 137
|
Post by gt94sss2 on Dec 12, 2015 16:59:15 GMT
It all seems to work fine though on the loans summary sheet I have found all the values against the loans showing as negative figures (not sure why?) Thanks for the spreadsheet which i find very useful. Two questions: 1) Does anyone know why on the loans summary sheet the loans showing as negative figures? Not a big issue - just curious if its by design or user (i.e. my) error! 2) Is it possible to code the spreadsheet to work out the estimated interest due for the current month given the current transaction data (and the threads that some people find the SS calculation short occasionally)?
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Dec 12, 2015 17:06:33 GMT
It all seems to work fine though on the loans summary sheet I have found all the values against the loans showing as negative figures (not sure why?) Thanks for the spreadsheet which i find very useful. Two questions: 1) Does anyone know why on the loans summary sheet the loans showing as negative figures? 2) Is it possible to code the spreadsheet to work out the estimated interest due for the current month given the current transaction data (and the threads that some people find the SS calculation short occasionally)? 1) The source data comes from the Transactions download to Excel, where "Loan Part Fund" transactions (ie. when you buy a loan part) appear as negative figures (ie. reductions to your account balance). If it bothers you I'd suggest either using the (subsequently added by SS) Loan Parts download instead, or simply adding a column on the data sheet that reverses the sign, then picking this column up in the pivot instead. 2) Probably, given that the date field is there, but what's the point? SS pay 1% per month on all their loans so even a rough estimate is usually pretty accurate. [Although there are indeed occasional posts suggesting that interest payments look to be "short" a day, they're usually down to misunderstanding how SS apply daily interest (as 12% * 1/365). I'm not aware anyone's ever actually found a genuine shortfall, but I'm happy to be corrected]
|
|
gt94sss2
Member of DD Central
Posts: 281
Likes: 137
|
Post by gt94sss2 on Dec 12, 2015 17:17:21 GMT
1) The source data comes from the Transactions download to Excel, where "Loan Part Fund" transactions (ie. when you buy a loan part) appear as negative figures (ie. reductions to your account balance). If it bothers you I'd suggest either using the (subsequently added by SS) Loan Parts download instead, or simply adding a column on the data sheet that reverses the sign, then picking this column up in the pivot instead. No - I was just curious if I was doing something wrong - and woudn't ahve the excel skills needed to manipulate your sheet anyway  Yes, its very easy to calculate if there have been no transactions on the account but when buying/selling has occurred especially multiple times or on multiple loan parts in a month, it gets harder/takes much longer - so was just being lazy in looking for an automated solution As for interest payments - agree that its usually misunderstanding how SS apply daily interest but am sure there have been reports here of genuine errors occasionally as well which SS have corrected when brought to their attention.
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on May 2, 2016 14:14:47 GMT
Slightly updated versions (.xls and .xlsx) of the pivot table proforma are attached that: a) include an extra column on the "Transactions Extract" sheet (column J, shaded grey) to reverse the amount sign so that holdings no longer appear as minus figures, and b) incorporate GSV3MIaC 's suggested formula tweak to avoid error messages when certain transactions types aren't yet present in your statement Saving Stream pivot proforma.xlsx (115.46 KB) Saving Stream pivot proforma.xls (435 KB) (usage instructions still as per my OP at the top of this thread) |
|
|
|
Post by Deleted on May 3, 2016 13:14:59 GMT
Steve,
if possible would be useful (to me) have a function counting the 'interest' still due (total accrued in the month) by the person at the moment of running that function, so to use it at the end of the month to do a quick check on the interest calculated/paid by SS.
|
|
|
|
Post by spareapennyor2 on Dec 6, 2017 9:55:43 GMT
is it me but the last few weeks the spreadsheet has been all wrong ?
loan summary amounts over the top / transaction summary now showing nothing
thought it might be the part repayments
started again with a fresh copy
any thoughts on this?
|
|
dawn
Member of DD Central
Posts: 308 
Likes: 275
|
Post by dawn on Dec 6, 2017 12:57:02 GMT
is it me but the last few weeks the spreadsheet has been all wrong ? loan summary amounts over the top / transaction summary now showing nothing thought it might be the part repayments started again with a fresh copy any thoughts on this? I believe an extra column has been added to the Lendy transaction report and this will have pushed other columns out of position. The spreadsheet may need a little tweaking to make sure it is looking up the right columns......
|
|
gwenynwyr
Member of DD Central
Posts: 54
Likes: 23
|
Post by gwenynwyr on Jan 4, 2018 10:22:21 GMT
is it me but the last few weeks the spreadsheet has been all wrong ? loan summary amounts over the top / transaction summary now showing nothing thought it might be the part repayments started again with a fresh copy any thoughts on this? I believe an extra column has been added to the Lendy transaction report and this will have pushed other columns out of position. The spreadsheet may need a little tweaking to make sure it is looking up the right columns...... The extra column is headed "Loan ID". The easy solution is to delete this column before adding to the previous months table. I've no idea what loan ID refers to - all suggestions welcome.
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Jan 4, 2018 10:35:32 GMT
The extra column is headed "Loan ID". The easy solution is to delete this column before adding to the previous months table. I've no idea what loan ID refers to - all suggestions welcome. It's Lendy's internal reference number for each loan, with additional loan ID's created when part repayments are made. So, for example, PBL133 is Loan ID 286, but PBL133R1 is 718, PBL133R2 is 741 and PBL133R3 is 756.
|
|
gwenynwyr
Member of DD Central
Posts: 54
Likes: 23
|
Post by gwenynwyr on Jan 4, 2018 10:51:02 GMT
The extra column is headed "Loan ID". The easy solution is to delete this column before adding to the previous months table. I've no idea what loan ID refers to - all suggestions welcome. It's Lendy's internal reference number for each loan, with additional loan ID's created when part repayments are made. So, for example, PBL133 is Loan ID 286, but PBL133R1 is 718, PBL133R2 is 741 and PBL133R3 is 756. I wonder why it appears in the Excel download? As far as I can see it's not visible anywhere else.
|
|
gwenynwyr
Member of DD Central
Posts: 54
Likes: 23
|
Post by gwenynwyr on Jun 5, 2018 14:09:37 GMT
It's Lendy's internal reference number for each loan, with additional loan ID's created when part repayments are made. So, for example, PBL133 is Loan ID 286, but PBL133R1 is 718, PBL133R2 is 741 and PBL133R3 is 756. These partial repayments,while welcome, do cause chaos in the pivot table due to the lack of a single reference common to all the installments. DFL023 with 10 installments is particularly untidy, as actually it should disapear completely from the table now that it has been repaid. Is there any way to combine the entries to achieve this result?
|
|
