Lease Amortization Schedule in Google Sheets (Straight-Line, Lessee vs Lessor)
A lease amortization schedule is the same shape as a loan amortization schedule: each period splits a payment between interest and principal, and the balance winds down to zero by the end of the term. The difference is what the balance represents. For a lessee under current lease accounting standards, it’s the lease liability, matched by a right-of-use asset amortized on its own separate schedule. This post builds both pieces in Google Sheets, with the lessee/lessor distinction made explicit, because mixing the two up is the most common error in early drafts of this schedule.
Lessee vs Lessor: Who Is Amortizing What
| Lessee | Lessor | |
|---|---|---|
| Role | Uses the asset, makes payments | Owns the asset, receives payments |
| Balance sheet impact | Right-of-use asset + lease liability | Asset stays on lessor’s books (operating lease) or is derecognized (finance lease) |
| What gets amortized | Lease liability (interest/principal split) and right-of-use asset (straight-line, separately) | Depends on lease classification; often just recognizes rental income |
| Schedule needed | Yes, this post | Different treatment, not covered here |
This post is written from the lessee’s side, since that’s where a manually built amortization schedule earns its keep. Lessor accounting is a different set of journal entries, largely unaffected by the recent standard changes.
The Two Schedules a Lessee Needs
- Lease liability amortization schedule. Tracks the discounted present value of remaining payments, reduced each period by the principal portion of the payment.
- Right-of-use (ROU) asset amortization schedule. Usually a simple straight-line reduction of the initial ROU asset value over the lease term, independent of the liability schedule.
Building the Lease Liability Schedule
Set up one row per payment period with these columns:
| Column | Field | Notes |
|---|---|---|
| A | Period | 1, 2, 3… |
| B | Opening Liability | Prior period’s closing balance |
| C | Interest Expense | =B2*monthly_rate |
| D | Lease Payment | The fixed (or scheduled) payment for the period |
| E | Principal Reduction | =D2-C2 |
| F | Closing Liability | =B2-E2 |
Row 1’s opening liability is the present value of all remaining lease payments, discounted at the rate determined for the lease (see the FAQ below on how that rate is set). Each subsequent row’s opening balance is simply the prior row’s closing balance (=F2 becomes next period’s B3), which is what makes this a genuine amortization schedule rather than a flat table.
Building the ROU Asset Schedule
This one is simpler because it’s straight-line in most cases:
| Column | Field | Notes |
|---|---|---|
| A | Period | 1, 2, 3… |
| B | Opening ROU Asset | Prior period’s closing balance |
| C | Amortization Expense | =Initial_ROU_Asset/Total_Periods |
| D | Closing ROU Asset | =B2-C2 |
The initial ROU asset value starts equal to the initial lease liability (adjusted for any prepaid rent, initial direct costs, or lease incentives), then amortizes independently of the liability schedule above. By the end of the lease term both schedules reach zero, but they get there by different paths, interest-weighted for the liability, straight-line for the asset, which is exactly why they need to be two separate tables rather than one.
A Worked Example
A five-year office lease, annual payments of €24,000 in arrears, discount rate 6%. The present value of five €24,000 payments at 6% is roughly €101,100, which becomes both the opening lease liability and the initial ROU asset.
- Year 1 liability row: opening €101,100, interest €6,066 (6% × €101,100), payment €24,000, principal reduction €17,934, closing €83,166.
- Year 1 ROU asset row: opening €101,100, amortization €20,220 (€101,100 ÷ 5), closing €80,880.
Note the two closing balances aren’t equal after year one (€83,166 vs €80,880), and that’s expected. They only match at the very start and the very end of the lease term; the paths between are different by design.
Frequently Asked Questions
What is the difference between a lessee and a lessor? The lessee is the party using the asset and making payments, for example a company renting office space. The lessor is the party that owns the asset and receives the payments, for example the landlord. Under modern lease accounting standards (IFRS 16, ASC 842), most lessee leases go on the balance sheet as a right-of-use asset and a lease liability; lessor accounting is largely unchanged from before.
What is lease amortization? Lease amortization is the gradual reduction of the lease liability (for the lessee) as payments are made, split each period between interest expense and a reduction of principal, similar to a loan. The right-of-use asset is amortized separately, typically on a straight-line basis over the lease term.
Do I need special software to build a lease schedule? No. A lease amortization schedule is a table of periods with opening balance, interest, payment, principal reduction, and closing balance, each row computed from the row above with plain formulas. Dedicated lease accounting software adds journal entry automation and disclosure reporting, but the schedule itself is straightforward in a spreadsheet.
How is the discount rate for a lease determined? It’s the rate implicit in the lease if that can be readily determined, or the lessee’s incremental borrowing rate if not, meaning the rate the lessee would pay to borrow a similar amount, over a similar term, secured by a similar asset. In practice, most lessees use their incremental borrowing rate, since the rate implicit in the lease is rarely disclosed by the lessor.
Try Sheetminer Free
If the lease agreement itself lives as a PDF, Sheetminer pulls the payment amount, term, and start date straight into the sheet with the source page linked, so the inputs for this schedule take a minute to set up instead of a manual re-key. 100 free credits every month, no credit card required.
Install Sheetminer free →