Rubayed Razib Suprov
Most of the students had to deal with the loan. The complex calculation of loan Repayment amount and how long it will take is difficult for anybody. To resolve this, here we will create two separate examples of Student Loan Payoff Calculator with an Amortization Table in excel, with elaborate explanations.
Download Practice Workbook
Download this practice workbook below.
2 Examples to Create Loan Payoff Calculator with Amortization Table in Excel
For the demonstration purpose, we are going to create the Student Loan Payoff Calculator in Excel. We should have the necessary information like the Principal Amount, Payment Year, Compound Rate per Year, and Compound Period per Year
1. Using PMT Function
Using the PMT function, we can directly Calculate how much Payment students have to make per Payment period. We also use the DATE, MONTH, YEAR, and DAY functions to Calculate the Payment dates in regular intervals.
Steps
- In the beginning, we will set up our dataset to organize the input data and then set up a Table for further calculations. We created the below dataset to accommodate the input data.
- We currently have the borrowed amount as the Principal Amount. We also got the Total Payment Period, Annual Interest Rate, and Compound Frequency per Year.
- Using this information, we are going to estimate the Loan Payment per period below.
- Now select cell D7 and enter the following formula:
=D6/H4This will estimate the Interest Rate per period.
- Next select cell H5 and enter the following formula:
=D5*H4This function will Calculate the no of the total compounded periods. In other words, the no of Payments students need to make in order to repay their Student Loans.
- After that, select cell H6 and enter the following formula:
=PMT(D7,H5,-D4,0)Doing this will Calculate the Payment that needs to be made each month by the student in order to Payoff their Student Loans.
- Finally, we enter the beginning date of the loan Repayment cycle in cell H7.
- We got all the necessary information to create the Amortization Table. The Amortization Table will help us to visualize how the Interest and the Payments are changing their dynamic.
- Select cell C11 and then enter the following formula:
=H7Doing this will enter the first date of the loan repayment cycle.
- After that, to continue this calculation for the subsequent period, we need some modifications for the next periods.
- Select the cell C12 and enter the following formula:
=DATE(YEAR(C11),MONTH(C11)+(12/$H$4),DAY(C11))- We already specified the date in cell C11.
- This formula will determine the starting date or the Payment date of each cycle.
Breakdown of the Formula
- YEAR(C11),MONTH(C11)+(C),DAY(C11) : This part of the formula will return the year, month, and day component of a date argument stored in the cell C11.
- DATE(YEAR(C11),MONTH(C11)+(12/$H$4),DAY(C11)): The DATE function will create a date by the components returned by the YEAR, MONTH, DAY functions. Note that the month part here is increased by the value of (12/$H$4). Which is basically the interval period between Payments.
- Drag the Fill Handle to cell C20 to fill the range of cells C11:C30 with the date of the Payment.
- Next, select cell D11 and enter the following formula:
=D4- This will begin the Amortization Table with the Opening Balance for the first cycle. Which is the loan that the student actually took in the beginning. At the end of the whole cycle, this Opening Balance will be reduced and at the end of all Repayment periods, the Opening Balance should be 0. Provided that the borrower paid all Payments on a regular basis. We will link the Closing Balance with this cell later part of this article.
- Then select cell E11 and enter the following formula:
=$H$6- This formula will place the installment per period in the Table. This value will be constant for every Payment cycle.
- Select cell F11 and enter the following formula:
=D11*$D$7
This will estimate the Interest that the borrower must pay per Payment cycle to the authority. After that, this Interest will be Calculated upon the Opening Balance of each period.
- Then select cell G11 and enter the following formula:
=E11-F11This formula will calculate the portion of the Principal paid after subtracting the Interest from the Periodic Payment, in each Payment cycle.
- Next, select cell H11 and enter the following formula:
=D11-G11Finally, we estimated the Closing Balances in each cycle. This calculation is done by subtracting the Principal paid in the G11 from the Opening Balances for that cycle.
- Next, select cell D12, and enter the following formula:
=H11This will return the Closing Balance of the previous cycle’s Closing Balance as the Opening Balance of the present cycle.
- Then select the range of cell E11:H11.
- And then drag them to row 12, just one row below row 11.
- So. the new range of cells D12:H12 is now filled with the values.
- Now again select a range of cells C12:H12, and then drag them to row 30.
- Doing this will fill the range of cells C11:H30 will be filled with the Opening Balance, Periodic Payment, Interest Paid, and Closing Balance info of each Payment cycle.
Read More: Car Loan Amortization Schedule in Excel with Extra Payments
2. Implementing Conventional Formula
We will use the conventional formula which Calculates the Payments at each period. We also use the DATE, MONTH, YEAR, and DAY functions, to Calculate the Payment dates in regular intervals.
Steps
- In the beginning, we will set up our dataset to organize the input data and then set up a Table for further calculations. We created the below dataset to accommodate the input data.
- We currently have the borrowed amount as the Principal Amount. We also got the Total Payment Period, Annual Interest Rate, and Compound Frequency per Year.
- Using this information, we are going to estimate the loan Payment per period below.
- Now select cell D7 and enter the following formula:
=D6/H4This will estimate the Interest Rate per period.
- Next select cell H5 and enter the following formula:
=D5*H4This function will Calculate the no of the total compounded periods. In other words, the no of Payments students needs to make to repay their Student Loans.
- After that, select cell H6 and enter the following formula:
=(D4*D7)/(1-(1+D7)^(-H4*D5))Doing this will Calculate the Payment that needs to be made each month by the student to Payoff their Student Loans.
- Finally, we enter the beginning date of the loan Repayment cycle in cell H7.
- We got all the necessary information to create the Amortization Table. The Amortization Table will help us to visualize how the Interest and the Payments are changing their dynamic.
- Select cell C11 and then enter the following formula:
=H7Doing this will enter the first date of the loan repayment cycle.
- Next, select cell D11 and enter the following formula:
=D4- This will begin the Amortization Table with the Opening Balance for the first cycle. Which is the loan that the student actually took in the beginning. At the end of the whole cycle, this Opening Balance will be reduced and at the end of all Repayment periods, the Opening Balance should be 0. Provided that the borrower paid all Payments on a regular basis.
Then select cell E11 and enter the following formula:
=$H$6
This formula will place the installment per period in the Table. This value will be constant for every Payment cycle.
Select cell F11 and enter the following formula:
=D11*$D$7This will estimate the Interest that the borrower has to pay per Payment cycle to the authority. After that, this Interest will be calculated upon the Opening Balance of each period.
- Then select cell G11 and enter the following formula:
=E11-F11This formula will calculate the portion of the Principal paid after subtracting the Interest from the Periodic Payment, in each Payment cycle.
- Next, select cell H11 and enter the following formula:
=D11-G11Finally, we estimated the Closing Balances in each cycle. This calculation is done by subtracting the Principal paid in the G11 from the Opening Balances for that cycle.
- After that, to continue this calculation for the subsequent period, we need some modifications for the next periods.
- Select the cell C12 and enter the following formula:
=DATE(YEAR(C11),MONTH(C11)+(12/$H$4),DAY(C11))- We already specified the date in cell C11.
- This formula will determine the starting date or the Payment date of each cycle.
Breakdown of the Formula
- YEAR(C11),MONTH(C11)+(C),DAY(C11) : This part of the function will return the year, month, and day component of a date argument stored in the cell C11.
- DATE(YEAR(C11),MONTH(C11)+(12/$H$4),DAY(C11)): The DATE function will create a date by the components returned by the YEAR, MONTH, DAY functions. Note that the month part here is increased by the value of (12/$H$4). Which is basically the interval period between Payment.
- Next, select cell D12, and enter the following formula:
=H11This will return the Closing Balance of the previous cycle’s Closing Balance as the Opening Balance of the present cycle.
- Then select the range of cell E11:H11.
- And then drag them to row 12, just one row below row 11.
- So the new range of cells D12:H12 is now full with the values.
- Now again select the range of cells C12:H12, and then drag them to row 30.
- Doig this will fill the range of cells C11:H30 with the Opening Balance, Periodic Payment, Interest Paid, and Closing Balance info of each Payment cycle.
In these ways, you can create a student loan payoff calculator with an amortization table in Excel.
Read More: Create Loan Amortization Schedule with Moratorium Period in Excel
Conclusion
To sum it up, the “Student Loan Payoff Calculator with Amortization Table excel” is responded by creating two separate sheets with the help of two different methods. The first one is to be creating the Calculator with the help of the PMT function. Another one is to use the conventional method to create the Calculator.
For this problem, a workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
Related Articles
- SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option
- Excel Loan Calculator with Extra Payments (2 Examples)
- Excel Simple Interest Loan Calculator with Payment Schedule
- Home Loan EMI Calculator with Reducing Balance in Excel
- Create Home Loan Calculator in Excel Sheet with Prepayment Option
- Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option
