Loan Repayment Schedule in Excel

Running head: LOAN REPAYMENT SCHEDULE IN EXCEL
Loan Repayment Schedule in Excel
Name of the Student:
Name of the University
Author’s Note:

1LOAN REPAYMENT SCHEDULE IN EXCEL
Table of Contents
Explanations of Formulas used for the Loan repayment schedule:.................................................2
Application of PMT function:.....................................................................................................2
Monthly Interest Computation:....................................................................................................3
Monthly Principal Payment Computation:..................................................................................3
Ending and Beginning Balance computation:.............................................................................4
Conditional Formatting:...................................................................................................................4
Bibliography:...................................................................................................................................6

2LOAN REPAYMENT SCHEDULE IN EXCEL
Explanations of Formulas used for the Loan repayment schedule:
Loan repayment schedule shows the periodic repayments of installments breaking it up in
principal and interest parts. It also shows the beginning and ending balance of the loan
outstanding throughout the tenure of the loan. In preparing the loan repayment schedule, the
most important part is to calculate the equal installment amounts to be paid. To calculate such
installment amount ‘PMT’ function in excel can be used.
Application of PMT function:
Syntax: =PMT(rate,nper,pv,fv,type)
Here, PMT stands for equal installment amounts, rate means interest rate applicable per annum,
NPER stands for total number of payments in the tenure of the loan, PV stands for loan amount
at the initiation, FV stands for future value at the end of the loan term and type denotes the
interest accumulation type. While applying this formula, rate must be converted to the
accumulation period, if it is monthly accumulated then it must be divided by 12 and so on. In
addition, a zero is put in place of FV, as at the end of the loan term the outstanding balance
would be zero. A screen shot of the formula as applied in the excel work sheet has been added
for better understanding.