XL: Finding the Balance of a Loan for a Given Period
This article was previously published under Q214091
This article has been archived. It is offered "as is" and will no longer be updated.
To calculate the balance of a loan for any period during the life ofthe loan, use the IPMT function to calculate the interest payment for thenext period and divide it by the interest rate of the loan.
The following examples demonstrate the use of the IPMT function to find the balance of a loan using the following formula
=IPMT(rate,per,nper,PV)/ratewhere rate is the interest per payment period, per is the period for which you want to find the balance, nper is the total number of payment periods, and PV is the present value of the loan.
Example 1: Monthly PaymentsTo find the balance of a $65,000 loan after 36 payments (3 years), when the rate of the loan is 9.5 percent per year, and payments are made monthly over a period of 30 years, calculate the interest payment (IPMT) of the loan for the next payment period and divide it by the computed interest rate, as in the following example.
In cell A1 of an Excel worksheet, enter:
=IPMT(9.5%/12,37,30*12,-65000)/(9.5%/12)Note that 9.5%/12 is the interest rate per period, 37 is the period forwhich you want to know your balance, 30*12 is total number of paymentperiods, and -65,000 is the present balance of the loan.
This formula returns the value of $63,674.26.
Example 2: Annual PaymentsTo find the balance of a $65,000 loan after 3 payments (3 years), when the rate of the loan is 9.5 percent per year, and payments are made yearly over a period of 30 years, calculate the IPMT of the loan for the next payment period and divide it by the computed interest rate, as in the following example:
=IPMT(9.5%,4,30,-65000)/9.5%This formula returns a value of $63,569.61. In this example, thereis no need to divide the rate and multiply the years by 12, becausethe number of payments per year is 1.
XL2000 xl2002 xl97 xl98 xl2001
Article ID: 214091 - Last Review: 12/05/2015 12:28:27 - Revision: 2.4
Microsoft Excel 2000 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 2001 for Mac, Microsoft Excel 98 for Macintosh
- kbnosurvey kbarchive kbhowto KB214091