Formulas/Functions
for Excel and Quattro Pro
Use the back button on your Internet browser to return to the previous page.

Formulas are used to perform calculations and start with an = (equal) sign in Excel and an @ sign in Quattro Pro.  A formula that adds a column might look like this: =B5+B6+B7+B8+B9. 
Functions are formulas that simplify complex work.  A function to add the same cells might look like this: =SUM(B5:B9).  In other words, if you have a long column to add you might want to use a function rather than enter each cell as in the previous formula =B5+B...example.  Don't forget to add the = sign for Excel or @ sign for Quattro Pro when you enter the calculation.  The colon (:) is used when you want to reference a row or list of cells.  B5 through B9 would be written B5:B9.

SUM is used to add the values of specified cells. Quattro Pro has a QuickSum and Excel has an AutoSum function that will allow you to total a single row or column, multiple rows or columns, or selected rows and columns.  You can also type in the cell reference numbers rather than use the QuickSum or AutoSum functions.

AVERAGE or AVG, MIN, and MAX are basic functions that return the average, lowest, and highest values, respectively, from an argument list.

The IF formula is a difficult concept to learn.  The HELP section of Microsoft and Corel's spreadsheet software applications has detailed IF command instructions.  The following example is a simple IF command (only key in the formula within the parenthesis not the explanation in italics behind it). 

An IF command asks a question and answers that question with both a yes and no answer. 

Suppose that you want to add information to your spreadsheet data that returns a true or false statement such as:  "If " you want to be able to show on your spreadsheet whether Mary passes or fails a test.  The formula to place in cell C2 is:  =IF(B2>80,"Pass","Fail") = Pass if B2 is more than 80; otherwise, Fail.  This means that if you put a score of 79 in the B2 cell, then the cell (C2) where you set up the formula will show Fail.   If the score is 89 then cell C2 will show Pass.  You set this formula up in cell C2 but can choose any cell other than the cell where you have the grade [QuattroPro @IF(B2>80,"Pass","Fail") and Excel =IF(B2>80,"Pass","Fail")].

The mini spreadsheet below shows the result.  The formula will not show on the sheet, just the answer will show.  If you want formulas to print, change the print settings. The diagram below the spreadsheet is an Excel formula bar (located at the top of the screen) to show what needs to be keyed in to create the correct function.   The command can also be keyed in directly on the spreadsheet.

The logical test simply means a question asked.  The Value if true gives a true answer and the Value if false gives the false answer.  Thought of this way simplifies the IF command and makes it easy to understand.

Excel IF Function
back to top

The IF command gives a True and False (yes and no) response:  The Excel =IF command returns one value if a condition specified evaluates to TRUE and another value if it evaluates to FALSE.   Use IF to conduct conditional tests on values and formulas.

A formula can be written to check whether a budget is over or under for a particular time period.  If a spreadsheet has been created that gives certain criteria for a budget, and if certain cells (B2 and C2) are referenced, then a typical IF command might look like this:

=IF(B2>C2,"Over Budget","OK") the result will equal "Over Budget"

Commas separate one part of an IF function from another part in both Excel and Quattro Pro.   

Quattro Pro IF Function
back to top

@IF - Performs a logical test. Syntax: @IF(Cond, TrueExpr, FalseExpr)
Cond = A logical expression representing the condition to be tested.
TrueExpr = A numeric or string value representing the value to use if Cond is true.
FalseExpr = A numeric or string value representing the value to use if Cond is false.
@IF evaluates the logical condition specified as Cond. If the condition is found to be true, it returns the value specified as TrueExpr. If the condition is false, it returns the value specified as FalseExpr. Cond is true if it evaluates to any nonzero numeric value.

The formula entered as Cond can be any logical expression that can be evaluated as true or false; for example, B6<0 or C3*D2=53.

Although logical expressions typically reference other cells, this is not required. Any expression resulting in a numeric value is accepted; for example, A1=1 or A1="Fred". If the result of Cond is nonzero, TrueExpr is the result; otherwise, FalseExpr is the result.

Examples:

@IF(8=7,4,5) = 5 (This formula does not reference a cell.  The other three examples do)

@IF(B4<=100,"Yes","No") = If the number in cell B4 is less than (< sign for less than) or equal to (=) 100 then the answer is Yes; otherwise it is No

@IF(C10=BLOCK,45,50) = 45 if C10 = the cell named BLOCK; otherwise, 50

@IF(C10,1,0) = 0 if C10 = 0; otherwise, 1

Excel PMT Function
back to top

Syntax: =PMT(rate,nper,pv,fv,type)  Calculates the payment for a loan based on constant payments and a constant interest rate.

Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If Fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type is the number 0 (zero) or 1 and indicates when payments are due.

The payment returned by =PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.

Make sure that the units used for specifying rate and Nper are consistent. If monthly payments are made on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for Nper. If annual payments are made on the same loan, use 12 percent for rate and 4 percent for Nper.

Tip: To find the total amount paid over the duration of the loan, multiply the returned =PMT value by Nper.

Examples:
The following formula returns the monthly payment on a $10,000 loan at an annual rate of 8 percent that you must pay off in 10 months:

=PMT(8%/12, 10, 10000) equals -$1,037.03 (you divide by 12 because you pay 8% per year not per month).

For the same loan, if payments are due at the beginning of the period, the payment is:

=PMT(8%/12, 10, 10000, 0, 1) equals -$1,030.16

The following formula returns the amount someone must pay to you each month if you loan that person $5,000 at 12 percent interest and want to be paid back in five months:

=PMT(12%/12, 5, -5000) equals $1,030.20

=PMT(C5/12,D5,-B5) this formula is used when you have a list of prices and payments and want to copy the formula to other cells (remember to put the minus (-) sign before the price of the item).

=PMT can be used to determine payments to annuities other than loans. For example, if a person wants to save $50,000 in 18 years by saving a constant amount each month, =PMT can be used to determine how much must be saved. If an assumption is made that 6 percent interest can be earned on savings, =PMT can be used to determine how much to save each month.

=PMT(6%/12, 18*12, 0, 50000) equals -$129.08

If a person pays $129.08 into a 6 percent savings account every month for 18 years, the result will be $50,000.

Print this page and key in the information in the columns and rows in either Excel or QuattroPro.   Remember that QuattroPro uses the @ sign and Excel uses the = sign to start a formula or function.   Try typing in other numbers (change the interest rate and price) to see what will happen when you have the correct formula entered. 

Quattro Pro PMT Function
back to top

Syntax: @PMT(Pv, Rate, Nper)  @PMT is an amortized payment.
Pv = A numeric value representing the amount borrowed (the principal).
Rate = A numeric value > -1, representing the periodic interest rate (the fixed interest rate per compounding period).
Nper = A numeric value > 0, representing the number of periods of the loan (the number of payments to be made) or investment (the number of compounding periods).
@PMT calculates the fully amortized periodic payment needed to repay a loan with a principal of Pv dollars at Rate percent per period over Nper periods. It assumes that interest is paid at the end of each period and the investment is an ordinary annuity (not an annuity due).

@PMT uses a formula where,
P = principal
R = periodic interest rate
N = number of periods

You can enter the value for Rate as a percent or a decimal; for example, 9.5% or .095. The amount you specify for Rate must correlate with the unit used for Nper. In other words, if payments are made and interest calculated annually, the amount entered for Nper must represent years. If monthly, Nper must represent the number of months the loan covers. To calculate monthly payments using an annual interest rate, divide the interest rate by 12.

@PMT assumes that the investment is an ordinary annuity.
Related @ functions: @PAYMT, @IPAYMT, and @PPAYMT let you use an optional argument, Type, to indicate whether the investment is an ordinary annuity or an annuity due.
@PMTC calculates payments based on semi-annual compounding.

Examples:
To calculate a monthly payment (paid on the last day of the month) for a three-year loan of $10,000 at an annual 15% interest rate, enter:

@PMT(10000,15%/12,3*12) = $346.65

You can also use @PAYMT to figure this payment (the negative result means the money is out of your pocket): PAYMT(15%/12,3*12,10000,0,0) = $-346.65

Other examples:

@PMT(1000,0.12,5) = $277.41

@PMT(500,0.16,12) = $96.21

@PMT(5000,16%/12,12) = $453.65

@PMT(12000,0.11,15) = $1,668.78

@PMT(10000,15%/12,36) calculates a monthly payment for a three-year loan of $10,000 at an annual 15% interest rate.

Note:  The basic function descriptions are all available in detail from the HELP menu in Quattro Pro and Excel.  Some of the information on this page is taken from both HELP menus and is added as a supplement to the student's competency course and exam preparation.

back to top

page access since February 1, 2000