« Combining data in cells | Main | Financial Functions: CUMIPMT & CUMPRINC »
April 01, 2005
Financial Functions: Calculating Principal & Interest
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 PPMTFor 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
- 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.
Posted by Dave at April 1, 2005 09:59 PM
Comments
Good !
Do you know how to "mix" to xy curves on the same chart ? (with x1<>x2 and y1<>y2)
Thanks
Posted by: berny at December 6, 2005 03:10 AM