# Lending library Excel sheet: loans and fines (free)

> Free Excel sheet for a school or community lending library: book catalogue, members, loans with due dates, late fines with a cap, overdue list and availability.

Source: https://softwiro.com/blog/lending-library-free-excel-online.html
Language: English
Softwiro, softwiro.com

---

Free Excel template

# Lending library management in Excel: books, members, loans and late fines (free)

Know where every book is, who is late and how much they owe, and which title has run out of copies.

15 November 2026 5 min read

## In short

- Each member type has its own loan period and maximum number of books, set once in Settings.
- Due date = loan date + period; days late run to the return date, or to today while the book is out.
- Fine = late days × daily fine, never above the cap you set; paid and unpaid are tracked per loan.
- Available copies = copies − on loan − lost or damaged, so a title with no copies left is obvious.
- Warnings flag an unknown code, an expired membership at loan time, and a member over their book limit.

You run a library at a school, a mosque, a club or in a neighbourhood, and you want to know at any moment: where is each book, who is late returning one and how much fine do they owe, which title has run out of copies, and has any member borrowed more than they are allowed? The free [lending library management sheet](https://softwiro.com/templates/lending-library) (the page is in Arabic) answers from things you type: your books, your members and one row per loan.

## The problem: a book leaves and nobody remembers where it went

A paper borrowing notebook records who took the book, but it does not work out the return date, does not tell you how many copies of a title are left, and does not warn you that a member holds four books when their limit is three. Fines turn into favours or arguments because nobody knows the exact number of late days. The sheet puts the library's rules in **الإعدادات (Settings)** once and applies them to every row.

## How it works, step by step

1. In الإعدادات (Settings) type today's date, the fine per late day and its maximum per loan, then for each member type (student, teacher, ordinary member for example) the loan period and the maximum number of books out at once.
2. In الكتب (Books) write each title with its code, author, category, number of copies and shelf place.
3. In الأعضاء (Members) write each member with a code, type, phone and membership expiry date.
4. In الاستعارة (Loans) write only the date, the member code and the book code for each loan. The rest fills in by formula.
5. When the book comes back, type the return date in the same row, and the fine the member paid if any. While the return date is empty the book is out of the library.
6. Record anything damaged or lost in التالف والمفقود (Lost and damaged), open المتأخرات (Overdue) to call the borrowers, and الملخص (Summary) for the month's numbers.

## What each sheet does

- Books: available now = copies − on loan now − lost or damaged. The status reads available or not available now, with a «count error» warning if more is recorded than you own.
- Members: books out now against the maximum for their type, total loans and fines, and a status: fine, has overdue, over limit or membership expired.
- Loans: due date = loan date + the period for the type. Days late = (return date or today) − due date, fine = days × daily fine up to the cap, and unpaid = fine − paid. A warning shows for an unknown code, an expired membership at the time of the loan, or going over the book limit.
- Overdue and Most borrowed: two lists that sort themselves, one for late borrowers and one for the most borrowed books and most active members.
- Lost and damaged, and Summary: the compensation still owed per row, library and fine figures, and the month's loans split by category.

## A walk-through with the sample numbers

These numbers are sample data inside the sheet, not a real library: 12 titles in 25 copies, 10 members and 34 loans. Today is 15 November 2026, the fine is 2 pounds a day with a cap of 40, and the loan period is 14 days for a student, 30 for a teacher and 21 for an ordinary member.

- Where things stand today: 18 copies are out, 2 copies are out of service (one damaged, one lost), so only 5 copies are available. «Al-Ayyam» by Taha Hussein has 3 copies, all out, so its status is «not available now».
- Overdue: three loans. Marwa El-Sayed is 23 days late with «Chemistry for Everyone»; the fine would be 46 pounds but the cap stops it at 40. Menna Hassan is 16 days late (32 pounds) and Abdelrahman Saleh 13 days (26 pounds). Running fines total 98 pounds.
- All fines: 202 pounds across all loans, of which 54 was paid, leaving 148.
- Members: Ahmed Sameh holds 4 books against a student limit of 3, so his status is «over limit» and the warning shows on the fourth loan. Laila Ibrahim's membership expired on 31 October and she borrowed on 4 November, so the expired-membership warning shows.
- The chosen month (October): 21 loans, 9 of them returned within the month, and most were in «novels and literature» with 6. The most borrowed book is «Al-Ayyam» with 6 loans, and the most active member is Ahmed Sameh with 7.
- Lost and damaged: compensation due 160 pounds, 40 collected.

## Common mistakes

- Forgetting to type the return date, so the book stays out and its lateness and fine grow every day.
- Duplicating a book code, or typing a member code differently from the Members sheet, which shows «not found» and calculates nothing.
- Not updating today's date in Settings, so overdue loans and running fines look smaller than they are.
- Leaving a lost book on loan. Put a return date on its loan row and record the loss in its own sheet.
- Setting a fine with no cap, so the fine exceeds the price of the book. Choose a cap that suits your library or that its managers approve.

## When to move to a system

The sheet suits a library run by one librarian with up to a few thousand loans. When there are too many members to type codes by hand, or you want cards and barcodes, automatic reminders before the due date, or members reserving a book from their phone, that is work for a [custom Softwiro program](https://softwiro.com/contact). And if you sell books rather than lend them, the [general shop inventory](https://softwiro.com/templates/general-inventory) sheet fits better.

## Your next step

Download the sheet, write your member types, loan period and the fine your library management approves in Settings, then enter the first twenty books and ten members and log this week's loans. Open Overdue at the end of the week.

## Common questions

**Does it work for a library with no fines?** Yes, set the daily fine to zero and the late days still show.

**Does it work on a phone?** Yes, in Excel, Google Sheets and LibreOffice.

**Who decides the loan period and the fine?** Your library's management. The numbers in the sheet are examples, so type what you approve.

## Free cheat sheet for this field

Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.

[![Cheat sheet: lending library (1/3)](https://softwiro.com/uploads/cheatsheets/lending-library/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/lending-library/en-1.jpg)[![Cheat sheet: lending library (2/3)](https://softwiro.com/uploads/cheatsheets/lending-library/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/lending-library/en-2.jpg)[![Cheat sheet: lending library (3/3)](https://softwiro.com/uploads/cheatsheets/lending-library/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/lending-library/en-3.jpg)

0 downloads · 3 pages

## When your work grows: a custom system instead of the sheet

A sheet is a great start. When more than one person works on your data, when it grows, or when you need alerts and reports of your own, we build you a custom system at competitive prices. This is what changes:

| In the sheet | In a custom system |
| --- | --- |
| One file that one person edits at a time, and copies travel by WhatsApp. | A program with a login for each user and permissions you set; everyone sees the same data live. |
| You type and check every number by hand, and one wrong cell breaks the result. | Screens built around your steps: each person records only their part, and the calculations run on their own. |
| Limited size; the file slows down as the data grows. | Holds years of data without slowing down, with instant search and filters. |
| Fixed columns and reports, as the sheet was designed. | Reports, alerts, printing and WhatsApp built around how you work. |
| Backups and security are your own responsibility. | A secured server with automatic backups and a log of who changed what and when. |

### Extra features in the app

- A login for each user with permissions, and a log of who changed what and when
- Automatic reminders and alerts by WhatsApp and email
- Photos, files and documents kept with each record
- Instant search and filters, with reports exported to PDF and Excel
- Works from a phone anywhere, with automatic backups

[Ask for your custom system](https://softwiro.com/contact)

## Questions this raises

### How does the sheet work out the due date and the fine?

Due date = loan date + the loan period for the member's type. Days late = (return date, or today if not returned) − due date, never below zero. Fine = days late × the daily fine, capped at the maximum you type in Settings.

### What do I do when a borrowed book is lost?

Type the date it was reported in the return date of its loan row so it no longer shows as out, then record it in Lost and damaged as «lost» with the compensation due. The copy leaves the available count.

### How do I stop a member borrowing more than their share?

Type the maximum for each member type in Settings. A loan that takes the member over it shows a «over book limit» warning and the member's status becomes «over limit». The warning draws your attention; it does not block typing.

### Can I use it for a library that charges no fines?

Yes. Set the daily fine to zero and overdue loans and late days still show with no fines.

## Have a system to build?

Softwiro designs, builds, and operates production systems. Describe what you need and we will come back with a scope rather than a slogan.

Start a project

[All writing](https://softwiro.com/blog.html)
