You can easily calculate the monthly average balance (MAB) in Google Sheets, using
the DAYS Google Sheets function and custom formulas.
Banks typically levy a fee for not maintaining the required MAB. Some
bank websites now display the MAB for the particular date range.
If the balance is equal to or less than the MAB, there are days left in the month, and you are going to use some or all of the balance in your account, then you need to calculate the minimum amount you should maintain in your savings account for the minimum number of days.
In Google Sheets, a way to calculate the minimum amount is to first calculate your present MAB, then find out the minimum amount and minimum days that will help maintain the MAB.
With the DAYS function, which is one of the Google Sheets functions, you can quickly find out the number of days a particular amount you maintained in your savings account. Then, you have to multiply the amount with the days to get the total amount.
Repeat the above procedure for all the days you have made a transaction. Next, calculate the grand total, using the SUM function of Google Sheets. Finally, divide the grand total with the total days in the month. The result will be your monthly average balance or MAB.
Here is an example of the steps to calculate the MAB.
Date Balance No. of Days with Balance Total Amount
------- ------------- ---------------------------------------- ---------------------
01-11-19 15,130 5 75,650.00
06-11-19 102 4 408.00
07-11-19 0 24 0.00
Grand Total 76,058.00
MAB 2,535.27
In the above example to calculate the monthly average balance or MAB:
1. No. of days are calculated with the DAYS function of Google Sheets.
2. MAB is calculated using the formula Grand Total/Days in the month.
Assume that you have to maintain a monthly average balance of Rs. 3000. Because the present MAB is less than the required MAB, you should calculate the minimum amount and the minimum number of days with that amount in your amount that would help maintain the required MAB.
Now, you need to multiply an amount with the number of days you will maintain that amount. Then, add the result to the Grand Total and see if the
MAB will be equal to or more than the required MAB. Otherwise, you will have to increase either the amount or the days. Repeat this procedure till you get the required MAB.
Do take a look at this tutorial with an example, which gives the steps to calculate the monthly average balance or MAB in Google Sheets. The tutorial also illustrates the steps to find out the minimum amount and days for the required MAB, in case the present MAB is less than the required amount.
