To get the exact count of Debits and also the Date of the Balance in the Savings Acct

**e4excel** · 01-18-2009, 01:01 PM

I need to know how many debits can take place in my Savings Account if I provide the Current Balance and the No of Debits,Amount of Debits,Frequency of Each Debit.

Lets say, I have Rs 42978/- in my savings account at this moment and I have 2 different Debits taking place on different dates of the Month.
First Debit of Rs 750/- (12th of Month)
Second Debit of Rs 584/- ( 27th of Month)

I am also planning to add one more debit EMI (Equated Onthly Installments)for Rs 1127/- every month.
I need to know the No of Months I can go without paying my Savings Account as well as the Month and the year.

I have tried doing it the regular way but it becomes quite cumbersome, I am looking for help in terms of a better design or a Template as some single-cell (hopefully) formula which can incorporate the number of Debits,Amounts etc.

One very important thing is to also keep a track of the Balance not going below an "X" amount and that is Rs 1500/- as thats the Bank's Minimum Balance requirement..

The no of Installments are as mentioned below:
Debit--- Amount--- Start Month--- No of Installments

I Debit--- 750--- Jan-09--- 36
II Debit--- 584--- Feb-09--- 27
III Debit--- 1127--- Mar-09--- 60

**vandanavai** · 01-19-2009, 03:20 AM

Hello

see if attached file helps u.
change date in C2-Orange colour cell to see changes in balance. For daily balance put =today() in cell C2. also see formulas in green coloured cells.

**e4excel** · 01-19-2009, 03:34 AM

Dear Vandana,

I did like the design, however I am not able to use it as the links are from your Computer..Im getting an error for #NAME...

So can u attach a new filw with independent formulae..

**vandanavai** · 01-19-2009, 03:42 AM

Hello

Error is due to use of function EOMONTH. It seems analysis tool pack is not installed on ur comp. I am sending another file. Only thing is u will have to enable macros while opening file.

**vandanavai** · 01-19-2009, 03:45 AM

Hello

See attached file

**e4excel** · 01-19-2009, 06:21 AM

Vandana,

Appreciate your help for the template, however I need to get the Date especially the Date/Month/Year when I would need to replenish my account..

I want to avoid putting the information of the Debits redundantly and just enter it once and I should get an answer of the Date when i need to add money to my account for the future debits to take place..

I will give you an Analogy

In Networkdays functions howe we just put the Start Date and the End Date and get the Working Days..

Similarly I want to put all the Debits their Amounts,Date of Debits,Frequenct and then get the Date when the Saving Account goes one step before hitting the Bank's Minimum Balance stage..

Hope I have explained you well...I think you would be aware of all the Indian Banking stuff..

**vandanavai** · 01-19-2009, 07:23 AM

Hello

Ok see attached file. Now try to use 'goal seek' function

Set Cell F2
To value 41000 (42978-1500 = 41478)
By changing cell C2

**e4excel** · 01-19-2009, 05:49 PM

Dear Vandana,

Unfortunately, Im not able to get what I want, I appreciate your help sincerely...but I need to get the MOnth till my account needs to be replenished..without putting redundant information...

I like your template but it does not give me the month till which the account can go unpaid...

**vandanavai** · 01-20-2009, 03:00 AM

Hello

Have u tried 'goal seek'. In fact it gives u date till u go without depositing any amount in Saving Account.
Do 'goal seek' and then see date in cell C2.

**e4excel** · 01-20-2009, 04:10 AM

I did try that but it did not work the way I wanted, maybe I need to change the approach..

**vandanavai** · 01-20-2009, 08:06 AM

Hello

See attached file. enter data in green cells. , than press calculate button and see result in pink colour cell.
Drawback of file is that presently it take care or one type of EMI only.
But if it is working ,one can always add more EMI types.

**e4excel** · 01-20-2009, 09:13 AM

Originally Posted by vandanavai

Hello

See attached file. enter data in green cells. , than press calculate button and see result in pink colour cell.
Drawback of file is that presently it take care or one type of EMI only.
But if it is working ,one can always add more EMI types.

I like this design better than the previous one for sure...
As you are aware there are going to be more Debits=EMI's I need to incorporate the same...
There are some anomalies I like to cite...when you type Current Balance =Rs 1500/- and Min Bal =Rs 1500 same then it still gives 1 EMI..

I sincerely appreciate your efforts and I believe this is indeed very complex more than what it appears from my explanation ..Unfortuantely Im not able to come up with a DEsign or else maybe it would have been easier for someone to give me a solution by now..

I am trying to achieve the same via formulas to keep it more dynamic and more importantly well within my reach as Im nt aware of VBA at all..

Well, nothings lost I liked your design which will help me finalise my format...
Thanks for not giving up...I am really sorry to let you down

but let me just come up with something...

Warm regards
e4excel

**e4excel** · 01-20-2009, 04:14 PM

I am trying very hard but am not able to come with a Feasible design..
I need to do this as really need to manage a lot of accounts with a lot of debits happening in each of them.

I need to arrange the different debits intertwined in between each other so that I can sum the amounts and then match with the Banks MIn Balance..

Please someone help me...I am at my wits end...pondering this the entire day

**e4excel** · 01-21-2009, 04:49 AM

Dear Forum,

I have worked on this file but not satisfied and need help..please some expert/gurus please help me by making it more robust and compact..

My approach is a very simple approach it does not take care of Multiple Debits in one column and hence will have to be changed each time a new debit is added..

Some good samaritan can help me improvise ,re-engineer or come up with something new adhering to the requirements..

Please someone....

**e4excel** · 01-21-2009, 06:15 AM

I am not able to upload my file...
Want tomeone to help me on this..

**e4excel** · 01-21-2009, 07:05 AM

Please forum...someone help me on the same..

**e4excel** · 01-22-2009, 02:33 PM

Bump no response...
Bump no response...

**e4excel** · 01-27-2009, 02:31 PM

Bump no response...
Bump no response...

Please someone try to look at the file to understand the requirement aand also am looking for a Formula Solution

**e4excel** · 01-29-2009, 08:51 AM

I think I got an Idea of how to go about solving this , there's no direct way but to have a Column full of Dates with the Amounts next to them

To get the exact count of Debits and also the Date of the Balance in the Savings Acct

LinkBack

Thread Tools

Rate This Thread

Display

To get the exact count of Debits and also the Date of the Balance in the Savings Acct

Unfortunately the links are not getting connected!

Confused..!

I am sorry but the peak is still ahead..

Yes I tried that..

New Approach

Hey thanks Vandana

Forum please help to arrive with a design...

Forum Need help to refine this format.

Try this out

Finally after zipping the file

Bump no response...

Bump no response...

Re: To get the exact count of Debits and also the Date of the Balance in the Savings

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions