Sams Teach Yourself StarOffice® 5 for Linux in 24 Hours

ContentsIndex

Hour 15: Using Formulas, Functions, and Names

Previous HourNext Hour

Sections in this Hour:

 

Using More Advanced Functions


In this section, you'll see an example of a financial function provided by StarOffice. The functions that are part of StarOffice include specialized scientific and complex mathematical functions that will meet almost any calculating need that you might have in a spreadsheet. (When was the last time you needed to calculate an arctangent?)

Many of the advanced functions require that you have a spreadsheet set up with certain information as background. A less-involved function is used as an example to show you the power of these functions.

Using a Financial Function

Suppose you want a little mortgage calculator spreadsheet to help you decide how much you can afford for a house that you want to purchase.

Set up a few fields to fill in for principal, number of years in the loan, and annual interest rate. The payment field has been left so that you can calculate it using a spreadsheet function.

You can arrange this in several different ways. For this example, keep changing the principal and interest rates until you see a payment that you can handle. The cell information that you'll enter will look similar to Figure 15.9. The numbers shown here were actually typed in, not computed using any formulas.

Figure 15.9
The fields for a sample financial calculation include principal, interest rate, and time.

Note - You'll learn how to do the currency and boldface formatting in Hour 16, "Formatting Your Spreadsheet."


Use a principal amount of $120,000 for the loan. This example estimates that you can get the loan for 7.0 percent annual interest, and assumes that you're planning for a 30-year loan.

Now you can use a function to calculate the payment:

1. Click on cell C8, to the right of the Payment label.

2. Choose Function from the Insert menu.

3. Select Financial from the Category list in the Function Autopilot dialog box.

4. Select PMT from the Function list.

5. Choose the Next>> button. The dialog box containing the required fields appears (see Figure 15.10).

Figure 15.10
The fields for the PMT function can be entered in this screen of the Function AutoPilot dialog box.

6. Move the dialog box to a place on your screen so that you can see the cells you've already entered (as in the previous figure).

7. The description of the Rate field tells us that this is the rate per period, which means monthly because you're making monthly payments. Type this in the Rate field (changing the cell number of the Interest rate value, if necessary, to match your screen). Don't press Enter yet or the Function AutoPilot will close prematurely.


(C5/100)/12

Note - Dividing by 100 makes the interest rate (7) into a percentage (.07). Dividing by 12 makes it a monthly rate instead of an annual rate.


8. Move to the nper field of the dialog box. This is the number of payments. Because you have a 30-year mortgage planned, this is 30*12. But to make it interesting, click on the cell in your spreadsheet that contains 30 (C6). The cell reference appears in the nper field. Now add *12 to the cell reference so that it says something similar to the following:


C6*12

9. Move to the PV, or principal value, field of the dialog box.

10. Click on the principal value cell in the spreadsheet (on the number 120,000, cell C4). The cell reference appears in the field.

11. Move to the FV, or Future Value, field and enter 0 (the bank wants the loan paid off at the end of this exercise).

12. Choose OK to close the Function Autopilot dialog box, with the function completed. The payment, computed by the function, appears next to the Payment label, in cell C8 (see Figure 15.11).

Figure 15.11
The PMT function provides the mortgage payment based on other information in the function.

Note - In mortgage calculations, the principal value and the payment always have opposite signs (one negative and one positive) to indicate that you receive one and pay the other. Therefore, the PMT value is a negative number.


13. Try changing the principal value and see how the Payment changes.

Caution - If you've switched automatic recalculation off, remember that you'll need to manually recalculate each time you make a change in order for the correct result to appear.


Caution - From the fields in this example, it's obvious that you have to know something about how to use a complex function before it can work well, even with the Function AutoPilot to guide you. Be careful using functions until you understand them well.


Sams Teach Yourself StarOffice® 5 for Linux in 24 Hours

ContentsIndex

Hour 15: Using Formulas, Functions, and Names

Previous HourNext Hour

Sections in this Hour: