A member due to society is Rs.16596.65 In Jun 11. In Jun 11 end, he pays 20000, monthly maintenance bills is 500.00, need solution which will give principal amt, total interest arrear, current bill amount, current bill interest(interest is calculated on the previous month principal+interest and balance payment column, in this case it is minus so balance should be in minus figure, below calculation is wrong, need same columns, can you help in correct excels forumals in each column below?
Month Op Principal Int. Arrears Cur Bill Cur.Interest Payment Total due
Apr.11 13,524.00 - 774.00 236.67 - 14,534.67
May.11 14,298.00 236.67 774.00 250.22 - 15,558.89
Jun.11 15,072.00 486.89 774.00 263.76 20,000.00 (3,403.36)
Jul.11 (4,154.00) 750.65 774.00 (72.70) - (2,702.05)
Aug.11 (3,380.00) 677.95 774.00 (59.15) - (1,987.20)
Sep.11 (2,606.00) 618.80 774.00 (45.61) - (1,258.81)
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Society Simple Interest Calculation
0
Discussion
It isn't possible to make a calculation like you describe without knowing the interest rate applicable. From your example, the annual rate appears to be around 70% (plus maintenance fee). At that rate your business would be outside the range of where I would be willing to help.
Variatus (rep: 4889) May 1, '17 at 9:34 pm
my apology, interest rate is 21% per annum, maintenance fee is 774. if the account is not going into negative then i am able to do the calculation and everything is coming correctly but when it goes into minus in the month Jun11, the next row formulas needs to change thats what I am not getting
career May 1, '17 at 11:14 pm
Perhaps its easier if you would post what you have so that I don't have to do it all from scratch.
Variatus (rep: 4889) May 2, '17 at 1:12 am
Hi, this is the excel sheet, as you can see there are 7 column below:
Month|Op Principal Amt|Int. Arrears|Cur Bill Amount|Cur.Interest|On Account Payment|Balance Total due.
1. Apr.11 | 13,524.00 | 0.00 | 774.00 | 236.67 | 0.00 | 14,534.67
2. May.11 | 14,298.00 | 236.67 | 774.00 | 250.22 | 0.00 | 15,558.89
3. Jun.11 | 15,072.00 | 486.89 | 774.00 | 263.76 | 20,000.00 | (3,403.35)
4. Jul.11 | (4,154.00) |750.65 | 774.00 | (72.70)| 0.00 | (2,702.05)
5. Aug.11 | (3,380.00) |677.95 | 774.00 | (59.15) | 0.00 | (1,987.20)
6. Sep.11 | (2,606.00) |618.80 | 774.00 | (45.61) | 0.00 | (1,258.81)
When the member makes 20000.00 on account payment on Jun.11, the balance goes into negative 3,403.35 which is correct but on Jul.11, if you can give the formulas to be put in all the 7 columns. Also, though monthly payment will be there from Jul.11 onwards there will not be any int. arrears& int.(3rd & 5th column of int. arrears&interest should be 0 since there is a credit balance of Rs.3403.35 as on Jun.11 and thereafter only 774 to be deducted every month till the 3403.35 becomes 0.00 and then when it becomes 0.00 then formula to add interest again, when it goes to credit balance as above the formule should work and then again when it goes to negative in Nov11, the formula to calculate the interest on prevous month outstanding. thanks in advance.
Month|Op Principal Amt|Int. Arrears|Cur Bill Amount|Cur.Interest|On Account Payment|Balance Total due.
1. Apr.11 | 13,524.00 | 0.00 | 774.00 | 236.67 | 0.00 | 14,534.67
2. May.11 | 14,298.00 | 236.67 | 774.00 | 250.22 | 0.00 | 15,558.89
3. Jun.11 | 15,072.00 | 486.89 | 774.00 | 263.76 | 20,000.00 | (3,403.35)
4. Jul.11 | (4,154.00) |750.65 | 774.00 | (72.70)| 0.00 | (2,702.05)
5. Aug.11 | (3,380.00) |677.95 | 774.00 | (59.15) | 0.00 | (1,987.20)
6. Sep.11 | (2,606.00) |618.80 | 774.00 | (45.61) | 0.00 | (1,258.81)
When the member makes 20000.00 on account payment on Jun.11, the balance goes into negative 3,403.35 which is correct but on Jul.11, if you can give the formulas to be put in all the 7 columns. Also, though monthly payment will be there from Jul.11 onwards there will not be any int. arrears& int.(3rd & 5th column of int. arrears&interest should be 0 since there is a credit balance of Rs.3403.35 as on Jun.11 and thereafter only 774 to be deducted every month till the 3403.35 becomes 0.00 and then when it becomes 0.00 then formula to add interest again, when it goes to credit balance as above the formule should work and then again when it goes to negative in Nov11, the formula to calculate the interest on prevous month outstanding. thanks in advance.
career May 2, '17 at 10:54 am
Variatus - Had put on 2nd May, dont know whether you have seen it or not and possible. Just want to know if it is possible by you without using macro or vb
career May 6, '17 at 2:04 am