All Industries, General Excel Financial Models

Debt Amortization, Debt Schedule, Excel, Interest Accrual, Loans

This template will help you calculate loan and interest balances, accrued interest at any point in time on loans granted or issued.

I have tried to make this tool as flexible as possible. As such, it allows you to:

– Choose the calculation period (1, 3, etc. month). This is the period for which the calculations are performed and accrued interest amounts and loan and interest balances are provided.
– Set the interest period (1 month, 3 months, etc.). This is the period at the end of which accrued interest is treated in some way (see below)
– Specify interest treatment at the end of each interest period (repay, capitalize or include into accumulated interest balance)
– Select the day-count method (30/360, actual/actual, actual/365, etc.)
– Set varying interest rates for different periods
– Include several loan drawdowns at different dates
– Implement various repayment schedules (regular repayments for the same amount or a customized schedule with different amounts and dates)
– Include accrued interest into the repayment amount or make interest repayments on top of it
– Provide an interest-free period, skip principal and interest repayments for a certain period at the beginning

The template includes a summary grouping the amounts by larger intervals (annual, quarterly) and indicating:

– Opening and closing principal and interest balances
– Accrued and capitalized/repaid interest during the period
– Principal drawn and repaid during the period

The template can be used on a stand-alone basis (to perform just loan calculations) or as part of a larger budgeting package. It can also be incorporated into investment analysis models. Finally, you can use it as a learning tool to understand real-life loan calculations.

The publication includes an Excel file and a text guide with explanations.

You must log in to submit a review.