Student Loan Payoff Calculator with Amortization Table in Excel

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.

Using PMT Function to Student Loan Payoff Calculator with Amortization Table Excel

  • Now select cell D7 and enter the following formula:
=D6/H4

This will estimate the Interest Rate per period.

Using PMT Function to Student Loan Payoff Calculator with Amortization Table Excel

  • Next select cell H5 and enter the following formula:
=D5*H4

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

Using PMT Function to Student Loan Payoff Calculator with Amortization Table Excel

  • 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:
=H7

Doing this will enter the first date of the loan repayment cycle.

Using PMT Function to Student Loan Payoff Calculator with Amortization Table Excel

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

Using PMT Function to Student Loan Payoff Calculator with Amortization Table Excel

  • Then select cell G11 and enter the following formula:
=E11-F11

This 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-G11

Finally, 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:
=H11

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

Using PMT Function to Student Loan Payoff Calculator with Amortization Table Excel

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

Using PMT Function to Student Loan Payoff Calculator with Amortization Table Excel

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.

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

  • Now select cell D7 and enter the following formula:
=D6/H4

This will estimate the Interest Rate per period.

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

  • Next select cell H5 and enter the following formula:
=D5*H4

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

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

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

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

  • 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:
=H7

Doing this will enter the first date of the loan repayment cycle.

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

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

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

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 has to pay per Payment cycle to the authority. After that, this Interest will be calculated upon the Opening Balance of each period.

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

  • Then select cell G11 and enter the following formula:
=E11-F11

This 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-G11

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

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

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

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

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:
=H11

This will return the Closing Balance of the previous cycle’s Closing Balance as the Opening Balance of the present cycle.

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

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

Implementing Conventional Formula to Student Loan Payoff Calculator with Amortization Table Excel

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

Student Loan Payoff Calculator with Amortization Table Excel

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

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

ExcelDemy
Logo