Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

XL: Finding the Balance of a Loan for a Given Period


View products that this article applies to.

Summary

To calculate the balance of a loan for any period during the life of the loan, use the IPMT function to calculate the interest payment for the next period and divide it by the interest rate of the loan.

↑ Back to the top


More information

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)/rate
where 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 Payments

To 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 for which you want to know your balance, 30*12 is total number of payment periods, and -65,000 is the present balance of the loan.

This formula returns the value of $63,674.26.

Example 2: Annual Payments

To 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, there is no need to divide the rate and multiply the years by 12, because the number of payments per year is 1.

↑ Back to the top


Keywords: KB214091, kbhowto

↑ Back to the top

Article Info
Article ID : 214091
Revision : 6
Created on : 1/24/2007
Published on : 1/24/2007
Exists online : False
Views : 36