IT Asset Management System in Excel: Asset Register, Assigned To, Warranty Alerts, Book Value, Repairs and Report

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: it-asset-management-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Assets, Repairs, Report, Asset card, 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

Bagale Tech, Pune, as of 17 October 2026 (made-up data). Laptop life 3 years, desktop 5, phone 2; warranty alert 60 days.

One laptop is in stock, ready for the next joiner.

2. Build it sheet by sheet

Step 1. Setup

1. B4 warranty alert days (60). Categories with useful life in A7:B11, departments in D7:D10, status in F7:F10.

Step 2. Assets: warranty

1. A5 tag ="IT-"&TEXT(ROW()-4,"000"). Type category, description, purchase date, cost, assigned to, department, status and warranty years.
2. J5 warranty ends =EDATE(D5,12*I5) (EDATE).
3. K5: =IF(J5<Setup!$B$3,"Expired",IF(J5-Setup!$B$3<=Setup!$B$4,"Ending soon","Active")), amber when ending soon.

Step 3. Assets: book value and repairs

1. L5 age =ROUND((Setup!$B$3-D5)/365,2).
2. M5 book value =ROUND(E5*MAX(0,1-L5/XLOOKUP(B5,Setup!$A$7:$A$11,Setup!$B$7:$B$11,1)),0): straight line, never below zero.
3. N5 repairs =SUMIFS(Repairs!$E$5:$E$2000,Repairs!$B$5:$B$2000,A5) from the repair log (SUMIFS).

Step 4. Report

1. By department: items, cost, book value and repairs, leaving out Retired with "<>Retired".
2. By category: items, in stock, warranty ending soon and past useful life with COUNTIFS.

Step 5. Asset card and Dashboard

1. Asset card: type a tag in B4; print it for the handover form. Set the print area.
2. KPI cards: items, cost, book value, repairs, warranty ending soon and past useful life. Charts: cost and book value by department, items by category.

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. No tag on the item. Stick the tag on the item. A register you cannot match to the desk is no use.
2. Deleting retired items. Mark them Retired. The report leaves them out but the history stays.
3. Adding warranty years by hand. EDATE(purchase, 12 × years) handles month ends and leap years.
4. Using book value for tax. This is a planning estimate. Tax depreciation follows its own rules; ask your accountant.
5. Assigned to not updated. Change it the day the item moves, with a signed asset card.

5. More real examples

Example 1: Office furniture. Chairs, desks and cupboards with a longer life and no warranty column.

Example 2: Tools and machines. Workshop equipment with service due dates, beside the manufacturing planning system.

Example 3: Software licences. Seats, renewal date and cost per user instead of book value.

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 a warranty end date in Excel?

=EDATE(purchase_date, 12*warranty_years).

How do I flag warranties ending soon?

Warranty end − today ≤ alert days shows Ending soon; before today is Expired.

How do I work out book value?

Cost × (1 − age ÷ useful life), not below zero, as in Assets M5.

How do I add repair costs per asset?

SUMIFS the repair log by asset tag.

How do I leave retired items out of totals?

Add the condition status "<>Retired" to COUNTIFS and SUMIFS.

7. Quick quiz

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