Post by sl75 on Feb 5, 2015 10:41:32 GMT
I was taking the GEIA thread off-topic, with a discussion about sales and purchases of non-round amounts. 2 of the numbers in the following analysis were too close to whole pennies for comfort, and the amounts of them didn't "make sense"... I've discovered the error in my own analysis, and thus have more clarity of the exact nature of the error in AC's systems - it does indeed seem to be a "display problem" that sales and purchases of non-round amounts are displayed on the statement as if they were for round amounts. See below...
This would have had the effect, in your case, of transferring some loan holding from your GEIA to your MLIA without the corresponding entries in your statement. If you add up all your transactions for your MLIA for that loan then you should find the equal and opposite discrepancy.In MLIA, my total from adding up all "purchase", "sale" and "principal" transactions related to that loan is £25.42432944
AC systems report my holding in this loan of £25.43.
It doesn't look like it balances, either with itself, or with the discrepancy in the GEIA.
I repeat again, every single purchase and sale of every single loan appears as exact number of pence. Given that some sales will be from users who are selling out their entire holding of a loan, where a non-round capital payment has occurred, that seems implausible. This sub-discussion probably wants to be split off to a different thread (as it isn't GEIA-specific), but trawling through my MLIA for a different example, I find the following:
id created_at narrative amount
1711491 2014-10-23 11:04:15.553792+01 Purchase loan part 883356 (old id 873187) for 16.85 GBP - principal 16.85, annualised rate 9.000, loan: L***** R***** Loan Tranche 2 (117) -16.85
1733483 2014-10-27 11:49:52.252294+00 Principal repayment for loan L***** R***** Loan Tranche 2 (117) 0.416617367
1733689 2014-10-27 11:49:52.694821+00 Interest payment for loan L***** R***** Loan Tranche 2 (117) 0.021062454
1798819 2014-10-30 14:28:14.309448+00 Sale of loan part 1158474 (new id 1170169) for 9.65 GBP with a fee of 0 GBP - principal 9.65, annualised rate 9.000, loan: L***** R***** Loan Tranche 2 (117) 9.65
1800500 2014-10-31 02:18:04.835366+00 Sale of loan part 1170255 (new id 1182617) for 6.79 GBP with a fee of 0 GBP - principal 6.79, annualised rate 9.000, loan: L***** R***** Loan Tranche 2 (117) 6.79
2031650 2014-11-28 08:56:16.60126+00 Interest payment for loan L***** R***** Loan Tranche 2 (117) 0.009594357
Taking the statement entries at face value, I now hold about £-0.0066 of that loan in my MLIA, as I purchased £16.85, had capital repayments of £0.416617367, and then sold a total of £16.44 (about £0.0066 more than I was still holding according to the earlier statement entries).
In order to, in part, answer my own question, it appears that in fact the problem is a display issue - aided by re-creating the interest calculations based on a few guesses about the day count rules AC might possibly use.
The history for loan 117 in my MLIA balances correctly (and I can exactly re-create the interest calculations to within the nearest nano-penny!) if I assume that:
- AC calculates interest as amount_held * rate / 12 * days_held / days_in_month
- the repayment received on 27 Oct was calculated as though it was on 28 Oct (i.e. 5 days interest on portion bought 23 Oct and 2 days interest on portion sold on 30 Oct)
- The purchase on 23 Oct was for £16.84996306 rather than tha £16.85 reported.
- The sale on 30 Oct was for £9.643362343 rather than the £9.65 reported.
- The sale on 31 Oct was for £6.789983347 rather than the £6.79 reported.
This suggests that in fact it is a display issue with the generated statement entries, rather than an accounting issue... i.e. the problem is that the statements make it look as though all sales and purchases are for whole pence when in fact many are not. Is this analysis correct?
I subsequently replaced the first assumption with:
- AC calculates interest for a normal monthly payment on the WHOLE LOAN as principal_outstanding * rate / 12, rounded down to a whole penny (the exact amount being shown on the repayments tab of the loan details)
- AC pro-rates the actual interest payment to individual lenders as amount_held * total_interest / total_principal * days_held / days_in_month, to a very small fraction of a penny.
This then results in the last 3 assumptions (to make everything balance) being:
- The purchase on 23 Oct was for £16.85 exactly as reported.
- The sale on 30 Oct was for £9.643382633 rather than the £9.65 reported.
- The sale on 31 Oct was for £6.79 exactly as reported.
I would note that, given the constraints from the assumed interest calculation method and from the (unwritten) assumption that "everything balances", the nice round numbers shown were the RESULT of a "goal-seek" calculation to get the same interest value that AC provided, rather than being a nice round number I was assuming in advance.
As such, it would seem that I have in fact replicated the interest calculation method, and only ONE of the 3 transactions was actually for a non-round amount.
The problem remains that my statement shows a sale of £9.65 on 30 Oct when in fact I must have sold £9.643382633. If AC wish to show us numbers with sub-penny precision (e.g. on the statement or on a hover-over box) they need to do so consistently, rather than mixing precise numbers with rounded ones as occurs on the current statement. Over to chris and/or Colin?
Feb 4, 2015 17:37:03 GMT sl75 said:
AC systems report my holding in this loan of £25.43.
It doesn't look like it balances, either with itself, or with the discrepancy in the GEIA.
I repeat again, every single purchase and sale of every single loan appears as exact number of pence. Given that some sales will be from users who are selling out their entire holding of a loan, where a non-round capital payment has occurred, that seems implausible. This sub-discussion probably wants to be split off to a different thread (as it isn't GEIA-specific), but trawling through my MLIA for a different example, I find the following:
id created_at narrative amount
1711491 2014-10-23 11:04:15.553792+01 Purchase loan part 883356 (old id 873187) for 16.85 GBP - principal 16.85, annualised rate 9.000, loan: L***** R***** Loan Tranche 2 (117) -16.85
1733483 2014-10-27 11:49:52.252294+00 Principal repayment for loan L***** R***** Loan Tranche 2 (117) 0.416617367
1733689 2014-10-27 11:49:52.694821+00 Interest payment for loan L***** R***** Loan Tranche 2 (117) 0.021062454
1798819 2014-10-30 14:28:14.309448+00 Sale of loan part 1158474 (new id 1170169) for 9.65 GBP with a fee of 0 GBP - principal 9.65, annualised rate 9.000, loan: L***** R***** Loan Tranche 2 (117) 9.65
1800500 2014-10-31 02:18:04.835366+00 Sale of loan part 1170255 (new id 1182617) for 6.79 GBP with a fee of 0 GBP - principal 6.79, annualised rate 9.000, loan: L***** R***** Loan Tranche 2 (117) 6.79
2031650 2014-11-28 08:56:16.60126+00 Interest payment for loan L***** R***** Loan Tranche 2 (117) 0.009594357
Taking the statement entries at face value, I now hold about £-0.0066 of that loan in my MLIA, as I purchased £16.85, had capital repayments of £0.416617367, and then sold a total of £16.44 (about £0.0066 more than I was still holding according to the earlier statement entries).
In order to, in part, answer my own question, it appears that in fact the problem is a display issue - aided by re-creating the interest calculations based on a few guesses about the day count rules AC might possibly use.
The history for loan 117 in my MLIA balances correctly (and I can exactly re-create the interest calculations to within the nearest nano-penny!) if I assume that:
- AC calculates interest as amount_held * rate / 12 * days_held / days_in_month
- the repayment received on 27 Oct was calculated as though it was on 28 Oct (i.e. 5 days interest on portion bought 23 Oct and 2 days interest on portion sold on 30 Oct)
- The purchase on 23 Oct was for £16.84996306 rather than tha £16.85 reported.
- The sale on 30 Oct was for £9.643362343 rather than the £9.65 reported.
- The sale on 31 Oct was for £6.789983347 rather than the £6.79 reported.
This suggests that in fact it is a display issue with the generated statement entries, rather than an accounting issue... i.e. the problem is that the statements make it look as though all sales and purchases are for whole pence when in fact many are not. Is this analysis correct?
- AC calculates interest for a normal monthly payment on the WHOLE LOAN as principal_outstanding * rate / 12, rounded down to a whole penny (the exact amount being shown on the repayments tab of the loan details)
- AC pro-rates the actual interest payment to individual lenders as amount_held * total_interest / total_principal * days_held / days_in_month, to a very small fraction of a penny.
This then results in the last 3 assumptions (to make everything balance) being:
- The purchase on 23 Oct was for £16.85 exactly as reported.
- The sale on 30 Oct was for £9.643382633 rather than the £9.65 reported.
- The sale on 31 Oct was for £6.79 exactly as reported.
I would note that, given the constraints from the assumed interest calculation method and from the (unwritten) assumption that "everything balances", the nice round numbers shown were the RESULT of a "goal-seek" calculation to get the same interest value that AC provided, rather than being a nice round number I was assuming in advance.
As such, it would seem that I have in fact replicated the interest calculation method, and only ONE of the 3 transactions was actually for a non-round amount.
The problem remains that my statement shows a sale of £9.65 on 30 Oct when in fact I must have sold £9.643382633. If AC wish to show us numbers with sub-penny precision (e.g. on the statement or on a hover-over box) they need to do so consistently, rather than mixing precise numbers with rounded ones as occurs on the current statement. Over to chris and/or Colin?
