How to Create a Schedule of Capital Lease Payments

Using Microsoft Excel is one of the best ways to calculate lease payments.

People come across variations of a capital lease all the time, in their car loan or mortgage agreement, but often are unaware of how much money per month actually goes to principal payments as compared to interest payments. A capital lease payment schedule solves that.


To be more specific, according to BusinessDictionary a capital lease is a " that is similar to a loan agreement for purchase of a capital asset on installments...Capital leases are regarded as essentially equivalent to a sale by the lessor, and a purchase by the lessee." Microsoft Excel can create a beautiful capital lease schedule for you in minutes.

Step 1

Open Microsoft Excel or other spreadsheet software

Step 2

Go to 'File' and click new.


Step 3

Click on 'Installed Templates'.

Step 4

Look for and select the 'Loan Amortization' template and click create.

Step 5

A capital lease is an excellent way for a business to acquire equipment without paying the entire up-front cost.

Enter the value of the asset being leased in the 'Loan amount' cell.


Step 6

Enter the annual interest rate in the respective cell.

Step 7

Enter the number of years for the lease in the 'Loan period in years'.

Step 8

Enter the number of payments per year in the the respective cell followed by the start date of the loan.

Step 9

Test for accuracy. For a piece of machinery that costs $500,000 that will be purchased with a 10-year lease agreement an interest rate of 10% per annum, and one payment per year, the total annual payment should be equal to $81,372.70.


While the amortization template in Excel is beautiful, there are several amortization calculators online that can also be used.


One of the most common areas that is overlooked is the number of payments per year. If its monthly, put in 12 or semi-annual, put in 2. This difference can easily throw out the formula.