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

Dynamic Amortization Schedule

This Dynamic Amortization Schedule is built on the Static Amortization Schedule example.

Two columns (Annual Interest Rate and Additional Payment) have been added to the Table.

Shaded areas can be changed.

If the interest rate changes, the user can enter the new rate in Annual Interest Rate Column. Each cell extracts Interest Rate from top cell.
Additional payments are entered in the Additional Payment Column. These payments are applied to the Principal Amount.
I have used some icons and pictures from different sources which have been written on template.
Some Excel functions including Sequence, 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.

Users can see Monthly Payment under Payment Column which is extracted from Data Entry Area on the on E15 Cell. In the following cells, I used the below written formula:
=IF(D16<>D15,-PMT(D16/12,(Term*12)-B15,I15),E15)
Balance*Interest_Rate/12 give us Interest Amount which can be found under Interest Column.

Reviews Add a review
No reviews yet

More From Excel World

Browse our top rated business templates. See All
What-If_Analysis_Goal_Seek
470
63
                                     What-If Analysis…
Women Volleyball Teams_2018
410
25
This template covers women volleyball players' information of 16 countries in 2018.   Template is consisted of 4 sheets: -Cover…
What-If_Analysis_Scenarios
465
69
This Tutorial is about Scenarios (one of What-If Analysis Tools). It covers "Creating Scenarios", "Merging Scenarios" and "Scenario Summary Report".…
Kebabs, Pizzas and Sandwiches Recipes and Ideas
549
31
Dear Excel Lovers! Dear Fast Food Lovers! The Excel template contains menus, including the foods and quantities used in the…
Static Amortization Schedule
566
49
Financial Schedules- Amortization Schedule An amortization schedule is a table detailing each periodic payment on an amortizing loan, as generated…
Sausage Casings' Transactions Spreadsheet
1,174
50
Hi, This spreadsheet is consisted of the following 11 (eleven) worksheets: 1 - Cover 2 - Chart 3 - Rates…
See All