Loan Balance Formula

Formula

PV = Pmt x (1 - 1 / (1 + i)n) / i
Variables used in the annuity formula
PV = Loan Balance
Pmt = Periodic payment
i = Discount rate
n = Number of periods remaining

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 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 loan balance formula discounts the value of each payment back to its value at the start of period 1 (present value).

Excel Function

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.

Loan Balance Formula October 30th, 2017Team

You May Also Like


Related pages


daily cash reconciliation templatesupplier invoice processingrelevance of variance analysis to budgeting and standard costingretained profit formulahow to use the pmt function in exceljournal entries examples accountingdepreciation of fixed assets journal entryamortise meaningexcel fvreplenish petty cash fundeffective annual discount ratedouble entry bookkeeping example questionsentry for allowance for doubtful accountsfreight on board originexamples of journalizing transactionscalculate simple interest in excelimportance of standard costing and understanding variancesmileage claim form templatedays sales in ending receivablesdefinition of accrued revenueaptitude test for accountingtable of future value annuity factorsincome tax provision accounting entriesredemption of bonds payableincome statement format xlsdupont formula examplefixed asset turnover ratiolease accounting entriesvertical format of balance sheet in excelbad debt write off journal entrycomputation of working capitalprepayment accountingexcel general ledgeraccounting marketable securitiessundry debtors accountdividends normal balancelcm method accountingannuity to present valuedetailed accounting equationasset turnover rate formuladepreciation formula excelwhat is amortisation in accountingcash receipt voucher formatsample accounting worksheetintangible assets examplesvouchers templates free downloadjournal entry for depreciationaccounting adjusting journal entriesaccount receivable turnoverfifo ending inventory calculatorblank accounting worksheetwages double entryexample of cash receipts journalpresent value of perpetuitybonds payable on balance sheethow to calculate discount on excelprepaid insurance normal balancedays sales in inventory ratio formulatemplate timesheetlabor efficiency formulajournal entry for capital accountprovision for bad debts journal entry effectcash accruals calculationfiguring gross marginaccounts receivable aging templateaccounting break even calculatorbad debt expense cash flow statementdividend receivable journal entryjournal entry for accrued incomeretained earnings equationprepaid expense account typeexample of a trial balance worksheettimes interest earned calculation