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/H4`

This will estimate the **Interest Rate** per period.

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

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

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

- 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/H4`

This will estimate the **Interest Rate** per period.

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

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

`=H7`

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

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

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

`=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 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**