Overview

Financial Schedules- Amortization Schedule

An amortization schedule is a table detailing each periodic payment on an amortizing loan, as generated by an amortization calculator. Amortization refers to the process of paying off a debt over time through regular payments.

https://en.wikipedia.org/wiki/Amortization_schedule

Simple Amortization Schedule

I tried to use a simple loan to demonstrate the basic nuances in refer to creating amortization schedule.

I have used some icons and pictures from different sources which have been written on template.

Some Excel functions including EDate, PMT have been used in this template.

This template is consisted of two parts:

1 – Data Entry Area

2 – Result Area

In Data Entry Area, Purchase Price, Down Payment, Annual Interest Rate, Term and Loan Date information subject to changes. These areas are shaded.

The difference between Purchase Price and Down Payment is a payment which is used in Amortization Schedule and can be find on Amount Financed cell of Data Entry Area.

PMT function gives us Monthly Payment.

Rate is given on yearly basis. We divided it by 12 to find on monthly basis.

Term also is given in years and multiplied by 12 to find in months.

I have used Sequence Function to find the number of terms in months under Payment Number Column.

I used EDate Function to find Payment Date in every next months which is shown under Date Column.

Users can see Monthly Payment under Payment Column which is extracted from Data Entry Area.

Balance*Interest_Rate/12 give us Interest Amount which can be found under Interest Column.

The difference between Payment and Interest gives us Principal Amount.

Last but not least, Users can find Balance Amount under Balance Column.

This Schedule is Static. It has been prepared for one time.

Users are required to make changes at each change on Data Entry Area.

In my next templates, I will prepare Dynamic Amortization Template which will be subject to dynamic changes.

No reviews yet

## More From Ilgar Zarbaliyev

Browse our top rated business templates. See All
Row, Column, Index, Match, Offset Functions. Excel Tutorial
1,947
224
“It's Excel's world, we just live in it.” “To be an accountant in the age of spreadsheet program is —…
New Dynamic Functions. Excel Tutorial
1,782
199
In September, 2018, we released dynamic array formulas for Excel for Microsoft 365. The differences between dynamic arrays and legacy…
Fill Series. Data Validation. Excel Tutorial
1,807
189
If you have inherited workbooks from someone else or if you have imported data from external data sources, you have…
HR INSIGHTS DASHBOARD
4,702
130
This report includes the necessary analyzes according to some characteristics of the people who made and participated in the job…
Supplier_Insights
4,826
113
Hello, This report is consisted of four pages. 1-Supplier Quality Metrics 2-Supplier Quality Regional 3-Top - Bottom Analysis 4-Downtime Analysis…
Sales_Analysis_Reportin Power BI
5,222
181
Hello, After participating in Enterprise DNA programs, I prepared reports on three topics in Power BI. The first report I…
See All