OpenOffice Calc Tips

The WEEKDAY function returns an integer that represents the day of the week. The usefulness of this function is in identifying weekends in a series of dates.

It is straightforward to use the filldown functionality of Openoffice Calc to create a column of consecutive dates. We can use the WEEKDAY function to only include the days of the week we want - for example excluding weekends.

The arguments to the function are :

WEEKDAY(Number; Type)
where

• Number, as a date value, is a decimal for which the weekday is to be returned.
• Type determines the type of calculation. For Type =1, the weekdays are counted starting from Sunday (this is the default even when the Type parameter is missing). For Type =2, the weekdays are counted starting from Monday =1. For Type = 3, the weekdays are counted starting from Monday = 0.

In the example below, we use the WEEKDAY function to identify weekend days.

We now use the WEEKDAY function to create a column of dates that exclude the weekends. An alternative (but similar) approach is shown

here

The sheet for this example can be downloaded here

The FIND, LEFT, MID, RIGHT and LEN text functions are extremely useful and versatile. We have seen
before how to split a person’s name into it’s component parts.

Here, we take a single cell containing a US address and split it into it’s City, State and Zip components. As long as the string we want to partition is well beahved - as is the case here, splitting should be relatively straightforward with the appropriate combination of text functions.

If, for example, we did not have a comma after the city, we could use the formulae below to extract the city and state components…

Also, if you want to delete column B, you will first need to preserve the columns you have just created by doing a Paste Special (values only) as shown
here

This worksheet can be downloaded here

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)