starfished
Member of DD Central
Posts: 296 
Likes: 216
|
Post by starfished on Apr 21, 2014 9:29:24 GMT
Morning
Does anyone know what the weight is to calculate the average rate on the "Your Money On Loan" page. Is it the current balance or the original loan amount?
My 1 and 5 year weighted spreadsheet average is close to the site but not my 3 year one (RS is much much higher). Want to make sure I am not missing something, made a formula mistake somewhere.
Thanks
|
|
pikestaff
Member of DD Central
Posts: 2,136 
Likes: 1,484
|
Post by pikestaff on Apr 21, 2014 14:46:54 GMT
I'm pretty sure it's weighted average. It certainly should be.
My calculation for weighted average rate is sum (balance x rate) / sum (balance).
BTW, am I the only one who finds it really annoying that the site gives us our weighted average rate to just one decimal point?
|
|
|
|
Post by yorkshireman on Apr 21, 2014 16:12:28 GMT
My calculation for weighted average rate is sum (balance x rate) / sum (balance).
Say the balance is £10000, the rate is 5% and call the average rate “X” the calculation then becomes:
X = (10000 x 5) / (10000) and the answer = 5.
Therefore, isn’t the answer the rate you originally started with?
If the £10k balance consisted of £6K @ 5% and £4K @ 6% then the calculation for weighted average would be:
1) £6000 @ 5% = £300
2) £4000 @ 6% = £240
3) Therefore total interest = £540 expressed as a percentage of the total balance is 5.4% = the weighted average rate.
As to how you enter that into a spread sheet, I’ll leave that to more computer savvy individuals than myself.
No criticisms intended and correct me if I’m wrong or misunderstood.
|
|
duck
Member of DD Central
Posts: 2,586
Likes: 5,763
|
Post by duck on Apr 21, 2014 17:19:16 GMT
In Excel the easiest way I find is to use
=SUMPRODUCT(cells containing the amounts in £,cells containing rates for the amounts £ )/SUM(sum of all the £'s) ..... just have 2 columns and then highlight them into the formula.
|
|
|
|
Post by bracknellboy on Apr 21, 2014 18:06:03 GMT
In Excel the easiest way I find is to use
=SUMPRODUCT(cells containing the amounts in £,cells containing rates for the amounts £ )/SUM(sum of all the £'s) ..... just have 2 columns and then highlight them into the formula. Interesting: I've not come across the pleasures of SUMPRODUCT before I don't think. Although I'm not sure it would help me: for FC I calculate a weighted average for each risk band but with all loans on the same sheet. To do this I calculate a 'weighted value' for each loan (row) into a separate column, and then via a pivot table I calculate the weighted average for each risk band (and indeed for all loans). I'll have to have a think as to whether this would make it neater, or if not whether there is somewhere else I could deploy this new toy.
|
|
pikestaff
Member of DD Central
Posts: 2,136
Likes: 1,484
|
Post by pikestaff on Apr 21, 2014 19:26:54 GMT
Yorkshireman - No worries. Your calculation is the same as mine  . So is Duck's! |
|
starfished
Member of DD Central
Posts: 296
Likes: 216
|
Post by starfished on Apr 21, 2014 21:29:18 GMT
Thanks. Like you all, I am currently weighting by balance remaining. However, I am off from the RS figure. I half wondered if time was also a factor but that doesn't seem to be the case (made the gap bigger).
It was close in the past so RS or I may have since changed something. I'll give it a few days and look again.
|
|
starfished
Member of DD Central
Posts: 296
Likes: 216
|
Post by starfished on May 10, 2014 8:54:25 GMT
Dug a bit more and it appears to be weighted by next instalment rather than balance remaining. For 1 and 5 years loans negligible difference between the two but for my 3 year one it appears to make a bit of a difference.
|
|
. So is Duck's!