Debt

Debt payoff spreadsheet template with month-by-month balances

Build a debt payoff spreadsheet with month-by-month balances: the columns, the formulas for interest, payment and balance, and an example of three debts.

Editorial illustration for Debt payoff spreadsheet template with month-by-month balances

A debt payoff sheet needs five columns per debt: month, opening balance, interest, payment and closing balance. Interest is the opening balance times the yearly rate divided by twelve, and the closing balance is opening plus interest minus payment. These formulas work the same in Excel and Google Sheets.

Set up the inputs

Make one tab per debt. In that tab, put the inputs where you can change them without touching formulas.

  • H1: starting balance, for example 30000.
  • H2: yearly interest rate, typed as a percentage, for example 18%.
  • H3: monthly payment, for example 3000.

What are the formulas for each row?

Row 1 holds the headings: Month, Opening, Interest, Payment, Closing. Row 2 is the first month. Type these in row 2 and fill down for as many months as you need, say to row 60.

  • A2: 1, and A3: =A2+1 for the following months.
  • B2: =H1 (opening balance of month one).
  • C2: =ROUND(B2*$H$2/12,0)
  • D2: =MIN($H$3,B2+C2)
  • E2: =B2+C2-D2
  • B3: =E2, and copy the rest of row 3 down.

The MIN in the payment column stops you paying more than the debt needs, so the last payment is smaller and the balance never goes negative. Once the balance reaches zero, the sheet shows zeros.

A filled example

Say the first debt is ₹30,000 at 18 percent with a ₹3,000 payment. All numbers are invented. Here are the first six months.

MonthOpeningInterestPaymentClosing
1₹30,000₹450₹3,000₹27,450
2₹27,450₹412₹3,000₹24,862
3₹24,862₹373₹3,000₹22,235
4₹22,235₹334₹3,000₹19,569
5₹19,569₹294₹3,000₹16,863
6₹16,863₹253₹3,000₹14,116

How do I combine three debts?

Add a Summary tab with a month column and one column per debt that points to each tab's closing balance, then a total. Suppose the other two debts are ₹50,000 at 12 percent with a ₹3,500 payment, and ₹80,000 at 9.5 percent with a ₹4,000 payment. Closing balances for the first six months are below.

MonthDebt 1Debt 2Debt 3Total owed
Start₹30,000₹50,000₹80,000₹1,60,000
1₹27,450₹47,000₹76,633₹1,51,083
2₹24,862₹43,970₹73,240₹1,42,072
3₹22,235₹40,910₹69,820₹1,32,965
4₹19,569₹37,819₹66,373₹1,23,761
5₹16,863₹34,697₹62,898₹1,14,458
6₹14,116₹31,544₹59,396₹1,05,056

The total row is =SUM(B2:D2) in the Summary tab. Interest charged over the six months is ₹8,056, and payments are 6 times ₹10,500, which is ₹63,000, so the debt fell by ₹54,944, matching 1,60,000 minus 1,05,056.

What totals should I add?

  • Total interest paid: =SUM(C2:C60) on a debt's tab.
  • Months to clear: =COUNTIF(E2:E60,">0")+1, valid when the schedule has at least one row of zeros after the payoff.
  • Total owed today: the Summary total for the latest month.

What mistakes should I watch for?

  • Typing the rate as 18 instead of 18%. Test the first row: ₹30,000 at 18 percent should show interest of ₹450 in month one.
  • Typing the payment into the Closing column. Closing should always be a formula.
  • Forgetting the dollar anchors in $H$2 and $H$3. Without them the references slide when you fill down and the formulas break.
  • Starting the sheet with a balance from an old statement. Use the latest balance and the latest month.

How do I add snowball or avalanche?

Add an Extra cell in the Summary tab. In the debt you are targeting, change the payment formula to =MIN($H$3+Extra,B2+C2), where Extra is that cell's address. When a debt closes, move its payment amount into the next target by hand. It keeps the formulas simple, and the manual step makes you notice each closure.

When a sheet stops being enough

A sheet is only as current as your last update. If you keep your bank and card statements in SpendixAI, you can check from the imported entries that each payment really went out on the date and for the amount your sheet assumes, then fix the sheet where it differs.

SPENDIXAI JOURNAL

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

Explore your reports