How to Create Detailed Loan and Lease Statements in Excel

Every EMI you pay on a car loan or every monthly rent you clear on a leased office follows the same mathematical backbone: a fixed payment split between interest and principal, recalculated period after period until the balance hits zero. A loan and lease statement is simply the document that lays this out row by row, and Excel happens to be the tool most commerce students and small business owners reach for to build one. This post walks through how these statements are structured, which formulas do the heavy lifting, and where loan and lease statements quietly differ from each other.

What a loan and lease statement actually shows 🔗

A loan or lease statement is a running record of an obligation over time. It tells you, for every payment period, how much was owed at the start, how much of the payment went toward interest, how much reduced the principal, and what remains outstanding. Banks issue these to borrowers, NBFCs issue them to vehicle or equipment financiers, and companies prepare them internally for leased assets under accounting standards. The statement matters because interest and principal are not fixed proportions of each payment. In the early periods, a larger share of your instalment goes toward interest since the outstanding balance is high. As the balance shrinks, more of each payment chips away at the principal.

Why Excel fits this job so well 🔗

Building a loan or lease statement by hand for a 60-month car loan means doing the same calculation 60 times, with each row depending on the one before it. Excel removes this repetition. Once you set up the first row correctly and use absolute and relative referencing, you can drag the formula down to generate the entire schedule in seconds. This is precisely why spreadsheet literacy is treated as a core skill in business computing courses, not just a convenience.

Setting up the basic structure 🔗

A typical loan or lease statement in Excel uses five columns, and getting these right before you touch any formula saves a lot of rework later.

Above this table, it helps to keep a small input block with the loan amount, annual interest rate, tenure, and payment frequency. Every formula in the statement should refer back to these cells so that changing one input recalculates the entire schedule automatically.

The PMT function: calculating the fixed installment 🔗

The core of any loan or lease statement is the instalment amount, and Excel calculates this using the PMT function. According to Microsoft’s own documentation, the function returns a payment that includes principal and interest only, not taxes or other charges, and its syntax is written as PMT(rate, nper, pv, [fv], [type]).

Understanding each argument 🔗

Rate is the interest rate per payment period, not the annual rate. Nper is the total number of payments over the loan’s life. Pv is the present value, meaning the loan amount itself. Fv and type are optional and can usually be left out for a standard loan that fully pays off by the end of the term. The detail that trips up most students is unit consistency: since interest rates are quoted annually, a monthly repayment schedule requires dividing the annual rate by 12 and multiplying the number of years by 12 to get the correct number of periods.

A worked example 🔗

Say a business takes a loan of ₹5,00,000 at 9% annual interest, repayable over 5 years in monthly instalments. The formula would read =PMT(9%/12, 5*12, -500000). Entering the loan amount as a negative value, or placing a minus sign before the entire formula, ensures Excel returns a positive instalment figure instead of a negative one, since PMT treats the loan disbursed as a cash outflow from the lender’s perspective by default.

Breaking the installment into interest and principal 🔗

Once the fixed instalment is known, the statement needs to split it into interest and principal for each period. Excel provides two companion functions for this. IPMT calculates the interest portion for a given period, and PPMT calculates the principal portion. Both follow a similar syntax to PMT but add a period number as the second argument. As one Excel tutorial on amortization schedules demonstrates, once you drag these formulas down the full tenure, the interest column shrinks period after period while the principal column grows, until the closing balance reaches exactly zero on the final row.

In practice, many spreadsheet builders skip IPMT and PPMT and calculate interest manually as opening balance multiplied by the periodic rate, then derive principal repaid as the instalment minus that interest. This approach is easier to audit and explains the mechanics more transparently to someone learning the concept for the first time, which is why it is commonly taught in business computing units before introducing the dedicated functions.

Loan statements versus lease statements 🔗

Loan and lease statements look nearly identical in their column structure, but what they represent differs. A loan statement tracks repayment of borrowed money where ownership of the underlying asset was never in question. A lease statement, especially under Indian accounting rules, tracks a company’s obligation to pay for the right to use an asset it does not own outright.

Under Ind AS 116, the Indian equivalent of IFRS 16, most leases are brought onto the balance sheet as a right-of-use asset paired with a lease liability. The lease liability is reduced by the principal portion of each lease payment until it reaches nil by the end of the lease term, mirroring exactly the mechanics of a loan amortization schedule. This is precisely why the same PMT-IPMT-PPMT approach used for loans works equally well for building lease statements in Excel, once the lease liability is substituted for the loan principal.

Flat rate versus reducing balance: a detail Indian students must get right 🔗

One place where loan statements can go wrong is confusing flat rate interest with reducing balance interest. Excel’s PMT, IPMT, and PPMT functions all assume a reducing balance method, where interest for each period is calculated only on the outstanding principal. This is the method banks in India typically use for home loans, vehicle loans, and most formal lending, because it is considered more transparent and easier to compare across lenders, as explained by SMFG India Credit’s overview of interest calculation methods.

A flat rate loan, by contrast, charges interest on the original principal for the entire tenure regardless of how much has already been repaid, which makes the effective interest rate significantly higher than the quoted flat rate. If you are building a loan statement for a flat rate product, using PMT directly will give you the wrong instalment figure, and you will need to calculate the flat interest separately before adding it to the principal component.

Common mistakes to avoid 🔗

A few errors show up repeatedly in student spreadsheets. Forgetting to convert the annual rate to a periodic rate is the most frequent one, since a rate left as an annual percentage inflates the calculated instalment enormously. Mixing up the sign convention for the loan amount is another, since PMT returns a negative number unless the present value is entered as negative. Finally, letting the closing balance drift slightly away from zero on the last row, usually due to rounding, is a sign that the opening balance or period count somewhere in the schedule needs rechecking.

Putting it all together 🔗

A well-built loan or lease statement is really just five interconnected columns and three formulas working in sequence. Once the structure is right, the same template can be reused for any loan amount, rate, or tenure by changing only the input cells at the top, and with a small adjustment, the identical logic extends to lease liabilities under Ind AS 116 as well.

What do you think? If you had to build a loan statement for a flat rate personal loan instead of a reducing balance one, how would you adapt the PMT-based structure covered here? And where do you think rounding errors are most likely to creep into a long-tenure schedule?

References

1. https://wus.prod.support.services.microsoft.com/en-gb/office/pmt-function-f30c80b4-7710-4959-b10b-498c3a5a8a55
2. https://corporatefinanceinstitute.com/resources/commercial-lending/amortization-calculator-excel/
3. https://www.exceltip.com/excel-financial-formulas/create-an-amortization-schedule-in-microsoft-excel.html
4. https://vinodkothari.com/2025/03/rou-ready-quick-guide-to-lease-accounting-from-lessee-perspective/
5. https://www.smfgindiacredit.com/knowledge-center/difference-between-reducing-balance-and-flat-rate-interest.aspx