Microsoft KB Archive/52224

{| = Excel: Calculating Principal Paid/Owed and Interest Paid/Owed =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q52224

SUMMARY
The following four formulas calculate:


 * 1) The amount of principal still owed after &quot;x&quot; number of periods
 * 2) The amount of principal paid after &quot;x&quot; number of periods
 * 3) The amount of interest still owed after &quot;x&quot; number of periods
 * 4) The amount of interest paid after &quot;x&quot; number of periods

MORE INFORMATION
  To calculate the amount of the principal remaining on a loan after &quot;x&quot; number of periods, use the function FV (future value) as follows: =FV(rate,x,pmt,pv) where &quot;rate&quot; is the rate per period, &quot;x&quot; is the number of periods that have been paid, &quot;pmt&quot; is the amount of the payments, and &quot;pv&quot; is the amount of the loan.   To calculate the amount of principal already paid on a loan after &quot;x&quot; number of months, use the following formula: =-pv-FV(rate,x,pmt,pv) where &quot;pv&quot; is the amount of the loan (a negative number) and the variables for the FV function are the same as above.   To calculate the amount of interest paid after &quot;x&quot; number of periods, use the following formula: =pmt*x+pv+FV(rate,x,pmt,pv) where all variables are as previously described.   To calculate the amount of interest still owed, use the following formula =PMT*(nper-x)-FV(rate,x,pmt,pv) where &quot;nper&quot; is the total number of periods for the loan, &quot;pmt&quot; is the amount of the payments, &quot;pv&quot; is the amount of the loan, and &quot;interest paid&quot; is the amount calculated from the previous example. 

Note: It is assumed for all financial functions that the present value (PV) of a loan is a negative number. For more information on the financial functions PV, FV, IPMT, NPER, PMT, PPMT, and RATE, see pages 72-73 in the &quot;Microsoft Excel: Functions and Macros&quot; manual for Excel version 2.20 or pages 87-89 in the &quot;Microsoft Excel Arrays, Functions, and Macros&quot; manual for Excel version 1.50.
 * }