The original mortgage loan was for $300,000 with a 5% APR for 30 years. You want to calculate the interest on the last monthly payment at the end of the 15th year. What value should be referenced for the per argument in the IPMT function? Hint: Number of payment periods in a year is 12.

The original mortgage loan was for $300,000 with a 5% APR for 30 years. You want to calculate the interest on the last monthly payment at the end of the 15th year. What value should be referenced for the per argument in the IPMT function? Hint: Number of payment periods in a year is 12.

a. 180
b. 15
c. 30
d. 0.05/12


Answer: A. 180

You workbook contains a list of artwork. Column B lists Sold Out if the piece is sold out, Available if it is still readily available, or Limited if only a few pieces are available. In the third column, you want to display TRUE if the art is not sold out. What function would produce the same results as =OR(B1="Available", B1="Limited")?

You workbook contains a list of artwork. Column B lists Sold Out if the piece is sold out, Available if it is still readily available, or Limited if only a few pieces are available. In the third column, you want to display TRUE if the art is not sold out. What function would produce the same results as =OR(B1="Available", B1="Limited")?

a. =AND(B1="Available", B1="Limited")
b. =TRUE(B1<>"Sold Out")
c. =IF(B1="Sold Out", "TRUE", "FALSE")
d. =NOT(B1="Sold Out")


Answer: d. =NOT(B1="Sold Out")

The date 5/12/2018 is stored (in format MONTH/DAY/YEAR) in cell C1. What function should you use to extract just 12?

The date 5/12/2018 is stored (in format MONTH/DAY/YEAR) in cell C1. What function should you use to extract just 12?

a. =YEAR(C1)
b. =MONTH(C1)
c. =DAY(C1)
d. =DAYS(B1, C1)


Answer: c. =DAY(C1)

Today's date (February 12, 2018) is stored in cell B1. The last day of the semester (May 4, 2018) is stored in cell B2. You want to know how many days until the end of the semester. Which function should you use?

Today's date (February 12, 2018) is stored in cell B1. The last day of the semester (May 4, 2018) is stored in cell B2. You want to know how many days until the end of the semester. Which function should you use?

a. =YEARFRAC(B1, B2)
b. =DAY(B1)
c. =DAYS(B2, B1)
d. =DAYS(B1, B2)


Answer: c. =DAYS(B2, B1)

When you are performing an advanced filter, where do you enter the rage for the dataset?

When you are performing an advanced filter, where do you enter the rage for the dataset? 

A. Output range
B. List range
C. Criteria range
D. Copy to range


Answer: B. List range

A local police office wants to create a rule that if an officer pulls over a person for exceeding the speed limit by at least five miles per hour or if that person has two or more speeding violations on record, the officer will fine the speeder the higher of $200 or $50 for each mile over the speed limit. Otherwise, the fine is $45. The speed limit is entered in cell B5, the person's speed is entered in cell B10, and the person's number of previous tickets is entered in cell B11. What function derives the correct answer?

A local police office wants to create a rule that if an officer pulls over a person for exceeding the speed limit by at least five miles per hour or if that person has two or more speeding violations on record, the officer will fine the speeder the higher of $200 or $50 for each mile over the speed limit. Otherwise, the fine is $45. The speed limit is entered in cell B5, the person's speed is entered in cell B10, and the person's number of previous tickets is entered in cell B11. What function derives the correct answer?

a. =IF(OR(B10-B5>=5, B11>=2), MAX(200, (B10-B5)*50), 45)
b. =IF(OR(B10>B5, B11>=2), MAX(200, 50), 45)
c. =IF(AND(B10-B5>=5, B11<2), MAX(200, (B10-B5)*50), 45)
d. =IF(AND(B10>B5, B11>=2), 200, 45)


Answer: a. =IF(OR(B10-B5>=5, B11>=2), MAX(200, (B10-B5)*50), 45)

What function would you use to calculate the total (i.e., cumulative) interest paid for the first year of a mortgage?

What function would you use to calculate the total (i.e., cumulative) interest paid for the first year of a mortgage?

a. CUMIPMT
b. IPMT
c. CUMPRINC
d. PPMT


Answer: a. CUMIPMT

A worksheet contains the times in which runners completed a race, with the times organized from fastest to slowest. You will use the MATCH function to identify what place a runner came in given a time of 4:05 (four minutes and five seconds). Which argument should contain the specific runner's time?

A worksheet contains the times in which runners completed a race, with the times organized from fastest to slowest. You will use the MATCH function to identify what place a runner came in given a time of 4:05 (four minutes and five seconds). Which argument should contain the specific runner's time?

a.
Lookup_value
b.
Row_num
c.
Match_type
d.
Lookup_array


Answer: a. Lookup_value

What function would you use to calculate the total number of periods in a loan or investment?

What function would you use to calculate the total number of periods in a loan or investment?

A. FV
B. PV
C. NPER
D. RATE


Answer: C. NPER

A workbook contains a list of members in the Computer Club. You want to identify the number of students who are freshmen who are Information Systems majors. Without filtering the dataset, what function should you use?

A workbook contains a list of members in the Computer Club. You want to identify the number of students who are freshmen who are Information Systems majors. Without filtering the dataset, what function should you use?

a. DCOUNT
b. DSUM
c. COUNT
d. COUNT


Answer: A. DCOUNT

Calculates the number of periods for an investment or loan given a fixed rate, period payment, and present value.

Calculates the number of periods for an investment or loan given a fixed rate, period payment, and present value.

Answer: NPER function

What is the function that returns the RELATIVE position of a value? Index, Match, HLOOKUP or VLOOKUP ?

What is the function that returns the RELATIVE position of a value?
Index, Match, HLOOKUP or VLOOKUP ?

Answer: It's MATCH fam

Why would you nest MATCH in an INDEX function?

Why would you nest MATCH in an INDEX function?

Answer: To use the position that was returned from the MATCH function as an argument in the INDEX function

Which of the following is not an argument in a Database function?

Which of the following is not an argument in a Database function? 

A. Database
B. Criteria
C. Filter
D. Field


Answer: C. Filter

What's the purpose of an Excel What-if scenario?

What's the purpose of an Excel What-if scenario?

Answer: It's a detailed set of values that represent different possible situations

Why would you put a negative sign in many financial functions?

Why would you put a negative sign in many financial functions?

Answer: Excel interprets these calculations as a negative cash flow (money leaving your account)

T or F: The INDEX function results the position of a value.

T or F: The INDEX function results the position of a value.

Answer: False

T or F: The arguments of an INDEX function are array, row_num, and column_num.

T or F: The arguments of an INDEX function are array, row_num, and column_num.

Answer: True

T or F: Advanced filtering will not copy results to a new location.

T or F: Advanced filtering will not copy results to a new location.

Answer: False

True or False: The RATE function returns periodic rate for an investment or loan.

True or False: The RATE function returns periodic rate for an investment or loan.

Answer: True