📌 Stage 3 · Costs, CapEx, and the Loan · CapEx & Loan · amortization schedule · 40–50 min

Thirty-six level payments of $559.91, interest on the prior balance

Learning goals

  • Read the loan terms from the parameter block
  • Build one amortization row: interest, principal, ending balance
  • Chain the payment dates without typing them
  • Explain why the final balance is not exactly zero

Concepts

The loan terms

The February build-out is funded by an $18,000.00 equipment loan at 7.5% APR over 36 months, with the lender-quoted payment of $559.91 a month. Those four numbers sit in the parameter block: C12 amount, C13 APR, C14 term, C15 payment. Every schedule formula reads only these cells, so a refinance is a four-cell edit.

The loan proceeds land in February (Cash Flow row 7) and the first payment is due Mar 2, 2026.

Interest first, principal second

Each payment splits into interest on the balance owed and principal that reduces it:

=ROUND($C$12*$C$13/12,2)

CapEx & Loan E17 (first payment's interest): $18,000.00 × 7.5% ÷ 12 = $112.50. Payment 2 switches the input to the prior ending balance: =ROUND($G17*$C$13/12,2) = $109.70.

=$D17-$E17

CapEx & Loan F17 (principal): the $559.91 payment minus $112.50 of interest = $447.41 of principal in payment 1.

=$C$12-$F17

CapEx & Loan G17 (ending balance): $18,000.00 − $447.41 = $17,552.59. Each later row reads the prior row's balance: =$G17-$F18.

💡 Tips:

  • Early payments are mostly interest, later ones mostly principal — that is the shape of every level-payment loan, and it is why refinancing decisions belong early in the term.

The date chain

Only the first payment date is typed; the rest follow it:

=EOMONTH(C17,0)+2

CapEx & Loan C18: the last day of the prior payment's month, plus 2 days — the 2nd of this month. The chain runs all 36 payments from Mar 2, 2026 to Feb 2, 2029 without a single typed date.

=$C$15

CapEx & Loan D17: the level payment pulled from the parameter block ($559.91), identical on every row.

Reading the schedule

Thirty-six payments × $559.91 is $20,156.76 against $18,000.00 borrowed — $2,156.76 of interest over the life of the loan. In 2026 the schedule shows ten payments (March through December) totaling $5,599.10: $997.03 of interest (Cash Flow row 17 feeds the P&L interest line) and $4,602.07 of principal.

The final ending balance is $0.06, not zero: the payment is rounded to cents and the owner absorbs the rounding residue on the last payment. That six-cent trace is the signature of a correctly built schedule — and a reminder that every rounded cell compounds.

Practice

Fixed case: Harbor Creative Studio LLC, a four-person design studio in Austin, Texas, planning calendar year 2026. Cached numbers are the Base scenario. Pale-green cells are formulas; white cells are typed inputs. The exercise opens on the sheet this lesson teaches. Cell E17 (interest on payment no. 1) is blank: retype the first-payment interest formula.

  1. Open CapEx & Loan and find the parameter block — The exercise opens on CapEx & Loan. Rows 12 to 15 hold the loan terms: $18,000.00, 7.5% APR, 36 months, $559.91 payment. Row 16 is the schedule header; payments run rows 17 to 52.
  2. Retype the first interest cell in E17 — Cell E17 is empty. Type =ROUND($C$12*$C$13/12,2) and confirm: it should return $112.50. The next row's E18 should read $109.70 (=ROUND($G17*$C$13/12,2)).
  3. Check principal and balance — F17 should read $447.41 and G17 should read $17,552.59. Payment dates: C17 is the typed Mar 2, 2026; C18 is the formula =EOMONTH(C17,0)+2 returning Apr 2, 2026.
  4. Read the tail of the schedule — Scroll to row 52 (payment no. 36): interest $3.48, principal $556.43, and ending balance $0.06 — the rounded residue. D52 still reads $559.91.
  5. Follow the loan into the plan — Monthly P&L row 26 (Interest expense) should show $0.00 in January and February and $112.50 in March, with O26 at $997.03. Cash Flow row 17 shows $559.91 from March through December, O17 $5,599.10.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Retyped the interest formula in E17 and it returned $112.50; G17 reads $17,552.59
  • ☐ The date chain returns Apr 2, 2026 for payment 2 without a typed date
  • ☐ Final balance reads $0.06 and FY interest reads $997.03 (ten payments, March to December)
  • ☐ Can explain why early payments are mostly interest