Loan amortisation schedules
Originally published: 20/04/2021 07:32
Last version published: 21/04/2021 05:56
Publication number: ELQ-31976-3
View all versions & Certificate
certified

Loan amortisation schedules

Loan amortisation schedule in Microsoft Excel + PDF with explanations.

Description
Do you need to check your loan repayments, depict a loan in a financial model or calculate your IFRS16 balances? Learn how to develop flexible amortisation schedules for loans and financial leases with simple inputs and automated calculations in Excel. You can also download a file with four versions for free!

The download contains two files
1) A PDF explains how to calculate a loan amortisation schedule
2) An Excel file contains four versions of the schedule ready to use.

When do I need this?
When you need to plan an annuity loan with regular (usually monthly) payments, perhaps as part of a financial model. Such a schedule can also be used to calculate liability values for finance leases / IFRS16. It can also be used to compare various financing alternatives with different terms and different interest rates.

Or maybe you just want to get a better understanding of that mortgage payment plan your bank is sending you. 😊

How can I use one of the loan schedules in the download file?
You can use any one of them ‘as is’ by simply amending the assumptions (grey input cells at the top). Alternatively, you can incorporate one into an existing model. In this case, copy the relevant calculation into your model, then link the interest costs to your profit and loss account (income statement) and the closing balances to your balance sheet. For a cash planning tool, link the total installments as cash outflows to your cash flow plan.

(Note: stock image is from depositphotos (c) marketing.lasers@ya.ru)

This Best Practice includes
1 PDF with theory and explanations, 1 Excel file with 4 ready-to-use schedules

Gary Knott offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss

Further information

Do you need to check your loan repayments, depict a loan in a financial model or calculate your IFRS16 balances? Learn how to develop flexible amortisation schedules for loans and financial leases with simple inputs and automated calculations in Excel. You can also download a file with four versions for free!

When users have intermediate Excel and some financial knowledge

Users with little or no Excel or financial knowledge


5.0 / 5 (3 votes)

please wait...