Debt

A debt-free timeline chart in Excel: a Gantt-style loan view

Build a debt-free timeline chart in Excel: a Gantt-style stacked bar for each loan, made from a small table of start and last EMI dates.

Editorial illustration for A debt-free timeline chart in Excel: a Gantt-style loan view

A debt-free timeline in Excel is a stacked bar chart, with one bar per loan. The first part of the bar is invisible and starts the bar at the loan's start date, the second part shows time already passed and the third shows time still to go. It is built from a four-column table and updates itself every day.

What does the table need?

Put these headings in row 1, with one loan per row from row 2. The dates are invented. Column B is the start date, column E the last EMI date, and columns C and D are formulas.

A: LoanB: StartC: Elapsed daysD: Remaining daysE: Last EMI
Laptop loan1 Jan 2026formulaformula1 Dec 2026
Bike loan1 Aug 2025formulaformula1 Jul 2028
Personal loan1 Mar 2025formulaformula1 Feb 2029
Education loan1 Jul 2024formulaformula1 Jun 2031

Enter the dates as real dates, not text. Then type the two formulas in row 2 and drag them down.

  • Elapsed days (C2): =MAX(0,MIN(TODAY(),E2)-B2)
  • Remaining days (D2): =MAX(0,E2-MAX(TODAY(),B2))

These handle all three situations. If the loan has not started, elapsed is zero and remaining is the full length. If it is in progress, the two add up to the whole tenure. If it has ended, elapsed is the full length and remaining is zero.

How do you build the chart?

  1. Select A1:D5. That is the loan names, the start dates and the two formula columns.
  2. On the Insert tab, open the bar chart options and choose Stacked Bar.
  3. Click on the first series, which is the start date, and set its fill to No Fill. The bars now begin at the start dates.
  4. Click the elapsed series and give it a solid colour. Give the remaining series a lighter shade of the same colour.
  5. Right-click the vertical axis, open its options and tick the setting for categories in reverse order, so the first loan is on top.

The exact menu names vary between Excel versions. They are written here for Excel for Microsoft 365.

How do you fix the date axis?

The horizontal axis shows date serial numbers, which means the bars start far to the right and the chart looks squashed. Fix it by setting the axis bounds. Type your earliest start date into a spare cell, for example 1 Jul 2024, change the cell format to General and note the number that appears. Do the same for the latest last EMI date. Right-click the horizontal axis, open Format Axis, and type those numbers as the minimum and maximum. Then set the axis number format to month and year, such as mmm-yy, so the labels read as dates.

How do you keep it updated?

  • Change the last EMI date when a lender revises the schedule or you part-prepay and the tenure changes.
  • Add a new row when you take a new loan and extend the chart range to include it.
  • When a loan ends, leave it in place for a while. A bar that is all elapsed is a good sight.
  • Save a copy at the start of each year so you can compare the timelines.

Can you do it in Google Sheets?

A stacked bar chart works there too, using the same table. Hiding the first series is fiddlier than in Excel, so this walkthrough is written for Excel. If you only need the dates and the order, a simple sorted table does the same job.

When does a chart stop being enough?

The chart shows dates, not whether each EMI was actually paid. If you want that side, SpendixAI can import a bank statement as PDF, CSV or Excel, with a review step before saving, so each EMI debit appears as a transaction you can check against the schedule. That is optional. The chart itself needs only your four columns.

Look at the picture once a month. If a bar has not moved when you expected it to, check the loan statement.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports