OpenOffice Calc Tips

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)

This entry was posted on Saturday, April 2nd, 2005 at 8:25 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.