Formula
The loan balance formula is based on the present value of an annuity formula. The repayment of a loan is simply a series of regular payments (an annuity). Consequently it follows that the value of the loan is the present value of all these regular payments. Furthermore the balance outstanding at any point in time must be the present value of the loan installments outstanding at that time.
The formula shows the outstanding balance on a loan today (PV) assuming a series of regular loan installment payments (Pmt) and a discount rate (i). It is important to realize that the payments are at the end of each period for the remaining n periods of the loan.
Use
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 outstanding balance formula discounts the value of each payment back to its value at the start of period 1.
Excel Loan Balance Function
The Excel PV function is an alternative for the loan balance formula, and has the syntax shown below.
PV(i, n, pmt, FV, type)
*The FV and type arguments are not applicable 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 repayable over a period of 7 years with monthly installments of 779.31 at the end of each month. What is the balance outstanding on the loan after 26 months?
The calculation of the balance using the outstanding balance formula is 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
Furthermore the use of the Excel PV function results in the same answer as follows:
PV = PV(i, n, pmt) PV = PV(8%/12,58,-779.31) PV = 37,384.70
The outstanding 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.