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

 Last reviewed: February 2, 1998
Article ID: Q96437

The information in this article applies to:
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0, 5.0c
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0, 5.0a
  • Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0

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.

MORE INFORMATION

For example, 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 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%/12,37,30*12,-65000,,0)/(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. (You can check this value against the Amortize spreadsheet located in the EXAMPLES subdirectory of your Excel directory, for Microsoft Excel for Windows, or the Examples folder for Microsoft Excel for the Macintosh.)

In the function above, 12 refers to the number of payments in a year. For a loan that is compounded yearly, you would calculate the loan balance at the end of the third year with the following formula: 

   =IPMT(9.5%,4,30,-65000,,0)/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.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 246, 341-342

Additional query words: 5.00 3.00 4.00 4.00a 7.00 7.00a 97 98 XL98 XL97 XL7
XL5 XL4 XL3
Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0,4.0a,5.0,5.0c,7.0,7.0a,97; MACINTOSH:2.0,3.0,4.0,5.0,98
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.