OpenOffice Calc Tips

One of the most useful and widely used financial functions available in OpenOffice Calc is PMT. We have discussed this
before.

It’s use is illustrated below in a simple example showing how to calculate the payments on a loan with fixed interest rate over a specified repayment term.

The advantage of using a spreadsheet in this manner is that we can plug different parameters into the equation and instantly see how it affects the payment we would need to make.

I will now extend this capability by showing how to create a table of payments for a range of loan amounts and interest rates. We first set up a blank table with the desired loan amounts on the side and the interest rates along the top.

In cell G4, we enter the following formula

We have already used this technique to create a multiplication chart
here

Using the ‘hybrid’ addressing mode results in OOo Calc generating the correct results when we autofill the formulae down the first column. More information on the autofill mechanism
here.

Likewise, we can autofill the complete table by dragging the first column across with the fill handle as shown below.

The above example spreadsheet can be downloaded
here
(ODF format)

A balloon mortgage is a short loan, lasting five to seven years, but the payments are based on a term of 30 years. Balloon loans often have a lower interest rate, and can usually be easier to qualify for than the traditional 30 year mortgage. At the end of your loan term you will need to pay off your outstanding balance. This usually means a refinance, a home sale or a conversion to a traditional mortgage at the current interest rates.

In the spreadsheet below, we use the PMT and FV functions to calculate the payments and final payment for a 7 year 8% balloon loan for $100,000.

Let us now revise this worksheet with a “what if” scenario - so easy with OOo Calc. let us sweeten the payment each month and see how it impacts our final payment due.

The worksheet for this example can be downloaded here (OO0 2.0)

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)

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.

Often, in financial statements, we wish to express totals as ‘Thousands’ or ‘Millions’. In Calc, we use custom formatting to do this.

In the example below, we use the number 1234567 to illustrate how the custom formats affect how this is displayed.

Firstly, #,## controls the use of commas. Secondly, 0 controls the number of digits after the decimal point. Finally, the trailing commas control the rounding of the number to thousands and millions.

As a follow on from the previous tip, I show how to grab FOREX quotes from the Yahoo Finance website.

Here is the link to the Yahoo FOREX quote for the US dollar against the Japanese yen. A screen shot is shown below. At any given time, we are interested in two values for each currency pair, the bid and the ask.

The URL in this example is http://finance.yahoo.com/q?s=USDJPY=X and the table is HTML__yfncsumtab.

By selecting Edit - Links, you will get the Links popup editor.

With this, you canreview and modify existing Webquery links in your spreadsheet.

In a previous entry we imported data from an HTML file on the local disk. In this example, we will extract stock quotes from an MSN site and import the data into OOo Calc spreadsheet.

(more…)

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.

As a follow on from the mortgage example earlier, we now consider a small amount of money invested over a number of years at a fixed rate of interest.

The function we will use will be FV(rate, nper. pmt, pv)

At some point, you will want to use OOo Calc to perform some sort of financial analysis. Like other topics I cover, you could write a whole book on this. As an introduction to the topic of financial functions in OOo Calc, we will look at a home mortgage example.

(more…)