« Financial Functions: Calculating Principal & Interest | Main | Looking up data in tables V »
April 02, 2005
Financial Functions: CUMIPMT & CUMPRINC
A useful quantity to know for a given loan is the total interest payable over the history of the loan - or the total interest paid to a certain point. There exists a built in OOo Calc function specifically for that purpose - CUMIPMT. A companion function - CUMPRINC calculates the total principal paid to a certain point.The sample spreadsheet below illustrates the use of CUMIPMT and CUMPRINC
D3:D6 are the input parameters. Change any of these values to examine the effect on the results.
For this example, CUMIPMT and CUMPRINC are calculated for the last payment of the loan - and as expected, CUMPRINC equals the loan amount. Also, the total payments over the histoy of the loan is just the sum of CUMIPMT and CUMPRINC
The next example shows how CUMIPMT and CUMPRINC can be calculated at any point during the loan. The formulae in C13:F13 are
=-IPMT($D$8;B13;$D$7;$D$2)
=-PPMT($D$8;B13;$D$7;$D$2)
=-CUMIPMT($D$8;$D$7;$D$2;1;B13;0)
=-CUMPRINC($D$8;$D$7;$D$2;1;B13;0)
These formula can be dragged down to fill the remaining rows.
Note that without the "-" in front of the above formulae, you would see negative numbers - a correct reflection of the direction of flow of funds.
The above sheet can be downloaded here (OOo 2.0 beta)
Posted by Dave at April 2, 2005 03:49 PM
Comments
Just discovered your site. You're solving all my queries about calc!!
Would this download work on OOo 1.1.4?
Thanks in advance,
C-
Posted by: Christel at April 9, 2005 03:55 PM