How to Create Loan and Lease Statements in Excel

Whether you’re tracking a business loan or managing a lease agreement, keeping a clear, structured financial statement isn’t just good practice – it’s essential. Excel gives you the tools to build these statements from scratch, automate the math, and keep your data clean and error-free. This post walks you through exactly how to do that: what loan and lease statements are, how to build a full repayment schedule using Excel’s PMT function, and how to use a smart logical technique to stop negative values from contaminating your data.

Understanding loan and lease statements 🔗

Before jumping into Excel, it helps to be clear on what these two financial instruments actually are – because while they’re often used together in business contexts, they work quite differently.

A loan is a borrowing arrangement where a lender provides a sum of money to a borrower, who then repays it with interest over an agreed period. When you take out a loan, you own the asset from day one, but carry the debt as a liability on your books until it’s fully repaid. Common examples include business equipment loans, vehicle loans, and home mortgages.

A lease, on the other hand, is a contract where the asset owner (the lessor) allows another party (the lessee) to use an asset for a fixed period in return for periodic payments. A loan gives the borrower ownership of the purchased asset, whereas a lease gives only the right to use the equipment. At the end of a lease term, the lessee may have an option to purchase the asset, return it, or renew the lease.

Key components of a loan or lease statement 🔗

Regardless of whether you’re building a loan or lease statement in Excel, the core components remain largely the same. Every statement tracks these elements across each payment period:

• Principal (opening balance): The total amount borrowed or the value of the leased asset being financed.
• Interest rate: The cost of borrowing, usually expressed as an annual rate that is then converted to a per-period rate for calculations.
• Installment (periodic payment): The fixed amount paid each period, which includes both an interest component and a principal repayment component.
• Interest portion: The part of the installment that covers the cost of borrowing for that period.
• Principal repayment: The portion of the installment that reduces the outstanding balance.
• Closing balance: The remaining balance after each payment, which becomes the opening balance for the next period.

Understanding this split is fundamental: interest does not reduce the loan principal – only the principal repayment portion does. Early in a loan term, a larger share of each payment goes toward interest. Over time, as the outstanding balance falls, less interest accrues and more of each payment chips away at the principal. This is the classic amortization pattern.

Creating a loan repayment schedule in Excel 🔗

The most efficient way to calculate periodic installments in Excel is with the PMT function. It is one of Excel’s core financial functions, designed to calculate the fixed periodic payment for a loan based on a constant interest rate and a set number of periods.

The PMT function syntax 🔗

The PMT function calculates the total payment – principal and interest – required to settle a loan with a fixed interest rate over a specific time period. Its syntax is:

=PMT(rate, nper, pv, [fv], [type])

• rate: The interest rate per period. If your annual rate is 12% and payments are monthly, use 12%/12 = 1% per month.
• nper: Total number of payment periods. For a 3-year monthly loan, this is 3×12 = 36.
• pv: Present value – the loan amount (principal). This is typically entered as a negative number since it represents money owed.
• fv (optional): Future value after the last payment. For a fully repaid loan, this is 0 (the default).
• type (optional): Whether payments fall at the end (0, default) or beginning (1) of each period.

A critical rule: always keep your rate and nper units consistent. If you’re making monthly payments on a loan with an annual rate, divide the rate by 12 and multiply the years by 12. Mismatched units are one of the most common errors in loan calculations.

Setting up the input section 🔗

Start by creating a clean input area at the top of your spreadsheet. Place the following values in dedicated, labeled cells – this makes your formulas flexible and easy to update:

• Loan Amount (Principal): e.g., ₹1,00,000
• Annual Interest Rate: e.g., 12%
• Loan Term (Years): e.g., 2
• Payment Frequency: Monthly

Then calculate your installment using the PMT function. If your principal is in cell B1, annual rate in B2, and term (years) in B3, your formula looks like:

=PMT(B2/12, B3*12, -B1)

The negative sign before the principal makes the returned installment value positive, which is easier to read in a repayment table. Without the negative sign, PMT returns a negative value because the payment represents money going out of your account.

Building the amortization table row by row 🔗

Once you have the installment amount, build a table with one row per payment period. Here is the structure and the formula logic for each column:

Column A – Period: Number each row from 1 to the total number of periods (e.g., 1 to 24 for a 2-year monthly loan).

Column B – Opening Balance: For Period 1, this equals the original loan amount. From Period 2 onward, it equals the closing balance of the previous period: =E2 (where E is the closing balance column).

Column C – Installment: This is your fixed PMT result. Lock the reference to the PMT cell using an absolute reference (e.g., =$G$1) so it doesn’t shift when you copy the formula down.

Column D – Interest: Multiply the opening balance by the periodic interest rate:
=B2*(AnnualRate/12)
For a ₹1,00,000 loan at a 12% annual rate, the monthly rate is 1%, so the first month’s interest is ₹1,000.

Column E – Principal Repayment: Subtract the interest from the installment:
=C2-D2
This is how much of the payment actually reduces the balance.

Column F – Closing Balance: Subtract the principal repayment from the opening balance:
=B2-E2
This becomes the opening balance for the next row.

Copy all formulas from row 2 down through all your periods. As the periods progress, the principal repayment portion increases while the interest portion decreases – and at the final period, the closing balance should reach exactly zero.

How lease statements differ in practice 🔗

A lease statement follows the same basic structure as a loan repayment schedule. The key difference lies in what is being tracked. In a lease, the periodic payment is determined by the cost of the asset, the lease term, a residual (or salvage) value at the end, and the interest rate agreed upon between the lessor and lessee.

For a finance lease, the PMT function can still be used to calculate the periodic payment. The difference is that the fv argument becomes relevant – you input the residual value of the asset (what it’s worth at the end of the lease) as the future value, so the payments only cover the depreciated portion of the asset’s cost plus interest.

For example, if a vehicle worth ₹5,00,000 has an expected residual value of ₹1,00,000 at the end of a 3-year lease at 10% annual interest with monthly payments:

=PMT(10%/12, 36, -500000, 100000)

Here, pv is -5,00,000 (cost of the asset) and fv is 1,00,000 (what the lessee will either pay to own it or return to the lessor). In the case of lease financing, the user can claim only lease rentals as expenses, which remain uniform during the lease period – making the fixed payment structure straightforward to model in Excel.

Avoiding negative balances with garbage cleaning 🔗

Here’s a problem that catches many people off guard. In a well-constructed amortization schedule, the closing balance should reach zero at exactly the final payment period. But due to rounding in Excel’s decimal calculations, the balance can sometimes tip slightly below zero in the last row – producing a small negative number like -₹0.01 or -₹0.003. While tiny, this is technically incorrect and looks unprofessional in a business report. It can also cause downstream errors if that closing balance feeds into another formula.

The fix is a technique sometimes called “garbage cleaning” – using logical conditions to catch and correct these erroneous values before they appear in the output. The tool for this in Excel is the IF function.

How the IF function works 🔗

The IF function evaluates a condition and returns one value if the condition is true, and another if it’s false. Its syntax is:

=IF(logical_test, value_if_true, value_if_false)

Applied to garbage cleaning in a loan statement, you wrap your closing balance formula inside an IF check:

=IF((B2-E2)<0, 0, B2-E2)

This formula checks whether the computed closing balance is less than zero. If it is, it returns 0. If not, it returns the actual calculated balance. The result: your statement always shows a clean zero (or positive balance) rather than a confusing negative figure.

Extending garbage cleaning to the interest column 🔗

The same logic applies to the interest column. If the closing balance from the previous period is zero (the loan is fully paid), the interest for the next period should also be zero – not a phantom interest charge on a non-existent balance. Wrap your interest formula the same way:

=IF(B2<=0, 0, B2*(AnnualRate/12))

This checks if the opening balance is zero or below. If so, it returns 0 interest. This prevents any rows that exist beyond the loan’s actual payoff point from generating incorrect figures.

Why this matters for lease statements too 🔗

Garbage cleaning is equally important in lease schedules. Lease statements often include a residual value column, and depending on how the final payment is structured, rounding errors can produce a small negative residual. Applying IF-based conditions to the closing balance and interest columns in a lease table keeps the statement accurate and audit-ready. A clean, zero-ending balance is the expected result – and Excel’s IF function is the simplest way to enforce it.

Putting it all together: tips for a professional statement 🔗

A few practical habits will make your loan and lease statements more reliable and easier to maintain:

• Use absolute cell references ($) for fixed inputs like the interest rate and loan amount when dragging formulas down. This prevents Excel from shifting the reference incorrectly.
• Format cells appropriately – use currency formatting for monetary columns and percentage formatting for the rate. This reduces misreading of raw decimal values.
• Add a totals row at the bottom to sum the installments, total interest paid, and total principal repaid. The total interest should equal the difference between total payments and the original loan amount.
• Verify your work: To confirm the total repaid amount, multiply the PMT result by the number of periods (nper). That figure, minus the original principal, gives you the total interest cost.
• Apply garbage cleaning (IF conditions) consistently to both the interest and closing balance columns across every row – not just the last one. This makes the sheet resilient even if loan terms are later adjusted.

For variable rate loans, PMT cannot be used directly – you would need to recalculate the payment each time the rate changes. For standard fixed-rate business loans and finance leases, however, a single PMT formula at the top of your sheet drives the entire schedule automatically.

A complete workflow summary 🔗

To recap the full process from start to finish: first, set up a dedicated input section with your principal, annual interest rate, and loan or lease term. Second, use the PMT function – adjusting the rate and nper for your payment frequency – to calculate the fixed periodic installment. Third, build an amortization table with columns for opening balance, installment, interest, principal repayment, and closing balance, using the formulas described above. Fourth, wrap your closing balance and interest formulas inside IF conditions to apply garbage cleaning and eliminate any rounding-induced negative values. Finally, format the sheet cleanly, add a totals row, and verify the final closing balance reaches zero.

This workflow applies whether you’re preparing a loan repayment schedule for a business equipment purchase, a vehicle lease for your company’s fleet, or a property finance statement. The underlying Excel logic is the same – only the inputs change.

What do you think? If you had to choose between building a loan repayment schedule manually row by row versus using Excel’s PMT function with automated formulas, what would you prefer and why? And have you ever encountered a case where rounding errors caused problems in a financial spreadsheet – how did you handle it?

References

1. https://noreastcapital.com/difference-between-a-capital-lease-and-a-loan/
2. https://www.educba.com/loan-vs-lease/
3. https://janzednicek.cz/en/excel-pmt-function-loan-payment-and-amortization-schedule/
4. https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
5. https://corporatefinanceinstitute.com/resources/excel/pmt-function/
6. https://exceljet.net/functions/pmt-function
7. https://www.contextures.com/excelpmtfunction.html
8. https://www.excel-easy.com/examples/loan-amortization-schedule.html
9. https://www.wallstreetmojo.com/finance-vs-lease/
10. https://www.wallstreetprep.com/knowledge/pmt-function/
11. https://www.datacamp.com/tutorial/pmt-function-in-excel