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 “Loan Amortization Schedule” and apply required formatting
- Insert your company logo on the top left side of the template
- Enter headings for customer information i.e. Customer Name, and Loan Account No in B6 & B9 respectively
- Enter the amortization input fields i.e. Loan Amount, Annual Interest Rate, Loan Tenure (Years), Payment Per Year, and EMI Start Date and calculator fields, Monthly Installment, Total Installments, Total Interest, and Loan End Date as following: –
- 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 “Monthly Installment”, use PMT built in function that is used to calculate the regular scheduled payment for a standard amortizing amount. So, enter PMT function in cell G7 as “=PMT($D$8/$D$10,$D$9*$D$10,-$D$7)”
Formula Explanation: –
- Rate – In the first argument, to get the monthly interest rate, have converted Annual Interest Rate which is in D8 by Payment Per Year provided in cell D10.
- Nper – Have taken D9 and later multiplied with Payment Per Year given in cell D10.
- PV –D7 as the present value in negative form as we want function to return the answer in positive number.
Number of Installments: –
- To calculate the “Total number of Installments”, multiply Loan Tenure with Payment Per Year as following.
Total Interest: –
- To calculate Total Interest, let’s multiply Monthly Installment with Total Installments and then subtract the Original Loan Amount from it.
Loan End Date: –
- To calculate Loan End Date, enter EDATE function and take EMI Start Date as Start Date and then Monthly Installment in second argument and subtract 1 from it to get the 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 Loan Summary 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 Total Interest and till when the loan will be active.
- Now to get the Amortization Schedule, move on to the second part and enter all the necessary and important fields as shown in following image.
- Now let’s have the automated Amortization Schedule, which will have Installment Number, when is next Installment Due, what is the Opening Liability 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.
Installment Number: –
- To get the Installment Number automatically, IF and ROW functions are used together in A17 as following: – “=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 IF function along with EDATE function in cell B17 as “=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 Installment Amount, let’s enter the PMT function in cell D17 wrapped inside IF Function which will return if Installment number has some value otherwise formula will return an empty space.
- Enter following formula in cell D17: – “=IF(A17<>””,PMT($D$8/$D$10,$G$8,-$D$7),””)”.
Formula Explanation: –
- IF function is checking whether Installment Number is non-blank or not? If not, then it will return the value returning by the PMT function otherwise the function will return the empty space.
- In the first argument of PMT function, converted the annual interest rate into monthly rate by dividing D8 by Payment Per Year given in cell D10 and locked both cell references. Then, selected Total Installments given in cell G8 (make it absolute reference). And in the third argument, as present value, supplied Loan Amount given in cell D7 in negative and locked down.
Principal Amount: –
- To get the Principle Amount of the payment, let’s enter PPMT function wrapped inside IF function which will only return if Installment Number has some value. If Installment Number is blank, it will return an empty space in the cell.
- Enter following formula in cell E17: – “=IF(A17<>””,PPMT($D$8/$D$10,A17,$G$8,-$D$7),””)”.
Formula Explanation: –
- Logical test of the IF function checked whether Installment Number has value or not? If it has, it will execute the PPMT function that is supplied in the second argument of IF function otherwise, will return the empty space in cell.
- In PPMT function, first monthly interest rate has been calculated and converted into absolute reference. Then taken A17 cell reference that contains the first Installment Number. And in the 3rd argument, Total Installments supplied in cell G8 was supplied as absolute reference and then Loan Amount in negative form was considered in the fourth argument as absolute reference.
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: –
- IPMT function in Excel is used to calculate the Interest Amount of the payment. And using with IF function, it returns the 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 IF function checked whether Installment Number is non blank or not? If it is, it will execute the formula supplied in the second argument of the function which has IPMT function otherwise, the IF function will execute the value supplied in the third argument that is an empty space in the cell.
- PPMT function: – First monthly interest rate has been calculated and then converted into absolute reference. After that cell A17 was taken into second argument that contains the first Installment Number. And in the 3rd argument, Total Installments provided in cell G8 was supplied as absolute reference and lastly the Loan Amount in negative form in the fourth argument as absolute reference.
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: –
- O/s Principle will be evaluated easily with the simple calculation with the help of IF function in cell G17 as following: – “=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 Loan Amount in cell G16 as show in the following image.
- Enter IF function to calculate Opening Liability Amount in C17 as following: “=IF(A17<>””,G16,””)”.
Formula Explanation: –
- IF function first checked whether or not Installment Number 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 G16 to G17 which then have the O/s Principle for the first installment.
- Copy all the formulas from row 17th and paste it down till 255th 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.