The loan balance formula is based on the present value of an annuity formula, and shows the outstanding balance on a loan today (PV) based on of series of regular loan installment payments (Pmt). The payments are made at the end of each period for the remaining n periods of the loan, and a discount rate i is applied.
The balance outstanding on a loan is equal to the present value of the remaining periodic loan installments.
This is further discussed and explained in our How to Calculate an Outstanding Loan Balance tutorial.
The loan balance formula discounts the value of each payment back to its value at the start of period 1 (present value).
The Excel PV function can be used instead of the loan balance formula, and has the syntax shown below.
PV(i, n, pmt, FV, type)
*The FV and type arguments are not used when using the Excel present value of an annuity function.
Example using the Loan Balance Formula
A loan of 50,000 with an interest rate of 8%, is repaid over a period of 7 years with monthly installments of 779.31 paid at the end of each month. What is the balance outstanding on the loan after 26 months?
The outstanding balance can be calculated using the loan balance formula as follows:
Pmt = Periodic payment = 779.31 a month i = Discount rate = 8%/12 a month n = Number of periods remaining = 84 - 26 = 58 PV = Pmt x (1 - 1 / (1 + i)n) / i PV = 779.31 x (1 - 1 / (1 + 8%/12)58) / (8%/12) PV = 37,384.70
The same answer can be obtained using the Excel PV function as follows:
PV = PV(i, n, pmt) PV = PV(8%/12,58,-779.31) PV = 37,384.70
The loan balance formula is one of many annuity formulas used in time value of money calculations, discover another at the link below.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Double Entry Bookkeeping. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a degree from Loughborough University.