Transcript Logical, financial, and date functions in Excel 2003

Logical, financial, and date
functions in Excel 2003
Part 4
(file: InsertingFunctions.xls)
Using logical functions
Using financial functions
Using data functions
Summary
Using logical functions
• In addition to basic arithmetic functions, Excel 2003
incorporates a variety of more complex functions that
allow you to make logical, financial, and date-related
calculations.
Excel 2003 supports several logical functions
• AND
– The AND function returns TRUE if all its arguments are TRUE, and
it returns FALSE if one or more arguments is FALSE.
• FALSE
– The FALSE function returns the logical value FALSE.
• IF
– The IF function returns one value if the specified condition is TRUE,
and another value if it is FALSE.
• NOT
– The NOT function reverses the value of its argument.
So if the argument is FALSE, the NOT function returns TRUE.
And if the argument is TRUE, it returns FALSE.
• OR
– The OR function returns TRUE if one or more arguments is TRUE,
and FALSE if all arguments are FALSE.
• TRUE
– The TRUE function returns the logical value TRUE.
Function Arguments: IF function
The logical test is the condition that can be either true or false. The result of
the IF statement depends on the outcome of the logical test.
Suppose that you wish to determine whether a column of sales figures
meets a target set by management.
If the sales figures meet the target, you want the phrase "Met target" to
display below the column.
If the sales figures miss the target, you want the phrase "Missed target" to
display below the column.
Select Insert - Function. Select Logical category. Select IF
In the Logical_test box specify the condition that will be
incorporated into the IF statement. This is whether the sum of
the figures in the relevant column (B5:B8) exceeds 35,000
Question
Which of the following is the correct form of an IF statement that
tests whether the average value of a range of cells is less than
10, and displays particular output depending on the outcome?
Options:
1.
=IF(AVERAGE(A1:A4)<10,"Within limit","Outside limit")
2.
IF(AVERAGE(A1:A4)<10,"Within limit","Outside limit")
3.
=IF AVERAGE(A1:A4)<10,"Within limit","Outside limit"
4.
=IF(AVERAGE A1:A4 <10,"Within limit","Outside limit")
Using financial functions : Determining the payment for a loan Calculating the time value of an investment
• Payment (PMT)
– The PMT function calculates the payment for a loan based on constant
payments and a constant interest rate.
• Rate (RATE)
– The RATE function returns the interest rate per period of a loan or an
investment. For example, use 6%/4 for quarterly payments at 6% APR.
• Number of periods (NPER)
– The NPER function returns the number of payment periods for an
investment based on periodic, constant payments and a constant interest
rate.
• Future value (FV)
– The FV function returns the future value of an investment based on periodic,
constant payments and a constant interest rate.
• Present value (PV)
– The PV function returns the present value of an investment, meaning the
total amount that a series of future payments is worth now.
• Interest payment (IPMT)
– The IPMT function returns the interest payment for a given period for an
investment based on periodic, constant payments and a constant interest
rate.
• Depreciation (VDB)
– The VDB function returns the depreciation of an asset for any period you
specify, including partial periods, using the double-declining balance method
or some other method you specify.
Example
Ex
Select Insert - Function. Select
Financial category.
Question
You want to find out how much the
investment will be worth at the end of the
year.
Which function should you select?
Options:
1.
2.
3.
4.
DB
DDB
FV
IPMT
Question
Suppose that you want to use the PMT function to calculate the
monthly payment of a $50,000 loan. The term of the loan is 24
months, with an annual interest rate of 10%. Payments are made
at the end of the month.
Given that the syntax of the PMT function is
=PMT(Rate,Nper,Pv,Fv,Type), which expression correctly
performs this calculation?
Options:
1.
PMT(10%/12,24,-50000,0,0)
2.
=PMT(10%/12,24,-50000,0,0)
3.
=PMT 10%/12,24,-50000,0,0
4.
=PMT(10%*12,24,-50000,0,0)
(The interest rate per month is 10% divided by 12. The number of
payments is 24, the present value is $50,000, the future value is
$0, and payments are made at the end of the month. )
Using date functions
You can use them to calculate when a document is updated or expires.
Using date functions
Using date functions
Question
Which function displays the current date
and time in the selected cell?
Options:
1.
2.
3.
4.
DATE
NOW
TIME
TODAY
Summary
• Excel incorporates a range of logical functions. Used
in combination with the IF function, these enable you
to perform complex logical tests.
Excel features a range of financial functions that
enable you to carry out a number of common financial
calculations easily. For example, you can calculate the
future value of an investment.
Excel also includes a range of date and time functions.
These enable you, for example, to display the current
date and time in a cell that is automatically updated
when particular events occur.
Inserting Functions: Hands-On Exercise
The office is selling off some of its assets that are no longer in
use. The proceeds of the asset sale will start an investment
fund to which the company will then make subsequent monthly
contributions for at least two years.
Task 1: Calculate a sales total (file:InsertingFunctions.xls)
Task 2: Assessing a sales target
Task 3: Accessing function arguments
Task 4: Specifying function arguments
Results