Financial Functions 3 : Complex Accumulation

October 7th, 2004

In this example, we consider a more complex accumulation - wherein we are making regular payments as well as earning interest on our initial investment.

Here, we define names for the cells containing the main variables - so that the formulas are more readable.

The purpose of the month-by-month breakdown of the account also serves to confirm the accuracy of our original formula.

Posted

Financial Functions: CUMIPMT & CUMPRINC

April 2nd, 2005

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 andCUMPRINC

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 in Finance | 1 Comment »

Financial Functions: Calculating Principal & Interest

April 1st, 2005

In this tip, we show how to determine the Principal and Interest components of a loan repayment using the built in OOo Calc functionsIPMT 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 nperargument.

· 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 <lifv The future value of the loan (desired) at the end of the nper payment periods. For mortgage payment calculations, this would normally be 0.</li

· 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.