Dynamic Amortization Schedule
Originally published: 04/11/2020 15:18
Publication number: ELQ-19862-1
View all versions & Certificate
certified

Dynamic Amortization Schedule

Dynamic Amortization Schedule in Microsoft Excel.

Description
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.

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.

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 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.

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 Dynamic.

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

This Best Practice includes
1 Excel File

Ilgar Zarbaliyev offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss

Further information

Financial Modelers are recommended to use this template

Two additional columns: Annual Interest Rate and Additional Payment differentiate make this template dynamic

Dynamic Array Functions would improve the quality of template

Reviews

  • Rate this Downloadable Best Practice

    Write a review

  • Franklind Lea(last updated: 27/05/2021 20:15)
    Does Not Update
    The spreadsheet isn't dynamic. If the term is extended beyond 15 years, say 20 years, the amounts fail to go past the 15 year (180 payments) column.
  • Jogishwar Singh(last updated: 13/02/2021 11:06)

keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Ilgar Zarbaliyev

keyboard_arrow_leftkeyboard_arrow_right

Any questions on Dynamic Amortization Schedule?

The user community and author are here to help. Go ahead!


3.0 / 5 (2 votes)

please wait...