Excel Loan Repayment: Formulas, Functions, and Conditional Formatting

This document provides a detailed explanation of how to create a loan repayment schedule in Microsoft Excel. It covers the application of the PMT function for calculating equal installment amounts, the computation of monthly interest and principal payments, and the calculation of beginning and ending loan balances. The guide also explains the use of conditional formatting to visually represent the loan status, highlighting the decreasing outstanding balance over time. The document includes screenshots of the formulas applied in Excel and references relevant academic literature on financial modeling and spreadsheet applications, offering a comprehensive understanding of loan amortization and repayment scheduling.

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.

3LOAN REPAYMENT SCHEDULE IN EXCEL
Every installment amount constitutes two parts, interest and principle. To compute the
interest amount per month, the beginning outstanding balance of the loan amount is multiplied
by the monthly interest rate.
Monthly Interest Computation:
Syntax: =Beginning outstanding balance*Annual interest rate/12
As the installments are paid monthly, to compute the monthly interest payments, the
beginning outstanding balance of the loan amount is multiplied by the monthly interest rate.
Following screenshot can show it in a better way.

4LOAN REPAYMENT SCHEDULE IN EXCEL
Monthly Principal Payment Computation:
Monthly principle payment can be computed by subtracting the monthly interest from the
installment amount. As loan amount moves towards final closure, the principal payments tend to
increase and the interest payment tends to decrease. Following screenshot can show it in
practical.
Ending and Beginning Balance computation:
To calculate the ending balance of the outstanding loan amount, the principle payment
amount is deducted from the beginning balance of the loan amount of the respective month, and
the ending balance is carried forward to the beginning balance f the following month.
Conditional Formatting:
Conditional formatting in excel, helps in presenting information with some customized
cell formats based on certain logic and criteria. It helps in quick interpretation of information and
overview of the numeric figures and parameters. To use conditional formatting, first select the
range where the conditional formatting needs to be applied, then from the home tab click on the

5LOAN REPAYMENT SCHEDULE IN EXCEL
dropdown button of the conditional formatting and select appropriate formatting option based on
the requirement. Following screenshot can show it practically.
In the given interest computation schedule, a conditional formatting has been used in the
ending balance column, it starts with the red shaded cell colors and moves to the green through
the yellow color, as the outstanding loan amount decreases. It becomes complete green as the
outstanding loan amount becomes zero.

6LOAN REPAYMENT SCHEDULE IN EXCEL
Bibliography:
Barton, J.T., 2015. Models for Life: An Introduction to Discrete Mathematical Modeling with
Microsoft Office Excel. John Wiley & Sons.
Chong, C.K., Puteh, M. and Goh, S.C., 2015. Framework to Integrate Spreadsheet into the
Teaching and Learning of Financial Mathematics. Electronic Journal of Mathematics &
Technology, 9(1).
Matsumoto, K., Munro, J.D., Chang, M. and Gouws, D., 2014. An Excel model of mortgage
refinancing decisions for sensitivity analysis and simulation. Journal of Accounting and
Finance, 14(3), p.21.
McFedries, P., 2016. Excel 2016 formulas and functions. Que.