Housing Society Maintenance System in Excel: Quarterly Bills, Sinking Fund, Interest on Arrears, Receipts, Expenses and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: housing-society-maintenance-management-system-template.xlsx: a 10-sheet system with example data and real formulas (Read me, Setup, Flats, Bills, Receipts, Ledger, Expenses, Report, Bill print, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Shraddha Co-operative Housing Society, Kolhapur, October to December 2026 quarter, as of 17 October (made-up data). Maintenance ₹3.50 and sinking fund ₹0.25 per sq ft a month, parking ₹300 a month, interest on arrears 21% a year, bills due 15 October.

The interest rate and charges must follow your bye-laws and general body decisions; the sheet only applies them.

2. Build it sheet by sheet

Step 1. Setup and Flats

1. B4 quarter start, B5 maintenance per sq ft per month (₹3.50), B6 sinking fund (₹0.25), B7 parking per month, B8 interest on arrears per year, B9 bill due date.
2. The model bye-laws for Maharashtra housing societies allow simple interest of up to 21% a year on arrears; type the rate your bye-laws set.
3. Flats: Flat, Member, Area (sq ft), Parking?, Arrears brought forward.

Step 2. Bills: quarter charges and interest

1. C5 maintenance =ROUND(Flats!C5*Setup!$B$5*3,0): 850 sq ft × ₹3.50 × 3 = ₹8,925 for A-201.
2. D5 sinking fund the same way, E5 parking =IF(Flats!D5="Yes",Setup!$B$7*3,0).
3. F5 interest: =ROUND(Flats!E5*Setup!$B$8*3/12,0), simple interest for 3 months; ₹6,200 arrears give ₹326.
4. G5 quarter charges =SUM(C5:F5), H5 arrears and I5 total due =G5+H5 (ROUND).

Step 3. Receipts and Ledger

1. Receipts: Receipt no, Date, Flat, Member, Amount, Mode. A5 ="SR-"&TEXT(ROW()-4,"000").
2. Ledger D5 paid this quarter: =SUMIFS(Receipts!$E$5:$E$2000,Receipts!$C$5:$C$2000,A5,Receipts!$B$5:$B$2000,">="&Setup!$B$4) (SUMIFS), E5 outstanding =C5-D5.
3. F5 status: =IF(E5<=0,"Clear",IF(Setup!$B$3>Setup!$B$9,"Overdue","Due")), coloured with conditional formatting.

Step 4. Expenses and Report

1. Expenses: Date, Head, Amount, Paid to, with the head from a drop-down.
2. Report B5 =SUMIFS(Expenses!$C$5:$C$2000,Expenses!$B$5:$B$2000,A5,Expenses!$A$5:$A$2000,">="&Setup!$B$4) and C5 share.
3. Rows 14 to 17: total due, collected, collection % and collected minus expenses.

Step 5. Bill print and Dashboard

1. Bill print: type a flat in B4; rows 6 to 12 look up each charge from Bills and B14 shows the due date. Set the print area.
2. KPI cards: total due, collected, collection %, outstanding, flats overdue and expenses. Charts: expenses by head and outstanding by flat.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Compound interest on arrears. Charge simple interest on the arrears only, as the bye-laws describe, not interest on interest.
2. Interest on the current bill. Interest applies to arrears brought forward, not to a bill that is not yet due.
3. Forgetting to carry arrears forward. At quarter end, copy Ledger outstanding into Flats arrears before changing the quarter start.
4. Receipts without a flat number. A payment without the flat cannot reach the ledger. Pick the flat from the drop-down.
5. Mixing sinking fund with expenses. Sinking fund is set aside for major repairs. Keep it in its own bill column and bank account.

5. More real examples

Example 1: Apartment association. Use a flat amount per flat instead of per sq ft: replace the area formula with a fixed rate.

Example 2: Commercial complex. Add a Unit type column (shop or office) and a rate per type looked up from Setup.

Example 3: Row-house colony. Bill water and garden charges per plot with their own columns on Bills.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate society maintenance per sq ft in Excel?

Area × rate × months: =ROUND(area*rate*3,0) for a quarter.

How do I calculate interest on arrears?

Simple interest: arrears × yearly rate × months ÷ 12, as in Bills F5.

What interest can a housing society charge?

In Maharashtra the model bye-laws allow up to 21% a year simple interest on arrears; your society's adopted bye-laws decide the actual rate.

How do I list defaulters?

Ledger status shows Overdue when anything is outstanding after the due date; filter that column.

How do I see collection %?

Collected ÷ total due, as in Report B16.

7. Quick quiz

Ten questions on this page. Sign in to save your score.