NonStopLeo
New Member
- Joined
- Mar 25, 2010
- Messages
- 38
Dear Friends
I need a formula to amortize my expenses in below sheet. I want in Column L monthly amortization on the basis, if full month is used then it should calculate for 30 days, if not full month then calculate based on day used in that month.
Days in year base should be 30 days. Kindly help.
<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
I need a formula to amortize my expenses in below sheet. I want in Column L monthly amortization on the basis, if full month is used then it should calculate for 30 days, if not full month then calculate based on day used in that month.
Days in year base should be 30 days. Kindly help.
COMPANY NAME | ||||||||||||
INSURANCE AMORTIZATION | Opening | Closing | ||||||||||
01-Feb-18 | 28-Feb-18 | |||||||||||
Cost | Opening | Closing | ||||||||||
S.No | Start Date | End Date | Ref No | Description | No of Days | AMOUNT | No of Days | Amortization | No of Days | Amortization | Charge for the Month | Prepaid Balance |
1 | ||||||||||||
2 | 1-Oct-17 | 31-Dec-17 | BPV-8417 | ADCO Fujairah | 90.00 | 3,600.00 | 90.00 | 3,600.00 | 90.00 | 3,600.00 | - | - |
3 | 11-Oct-17 | 7-Feb-18 | BPV-8489 | Mirfa Land - Bouroge | 116.00 | 5,530.00 | 110.00 | 5,243.97 | 116.00 | 5,530.00 | 286.03 | - |
4 | 6-Feb-17 | 5-Feb-18 | 6677 | 01/4102/41/2017/50 | 359.00 | 2,712.00 | 355.00 | 2,681.78 | 359.00 | 2,712.00 | 30.22 | - |
5 | 1-Mar-17 | 28-Feb-18 | 15763 | 01/4111/41/2017/14 | 357.00 | 2,220.00 | 330.00 | 2,052.10 | 357.00 | 2,220.00 | 167.90 | - |
6 | 15-Mar-17 | 28-Feb-18 | 14988 | 01/4111/41/2017/14 | 343.00 | 3,232.00 | 316.00 | 2,977.59 | 343.00 | 3,232.00 | 254.41 | - |
7 | 1-Apr-17 | 31-Mar-18 | W/C | 01/1151/11/2016/38 | 360.00 | 4,588.00 | 300.00 | 3,823.33 | 330.00 | 4,205.67 | 382.33 | 382.33 |
8 | 11-Jul-17 | 28-Feb-18 | 78427 | 01/4111/41/2017/14 | 227.00 | 1,681.13 | 200.00 | 1,481.17 | 227.00 | 1,681.13 | 199.96 | - |
9 | 20-Jul-17 | 15-Jan-18 | 99609 | 01/4102/41/2017/251 | 175.00 | 1,127.00 | 175.00 | 1,127.00 | 175.00 | 1,127.00 | - | - |
10 | 28-Jul-17 | 25-Jan-18 | 56570 | 01/4102/41/2017/259 | 177.00 | 3,226.00 | 177.00 | 3,226.00 | 177.00 | 3,226.00 | - | - |
11 | 7-Aug-17 | 30-Jul-18 | 70691 | 01/4102/41/2017/263 | 353.00 | 2,182.00 | 174.00 | 1,075.55 | 204.00 | 1,260.99 | 185.44 | 921.01 |
12 | 17-Aug-17 | 16-Aug-18 | 93187 | 01/4102/41/2017/268 | 359.00 | 2,500.00 | 164.00 | 1,142.06 | 194.00 | 1,350.97 | 208.91 | 1,149.03 |
13 | 27-Aug-17 | 26-Aug-18 | 87816 | 01/4102/41/2017/287 | 359.00 | 6,050.00 | 154.00 | 2,595.26 | 184.00 | 3,100.84 | 505.57 | 2,949.16 |
14 | 25-Sep-17 | 28-Feb-18 | 85151 | 01/4111/41/2017/14 | 153.00 | 2,293.00 | 126.00 | 1,888.35 | 153.00 | 2,293.00 | 404.65 | - |
15 | 23-Aug-17 | 15-Feb-18 | 68521 | 01/4102/41/2017/274 | 172.00 | 1,300.00 | 158.00 | 1,194.19 | 172.00 | 1,300.00 | 105.81 | - |
16 | 10-Sep-17 | 9-Sep-18 | 16999 | 01/4102/41/2017/303 | 359.00 | 2,345.00 | 141.00 | 921.02 | 171.00 | 1,116.98 | 195.96 | 1,228.02 |
17 | 18-Sep-17 | 28-Feb-18 | 99717 | 01/4111/41/2017/14 | 160.00 | 1,065.00 | 133.00 | 885.28 | 160.00 | 1,065.00 | 179.72 | - |
18 | 30-Oct-17 | 30-Apr-18 | 54234 | 05/4102/41/2017/191 | 180.00 | 2,470.00 | 91.00 | 1,248.72 | 121.00 | 1,660.39 | 411.67 | 809.61 |
19 | 26-Oct-17 | 20-Apr-18 | 68137 | 05/4102/41/2017/189 | 174.00 | 2,308.00 | 95.00 | 1,260.11 | 125.00 | 1,658.05 | 397.93 | 649.95 |
20 | 26-Oct-17 | 15-Oct-18 | 98959 | 05/4102/41/2017/175 | 349.00 | 1,728.00 | 95.00 | 470.37 | 125.00 | 618.91 | 148.54 | 1,109.09 |
21 | 2-Nov-17 | 30-Apr-18 | 75941 | 05/4102/41/2017/193 | 178.00 | 1,922.00 | 89.00 | 961.00 | 118.00 | 1,274.13 | 313.13 | 647.87 |
22 | 10-Nov-17 | 5-May-18 | 53892 | 05/4102/41/2017/197 | 175.00 | 2,416.00 | 81.00 | 1,118.26 | 110.00 | 1,518.63 | 400.37 | 897.37 |
23 | 9-Nov-17 | 8-Dec-18 | 96115 | 05/4102/41/2017/198 | 389.00 | 1,790.00 | 82.00 | 377.33 | 111.00 | 510.77 | 133.44 | 1,279.23 |
24 | 29-Nov-17 | 28-Feb-18 | 67183 | 01/4111/41/2017/14 | 89.00 | 559.00 | 62.00 | 389.42 | 89.00 | 559.00 | 169.58 | - |
25 | 29-Nov-17 | 28-Feb-18 | 16574 | 01/4111/41/2017/14 | 89.00 | 560.00 | 62.00 | 390.11 | 89.00 | 560.00 | 169.89 | - |
26 | 6-Dec-17 | 28-Feb-18 | 97201 | 01/4111/41/2017/14 | 82.00 | 517.00 | 55.00 | 346.77 | 82.00 | 517.00 | 170.23 | - |
27 | 27-Nov-17 | 28-Feb-18 | 64160 | 01/4111/41/2017/14 | 91.00 | 1,366.00 | 64.00 | 960.70 | 91.00 | 1,366.00 | 405.30 | - |
28 | 11-Dec-17 | 28-Feb-18 | 26890 | 01/4111/41/2017/14 | 77.00 | 511.00 | 50.00 | 331.82 | 77.00 | 511.00 | 179.18 | - |
29 | 13-Dec-17 | 12-Dec-18 | 85170 | 05/4102/41/2017/218 | 359.00 | 3,202.00 | 48.00 | 428.12 | 77.00 | 686.78 | 258.66 | 2,515.22 |
30 | 28-Dec-17 | 27-Dec-18 | 65705 | 05/4102/41/2017/237 | 359.00 | 4,410.00 | 33.00 | 405.38 | 62.00 | 761.62 | 356.24 | 3,648.38 |
31 | 21-Dec-17 | 28-Feb-18 | 74061 | 01/4111/41/2017/14 | 67.00 | 292.00 | 40.00 | 174.33 | 67.00 | 292.00 | 117.67 | - |
TOTAL | 69,702.13 | 44,777.09 | 51,515.85 | 6,738.75 | 18,186.28 |
<colgroup><col><col span="2"><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>