Question 1

Which of the following functions will calculate the total principal paid between select periods?

Accepted Answer

CUMPRINC function calculates the cumulative principal paid on a loan between start and end periods.

Question 2

When using the MATCH function with an exact match lookup_value,the match type must be set to:

Accepted Answer

When using the MATCH function with an exact match lookup_value, the match type must be set to 0, which means the function will perform an exact match. Setting the match type to 1 or -1 will cause the function to perform an approximate match, which may not return the correct result. Setting the match type to 2 is only used when searching for the smallest value that is greater than or equal to the lookup value, which is also not applicable for an exact match.

Question 3

Which function will calculate the periodic interest rate of a loan with a $500 payment on a $20000 loan paid back over 5 years?

Accepted Answer

The RATE function can be used to calculate the periodic interest rate of a loan based on the payment amount, loan amount, and loan term. In this case, the payment amount is $500, the loan amount is $20000, and the loan term is 5 years. By using the RATE function with the appropriate inputs, the periodic interest rate can be calculated.

Question 4

Which of the following functions will return the principal payment of a loan?

Accepted Answer

PPMT function calculates the principal payment on a loan for a specified period. CUMPRINC calculates the cumulative principal paid between two specified periods. PMT calculates the periodic payment for a loan, while IPMT calculates the interest payment for a specified period.

Question 5

Which function will calculate the current value on an investment that will earn 5% interest on yearly payments of $100 over 10 years?

Accepted Answer

The answer of Which function will calculate the current value...

Question 6

If there are four possible outcomes,how many logical tests are required?

Accepted Answer

To determine a specific outcome among four possible outcomes, a minimum of three logical tests or questions (each dividing the set of possibilities roughly in half) is required. This is based on the binary search algorithm principle.

Question 7

Which of the following functions will calculate the total interest paid between select periods?

Accepted Answer

CUMIPMT (cumulative interest payment) is the function used to calculate the total interest paid between select periods.

Question 8

Which of the following logical functions will return a value if false if one of the arguments is false?

Accepted Answer

The logical function AND will return a value of false if one of the arguments is false. It requires all arguments to be true for it to return a true value.

Question 9

Which database function will return the largest value from a dataset based on preset criteria?

Accepted Answer

DMAX is the database function that returns the largest value from a dataset based on preset criteria. DCOUNT counts the number of values in a dataset based on preset criteria, DSUM adds up the values in a dataset based on preset criteria, and DMIN returns the smallest value from a dataset based on preset criteria.

Question 10

Which of the following functions does not support multiple conditions?

Accepted Answer

MATCH function is used to find the position of a lookup value in a range, it does not support multiple conditions. AND and OR functions are used for logical tests and can support multiple conditions, while NOT function is used to invert a logical value.

Question 11

Which function will return the total number of periods on a $20000 loan at 5% interest and a $600 payment?

Accepted Answer

The NPER function calculates the total number of periods required to pay off a loan, given a fixed payment amount, interest rate, and present value. Therefore, to calculate the total number of periods on a $20000 loan at 5% interest and a $600 payment, we need to use the NPER function.

Question 12

Which function will return the intersection of a specific row and column?

Accepted Answer

The INDEX function returns the value at the intersection of a specific row and column. You just need to provide the array or range, and the row and column numbers to specify the position of the value you want to retrieve.

Option A (VLOOKUP) and option B (HLOOKUP) are not ideal choices, as they are designed to retrieve data from a specific column or row based on a match with a lookup value. Option D (MATCH) is also not the best choice, as it only returns the position of the specified value within a range or array, rather than the value itself.

Question 13

Which of the following has the ability to restrict data view to only information that fits predetermined criteria?

Accepted Answer

Advanced Filter has the ability to restrict data view to only information that fits predetermined criteria. VLOOKUP, HLOOKUP, and INDEX are lookup functions and do not have this capability.

Question 14

Which function will calculate the final value of an investment of $100 a month that earns 4% interest over 5 years?

Accepted Answer

The FV (Future Value) function is used to calculate the final value of an investment over a period of time, given a regular payment and a fixed interest rate. In this case, the investment is $100 per month, the interest rate is 4%, and the investment period is 5 years. Therefore, the FV function is the best choice to calculate the final value of the investment.

Question 15

Which of the following functions will return the interest payment of a loan?

Accepted Answer

The IPMT function calculates the interest payment of a loan.

Question 16

Which database function will return the smallest value from a dataset based on preset criteria?

Accepted Answer

DMIN is the database function that will return the smallest value from a dataset based on preset criteria. DCOUNT counts the number of records that meet specific criteria, DSUM will add up the values from a dataset based on preset conditions, and DMAX will return the largest value from a dataset based on preset criteria.

Question 17

What is the first argument of the IF function?

Accepted Answer

The first argument of the IF function is the logical test, which is a true or false statement. It is used to determine which value should be returned based on the result of the logical test.

Question 18

Which of the following functions requires all arguments to be true in order to return the value if true?

Accepted Answer

The AND function requires all arguments to be true in order to return a true value.

Question 19

What is the second argument of the IF function?

Accepted Answer

The second argument of the IF function is the value that is returned if the condition (logical_test) is TRUE.

Question 20

Which of the following is a nested function?

Accepted Answer

A nested function in Excel is a function that is used as one of the arguments of another function. In choice A, the AND function is nested inside the IF function.

Quiz 21: Specialized Functions