OpenOffice Calc Tips

In this tip, we show how to determine the Principal and Interest components of a loan repayment using the built in OOo Calc functions IPMT and PPMT

For a standard loan or mortgage with fixed interest rate over the lifetime of the loan, a single regular payment can be calculated with the PMT function. This has been discussed in a previous tip.

However, as the amount of the outstanding balance is reduced over time, the Principal and Interest components of the loan change.

OOo Calc provides two functions that calculate the Interest and Principal components of any loan payment.

The arguments to the functions are :
IPMT(rate, per, nper, pv, fv, type)
and
PPMT(rate, per, nper, pv, fv, type) where

• rate The interest rate for each of the time periods in the nper argument.
• per The number of the payment period. The first payment is 1 and the last is nper.
• nper The number of time periods between now and the end of the loan. For a standard 30-year mortgage - with monthly payments - this is 360.
• pv The present value (or initial value) of the loan. Also known as the principal fv The future value of the loan (desired) at the end of the nper payment periods. For mortgage payment calculations, this would normally be 0.
• type Optional argument which controls whether payments are made at the start of a period or the end.

In the example below, the loan parameters are defined in C3:C5
and the loan payment in C6 is calculated as
=-PMT(C3;C4;C5)

The Principal component formula in C9 is calculated as
=-PPMT($C$3;B9;$C$4;$C$5)

and the Interest component in D9 is calculated as
=-IPMT($C$3;B9;$C$4;$C$5) For subsequent periods, the only argument to these functions that changes is the period number.

In case you need further convincing, the IPMT and PPMT components are summed in column E, and are constant throughout the life of the loan - as expected.

This entry was posted on Friday, April 1st, 2005 at 8:28 pm and is filed under Finance. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.