What is Monthly Average Balance (MAB) in banking system and how can we calculate in M S Excel.

Monthly Average Balance (MAB) confuses many account holders and they wrongly take it as maintenance of a specified amount in their Savings Bank Account all the time.

However, MAB is actually calculated by adding up all end of day balances in an account in a month, and then dividing it by the number of days in that particular month. While calculating MAB, all bank holidays and working days are included too.

Suppose the “Monthly Average Balance” requirement of your bank is ₹ 10,000. It implies that the bank wants the average in your account to be ₹ 10,000. It does not means that you should have ₹ 10,000 balance at the end of every day. It’s your discretion as to how to go about it. You can maintain a balance of ₹ 10,000 every day or ₹ 3,00,000 (₹ 10,000*30) on any one day of the month which have 30 days in it or ₹ 3,10,000 (₹ 10,000*31) on any one day of the month which have 31 days in the month. Ultimately the average needs to come around ₹ 10,000.

So let's calculate it.

First of all we need bank statement as in picture below.

Here we can see Date, Deposit Amount, Withdraw Amount & Balance Amount & we are going to calculate MAB from these data.

• On April 1, the balance in the account is ₹ 35,000.
• On April 8, withdrawal of ₹ 20,000 takes place. EOD balance is ₹ 15,000.
• On April 16, deposit of ₹ 12,000 & withdraw of ₹ 10,000 takes place. EOD balance is ₹ 17,000.
• On April 22, withdraw of ₹ 15,000 takes place. EOD balance is ₹ 2,000.
• On April 27, deposit of ₹ 1,000 takes place. EOD balance is ₹ 3,000.

Summing up the EOD balances as shown in Picture above:

• From April 1 to April 7 (7 Days), sum of EOD balance will be ₹ (35,000*7) = ₹ 245,000.
• From April 8 to April 15 (8 Days), sum of EOD balances will be ₹ (15,000*8) = ₹ 120,000
• From April 16 to April 21 (6 Days), sum of EOD balances will be ₹ (17,000*6) = ₹ 102,000
• From April 22 to April 26 (5 Days), sum of EOD balances will be ₹ (2,000*5) = ₹ 10,000
• From April 27 to April 30 (4 Days), sum of EOD balances will be ₹ (3,000*4) = ₹ 12,000

Sum of all the EOD closing balances = 489,000

No. of days in April Month = 30 Days

MAB = (Sum of all the EOD closing balances) / (number of days in a month)

So, the Monthly Average Balance will be 489,000/30 i.e. 16,300.

Now if we want to calculate MAB in M S Excel by using formula the stay with me..

First of all we need bank statement  in excel format as in picture below.

now create a new table for MAB calculation by using some formulas shown in picture below.

For above calculation check formula description in below table

For more Information Follow my blog.

Thank You.