Free Lease Amortization Schedule Template (Build Your Own)

If you've been asked to build a lease amortization schedule in Excel, the good news is that it's simpler than it looks. A handful of columns, three core formulas, and a discount rate are all you need to turn a lease's payment terms into a period-by-period breakdown of interest, principal, and the closing liability balance. That schedule is the engine behind everything else in ASC 842 accounting: the journal entries, the disclosure tables, and the balance you report each month.

This guide gives you the exact column structure and formulas to build your own schedule from scratch, plus a worked example you can use to check your spreadsheet is calculating correctly. There's no download link here, we're not going to hand you a mystery file full of hidden formulas you have to reverse-engineer. Instead, you get everything you need to build it yourself, understand every cell, and trust the output.

• A lease amortization schedule tracks the opening balance, interest, payment, principal, and closing balance for every period of a lease.
• The periodic discount rate is simply the annual rate divided by the number of payments per year.
• Interest each period is the opening balance multiplied by the periodic rate; principal is whatever's left of the payment after interest.
• Excel has no single lease-specific function, but PV, PMT, IPMT and PPMT do all the heavy lifting between them.
• A correct schedule reconciles to exactly zero (or a known residual) by the final period; if it doesn't, there's a rounding or rate-timing error somewhere.

What a lease amortization schedule actually does

Under lease accounting, you don't expense a lease payment as it happens. Instead, you record a lease liability up front, worth the present value of every future payment, and a matching right-of-use asset. Each period, that liability changes in two ways: it grows a little from interest (or accretion, if you want the technical term), and it shrinks by the principal portion of whatever you paid that period.

An amortization schedule is just a table that tracks those two movements, payment by payment, from the start of the lease to the end. By the final period, assuming nothing has changed mid-lease, the liability balance should land on exactly zero. That's what makes it useful as a check: if your spreadsheet doesn't zero out, you've got a formula or rate error hiding somewhere in the middle.

It also feeds almost everything else you need to report. The interest and principal split gives you your journal entries. The full run of closing balances gives you your maturity analysis for disclosures. Get the schedule right and the rest mostly falls into place.

The exact columns your spreadsheet needs

Set up one row per payment period (monthly, quarterly, or annual, depending on your lease) and one column for each of the following. Keep the labels close to these so anyone else who opens the file, an auditor, a colleague, your future self, can follow it without asking.

• Period: the period number, from 1 to the total number of payments.
• Opening balance: the lease liability at the start of the period, equal to the prior period's closing balance.
• Interest (or accretion): the finance cost for the period.
• Payment: the cash paid during the period.
• Principal: the portion of the payment that reduces the liability.
• Closing balance: the liability carried into the next period.

If you're accounting for a finance lease, add a second block of columns for the right-of-use asset: opening asset value, straight-line amortization for the period (initial asset value divided by the number of periods), and closing asset value. For an operating lease, you only need one extra column: straight-line lease cost, which stays flat every period, with a ROU amortization column calculated as that straight-line cost minus the period's interest. That distinction trips a lot of people up, and if you're not sure which one applies to your lease, our guide on distinguishing an operating lease from a finance lease is a good place to start.

Whatever rate you use has to be defensible too. If the lease doesn't state an implicit rate, you'll need your entity's incremental borrowing rate for a lease of similar term and security, and it's worth keeping a note of how you arrived at it, that's one of the first things an auditor asks about.

The formulas, step by step

Three formulas do essentially all the work. Once you have them set up correctly in one row, you copy them down for every remaining period.

• Periodic rate = annual discount rate ÷ payments per year. A 6% annual rate paid monthly becomes a 0.5% monthly rate.
• Present value (to size the initial liability) = payment × [1 − (1 + r)^−n] ÷ r, where r is the periodic rate and n is the number of periods. In Excel this is `=PV(rate, nper, -payment)`. If payments fall in advance rather than in arrears, common for real estate, multiply the result by (1 + r).
• Interest for the period = opening balance × periodic rate. In Excel: `=B2*$rate` (with the opening balance in column B and the rate in a fixed cell).
• Principal for the period = payment − interest.
• Closing balance = opening balance − principal.

Excel's built-in IPMT and PPMT functions can shortcut the interest and principal formulas directly from the original loan terms, without you needing to build the running balance manually. That's a useful sanity check, though most people find a row-by-row schedule easier to audit and adjust later, especially once a lease gets modified mid-term. The important thing is that every row obeys the same logic: this period's opening balance is always last period's closing balance, and the whole thing should tie out to zero, or a known residual value, on the final row. For more on the present value side of this, see our breakdown of how the present value of lease payments is actually calculated.

Worked example: a six-month equipment lease

Here's a simple, illustrative example you can use to sanity-check your own spreadsheet. Say you've leased a piece of equipment for six months, paying $2,000 at the end of each month, with a 6% annual discount rate (so a 0.5% monthly periodic rate). The present value of those six payments, and therefore the opening lease liability, comes to $11,792.77.

Run that through the formulas above and you get the schedule in the table below. Notice that the interest portion shrinks every period while the principal portion grows, even though the payment stays flat at $2,000, that's a normal feature of any amortizing schedule, not a bug. The final payment is one cent lower than the rest, at $1,999.99, simply to absorb the last sliver of rounding and bring the closing balance to exactly zero.

If your own numbers don't reconcile this cleanly, check three things first: that you're using the periodic rate, not the annual rate, in every row; that interest is always calculated on the opening balance rather than the closing balance; and that you haven't mixed up payments in advance with payments in arrears.

Operating vs finance lease: what changes in the schedule

The liability side of the schedule, opening balance, interest, payment, principal, closing balance, is identical whether you're accounting for an operating lease or a finance lease. What differs is the asset side and, for operating leases, the income statement treatment.

For a finance lease, the right-of-use asset amortizes on a straight-line basis, separately from the liability, so interest expense and amortization expense are reported separately and front-loaded, meaning total expense is higher in early periods and lower in later ones. For an operating lease, US GAAP requires a single, flat lease cost each period, so the ROU amortization line becomes a plug figure: straight-line cost minus that period's interest, which starts small and grows over the lease term. It's a subtle distinction, but it changes both your journal entries and how the expense looks on the income statement.

Where hand-built spreadsheets break down at scale

For a single lease, a spreadsheet built this way works fine. The trouble starts when you have more than one.

• No audit trail when a discount rate or a payment changes mid-lease. A modification means re-measuring the liability at a new rate from that point forward, and it's easy to lose track of exactly what changed and when if the only record is an edited cell.
• Formulas silently drift. Someone inserts or deletes a row, a reference shifts, and the schedule keeps calculating, just wrong, until someone notices the balance doesn't reconcile.
• No single source of truth. The same lease population needs to produce journal entries, a disclosure maturity analysis, and an amortization schedule that all reconcile to the same numbers. Once those live in separate tabs, or separate files, they drift apart.
• No easy way to keep dozens or hundreds of leases in sync. One lease is a spreadsheet problem. Fifty leases, each with its own rate, term, and modification history, is closer to a database problem, and disclosure requirements only get more detailed from there, as our guide to ASC 842 disclosure requirements sets out.

Build it yourself, or get it instantly

Everything above is enough to build a correct lease amortization schedule from a blank sheet: the columns, the formulas, and a worked example to check your arithmetic against. For a single, straightforward lease, that's a perfectly reasonable way to spend twenty minutes.

If you'd rather skip the formula-debugging and get the same schedule instantly, with interest, principal, and closing balance already reconciled and ready to check against your own numbers, Ledgerage's free calculator builds it for you: one lease, no sign-up, no cost. It's the same logic laid out in this guide, just without the risk of a stray cell reference throwing your whole schedule off by a few dollars in period forty.

Frequently asked questions

Sources

• Microsoft Support: PV function
• Microsoft Support: IPMT function
• FASB Accounting Standards Update 2016-02, Leases (Topic 842)