An ** Amortization Schedule** which is also known as mortgage or loan schedule, is a list of payments that shows each loan repayment and a breakdown of the principle amount and the interest. It shows the breakdown so that you can see how much is going toward the principle and interest each month. Apart from this, the schedule also shows the remaining balance after each payment has been made. In other words, it calculates your monthly loan repayment and lets you know how much of your monthly installment will go towards the principle and how much will be paid for interest.

Following are the steps to create ** Amortization Schedule Template**: –

- Open a blank excel spreadsheet
- Give a title to template as
and apply required formatting*“Loan Amortization Schedule”* - Insert your company logo on the top left side of the template

- Enter headings for customer information i.e.
in B6 & B9 respectively*Customer Name, and Loan Account No* - Enter the amortization input fields i.e.
and calculator fields,*Loan Amount, Annual Interest Rate, Loan Tenure (Years), Payment Per Year, and EMI Start Date*as following*Monthly Installment, Total Installments, Total Interest, and Loan End Date**: –*

- Enter some data in ‘input column’ as following:-

- Now let’s enter the required formulas to calculate the
*Loan Summary*

__Calculate Monthly Installment: –__

- To calculate “
use*Monthly Installment”,*built in function that is used to calculate the regular scheduled payment for a standard amortizing amount. So, enter*PMT*function in cell*PMT*as “*G7**=PMT($D$8/$D$10,$D$9*$D$10,-$D$7)*”

**Formula Explanation**: –

– In the first argument, to get the monthly interest rate, have converted*Rate*which is in D8 by*Annual Interest Rate*provided in cell D10.*Payment Per Year*– Have taken D9 and later multiplied with*Nper*given in cell D10.*Payment Per Year*–D7 as the present value in negative form as we want function to return the answer in positive number.*PV*

__Number of Installments: –__

- To calculate the “
, multiply*Total number of Installments”*with*Loan Tenure*as following.*Payment Per Year*

__Total Interest: –__

- To calculate
, let’s multiply*Total Interest*with*Monthly Installment*and then subtract the*Total Installments**Original*from it.*Loan Amount*

__Loan End Date: –__

- To calculate
, enter*Loan End Date*function and take*EDATE*as*EMI Start Date*and then*Start Date*in second argument and subtract 1 from it to get the*Monthly Installment**Loan End Date.*

- With the last step, half of the work for this template has been finished. Now, as soon as input column will be updated, the
will also be updated automatically. It will help in quickly deciding and planning that how much one will have to pay monthly along with the*Loan Summary*and till when the loan will be active.*Total Interest* - Now to get the
move on to the second part and enter all the necessary and important fields as shown in following image.*Amortization Schedule,*

- Now let’s have the automated
, which will have*Amortization Schedule*when is next*Installment Number,*, what is the*Installment Due*amount, how much every month will be paid, the principle and the interest amount will be paid every month, and the outstanding principle by the end of every installment.*Opening Liability*

__Installment Number: –__

- To get the
automatically,*Installment Number*and*IF*functions are used together in A17 as following: – “*ROW**=IF((ROW()-ROW($A$16))>$G$8,””,(ROW()-ROW($A$16)))”.*

- Once entered the required formula in A17, drag it down to get the next installment numbers.

*Note**: – The mentioned formula will return the Installment Number up to Total Numbers of Installments to be paid. Don’t forget to freeze the required cell references as shown in the above image. *

__Installment Date: –__

- To get Installment date, use
along with*IF function*in cell B17 as “*EDATE function**=IF(A17>$G$8,””,EDATE($D$11,A17-1))”.* - Drag the formula down to get the next
*Installment Date.*

*Note**: – If the logical test (installment number is greater than Total Installments) meets the supplied criteria then it’ll return empty text (“”) otherwise will return a date as per specified number of months after EMI Start Date. Locked Total Installments and EMI Start Date cell references.*

__Installment Amount: –__

- For
let’s enter the*Installment Amount,*function in cell D17 wrapped inside*PMT*which will return if*IF Function*has some value otherwise formula will return an empty space.*Installment number* - Enter following formula in cell D17: –
*“=IF(A17<>””,PMT($D$8/$D$10,$G$8,-$D$7),””)”.*

**Formula Explanation**: –

function is checking whether*IF*is non-blank or not? If not, then it will return the value returning by the*Installment Number*function otherwise the function will return the empty space.*PMT*- In the first argument of
function, converted the annual interest rate into monthly rate by dividing*PMT*by*D8*given in cell D10 and locked both cell references. Then, selected*Payment Per Year*given in cell G8 (make it absolute reference). And in the third argument, as present value, supplied*Total Installments*given in cell D7 in negative and locked down.*Loan Amount*

__Principal Amount: –__

- To get the
of the payment*Principle Amount*let’s enter*,*function wrapped inside*PPMT*which will only return if*IF function*has some value. If*Installment Number*is blank, it will return an empty space in the cell.*Installment Number* - Enter following formula in cell E17: –
*“=IF(A17<>””,PPMT($D$8/$D$10,A17,$G$8,-$D$7),””)*”.

**Formula Explanation**: –

- Logical test of the
function checked whether*IF*has value or not? If it has, it will execute the*Installment Number*function that is supplied in the second argument of*PPMT*function otherwise, will return the empty space in cell.*IF* - In
function, first monthly interest rate has been calculated and converted into absolute reference. Then taken*PPMT*cell reference that contains the first*A17*. And in the 3*Installment Number*^{rd}argument,supplied in cell G8 was supplied as absolute reference and then*Total Installments*in negative form was considered in the fourth argument as absolute reference.*Loan Amount*

*Note: –** The above formula returned the principle amount only for the first installment and when formula was dragged in the column, it returned the principle amount for the respective period however, with the time, Principle Amount will be increased with every installment. *

__Interest Amount: –__

function in Excel is used to calculate the*IPMT*of the payment. And using with*Interest Amount*function, it returns the*IF*.*Installment Amount*- Enter the following formula in cell F17: –
*“=IF(A17<>””,IPMT($D$8/$D$10,A17,$G$8,-$D$7),””)”*.

**Formula Explanation**: –

- Logical test of the
function checked whether*IF*is non blank or not? If it is, it will execute the formula supplied in the second argument of the function which has*Installment Number*function otherwise, the*IPMT*function will execute the value supplied in the third argument that is an empty space in the cell.*IF* function: – First monthly interest rate has been calculated and then converted into absolute reference. After that cell*PPMT*was taken into second argument that contains the first*A17*. And in the 3*Installment Number*^{rd}argument,provided in cell G8 was supplied as absolute reference and lastly the*Total Installments*in negative form in the fourth argument as absolute reference.*Loan Amount*

*Note: –** The above formula returned the ***Interest*** Amount only for the first installment and when formula was dragged down in the column, it returned the Interest Amount for the respective period and with every installment, Interest Amount will be decreased. *

__O/s Principle: –__

will be evaluated easily with the simple calculation with the help of*O/s Principle*function in cell G17 as following: –*IF**“=IF(C17<>””,C17-E17,””)”.*

*Note**: – The above formula won’t return anything until Opening Liability Amount shows some value for the respective installment. *

__Opening Liability Amount: –__

- Let’s first refer the original
in cell G16 as show in the following image.*Loan Amount* - Enter
function to calculate*IF*in C17 as following:*Opening Liability Amount**“=IF(A17<>””,G16,””)”.*

**Formula Explanation**: –

function first checked whether or not*IF*is blank? When it found it is not, it returned the value supplied in cell Here G16 was taken as relative reference so when formula dragged in the next row, the relative cell reference also got changed from*Installment Number*to*G16*which then have the*G17*for the first installment.*O/s Principle*- Copy all the formulas from row 17
^{th}and paste it down till 255^{th}row which will show maximum of 240 installments.*(It can be changed based on the requirement as well).* - To give a presentable look, format the template as per your choice or as shown in the below image.

- A good looking amortization schedule is now ready to use. Change the input and everything else will be changed accordingly.

Check out the video shown below to have more clarity on the functions and other features used to create this template. If you’ve any feedback or suggestion for us, please do write us.