compound interest on compound capital

[rdcmol]

Based on the Eonia (Euro Overnigt Index Average) we offer our accountholders
on their entrepreneur's account a supplementary intrest rate.

Creditamount----------------------- interest
Euro 0 - 25.000 ------------------30% of the Eonia (per today = 0.873%)
Euro 25.001 - 150.000 --------50% of the ...................................
Euro 150.001 - 300.000 ------70% of the ...................................
Euro 300.001 - 500.000 ------90% of the ...................................

I'm looking for a formula or macro where I only have to fill the actual Eonia and the Creditamount on the account
resulting in the interestpercentage and value.

let's assume :
Customer has Eur 267.000 on his account.
On the first 25.000 customer willll get 30%of 0.873 = (0.2619 * 25000)/100 = 65,47
On the second part 125.000 he'll get 50% of 0,873 = (0.4365 * 125000)/100 = 545.62
In the last 117.000 he'll get 70% of 0.873 =( 0,6111 * 117000)/100 = 714,98

This is an extra Eur 1326,07 compared to competition
I've tried with vlookup, googled compound, but only got an increased bloodpressure

please help me make this workable on the field in a user form

thank you and regards

Rik De Ceuster

 

[Richard Schollar]

Hi Rik

Here's one way:

Eonia rate entered in E1, customer balance in B13. Formula to work out amount is in C13. Formulas in C2:D2 are copied down as far as required.

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Lower limit</td><td style="font-weight: bold;;">% of Eonia</td><td style="font-weight: bold;;">Resultant rate</td><td style="font-weight: bold;;">Differential Rate</td><td style="font-weight: bold;text-align: right;;">0.8730%</td><td style="font-weight: bold;;">=Eonia</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">30%</td><td style="text-align: right;;">0.261900%</td><td style="text-align: right;;">0.2619%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">25001</td><td style="text-align: right;;">50%</td><td style="text-align: right;;">0.436500%</td><td style="text-align: right;;">0.1746%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">150001</td><td style="text-align: right;;">70%</td><td style="text-align: right;;">0.611100%</td><td style="text-align: right;;">0.1746%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">300001</td><td style="text-align: right;;">90%</td><td style="text-align: right;;">0.785700%</td><td style="text-align: right;;">0.1746%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;;">Balance</td><td style="text-align: right;;">267000</td><td style="text-align: right;;">1326.083508</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=0.873%</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=B2*$E$1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=C2-N(<font color="Blue">C1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C13</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">B13>$A$2:$A$5</font>)*(<font color="Red">B13-$A$2:$A$5</font>)*$D$2:$D$5</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Sal Paradise]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Balance</td><td style=";">Rate</td><td style=";">Interest</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">EONIA</td><td style="text-align: right;;">0.873%</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Account A</td><td style="text-align: right;;">431829</td><td style="text-align: right;;">0.786%</td><td style="text-align: right;;">3392.88 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Account B</td><td style="text-align: right;;">233790</td><td style="text-align: right;;">0.611%</td><td style="text-align: right;;">1428.69 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Account C</td><td style="text-align: right;;">78123</td><td style="text-align: right;;">0.437%</td><td style="text-align: right;;">341.01 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Balance</td><td style=";">Rate</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Account D</td><td style="text-align: right;;">822405</td><td style="text-align: right;;">0.786%</td><td style="text-align: right;;">6461.64 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">30%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Account E</td><td style="text-align: right;;">347429</td><td style="text-align: right;;">0.786%</td><td style="text-align: right;;">2729.75 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">25000</td><td style="text-align: right;;">50%</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Account F</td><td style="text-align: right;;">968225</td><td style="text-align: right;;">0.786%</td><td style="text-align: right;;">7607.34 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">150000</td><td style="text-align: right;;">70%</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Account G</td><td style="text-align: right;;">10632</td><td style="text-align: right;;">0.262%</td><td style="text-align: right;;">27.85 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">300000</td><td style="text-align: right;;">90%</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Account H</td><td style="text-align: right;;">836745</td><td style="text-align: right;;">0.786%</td><td style="text-align: right;;">6574.31 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Account I</td><td style="text-align: right;;">786561</td><td style="text-align: right;;">0.786%</td><td style="text-align: right;;">6180.01 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=INDEX(<font color="Blue">$H$5:$H$8,MATCH(<font color="Red">$B2,$G$5:$G$8</font>),</font>)*$H$1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=C2*B2</td></tr></tbody></table></td></tr></table><br />

Feel free to combine columns C and D

 

[rdcmol]

Thank You Richard
Headache gone, bloodpressure to normal.
You must be a magician!
Made my day
rik

 

[rdcmol]

u2 Sal, thanks a lot !!!

 

[Sal Paradise]

Mine is incorrect. I misread your question. Richard's is correct. My apologies.

 

[rdcmol]

Nevertheless, thank you for your time and effort
regards
rik