how to calculate student loan repayments in excel

We will try to respond to all the relevant queries asap. And don’t hesitate to ask any questions in the comment section below. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. To sum up, we have discussed the procedure to prepare a loan amortization schedule with a variable interest rate in Excel.

Adjust the Period according to the Number of Years.To make a loan amortization schedule just change the values such as the Original Balance, Number of Years, Annual Interest Rate (AIR), and Lump Sum.H10 is the lump sum end payment amount.F9 refers to the previous value of remains balance to pay back.Now follow the steps below to calculate the PMT. In the PMT column, we will calculate the amount of loan payment per month. Step-1: Calculate the Payment Amount, PMT Thus, in the Period column, we have taken a serial number from 1 to 24. Total Number of Payments Against the Loan = (The number of Years x Loan Payment Frequency per Year) = 2 x 12 = 24. I will show you to prepare a loan amortization schedule based on the following data. How to Prepare a Loan Amortization Schedule with Variable Interest Rate in Excel Steps to Prepare a Loan Amortization Schedule with Variable Interest Rate in Excel In the table, the total number of periods to pay back the loan, the remaining balance to pay, how much money you are paying for the interest, and the original amount all are stated. Loan Amortization Schedule with Variable Interest Rate.xlsxĪ Loan Amortization Schedule is a table of period payments against the loan.

How to calculate student loan repayments in excel