CGS 2518 FINAL EXAM STUDY GUIDE
QUESTIONS AND ANSWERS
Which Excel function should you use when you want to look up a value from a two-
dimensional table, where both the columns and rows can be varied? - Answer-INDEX
Write a formula to choose the third day of the week from the list starting with Sunday,
Monday, Tuesday,...Saturday - Answer-
=CHOOSE(3,"SUNDAY","MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRID
AY","SATURDAY")
What function returns the relative position of an item from a list? - Answer-MATCH
Write a formula in cell C13 in the Pricing worksheet to determine the total cost of
making copies for this order (275 copies). Write a formula so that it works when copied
into cells C14:C15. - Answer-=VLOOKUP(B13,$B3:$C9,2,TRUE)*B13
Function to calculate the value at the end of a financial transaction? - Answer-FV
Function to calculate the interest percentage per period of a financial transaction -
Answer-RATE
Function to calculate the value at the beginning of a financial transaction. - Answer-PV
Function to calculate the number of a compounding periods in a financial transaction. -
Answer-NPER
Function to calculate periodic payments into or out of a financial transaction - Answer-
PMT
Use a 0 for this argument to indicate that interest will be paid at the end of each
compounding period. - Answer-Type
This type of interest is calculated based on original principal regardless of the previous
interest earned. - Answer-Simple Interest
This type of interest is calculated based on principal and previous interest earned. -
Answer-Compound Interest
Function to calculate straight line depreciation based on the initial capital investment,
number of years to be depreciated, and salvage value. - Answer-SLN
Function to calculate the cumulative interest paid between two period - Answer-
CUMIPMT
, Function to calculate the amount of a periodic payment that is interest in a given period.
- Answer-IPMT
Function to calculate the amount of a specific periodic payment that is principal in a
given period. - Answer-PPMT
Function to determine the value of a variable set of cash flows discounted to its present
value - Answer-NPV
Function to determine the rate of return, when the net present value of the cash flows is
0 - Answer-IRR
Assume that you have been left an inheritance and want to save part of it toward the
purchase of a car upon graduation, which is three years from now. Write an Excel
formula to determine the amount of money you need to invest now to have $17,000 at
the end of the three-year period. Assume that you will place this money in a CD that
pays 2% interest compounded quarterly and that you will be making no additional
deposits into this account. - Answer-=PV(2%/4,3*4,0,17000)
Write an Excel formula to determine the yearly interest rate being charged by the bank
on a $400,000, 20-year mortgage. You make a monthly mortgage payment of $3,400,
and the value of the loan at the end of 20 years is 0. Interest is compounded monthly. -
Answer-=RATE(20*12,-3400,400000,0,0)
Assume that you are buying a car for $25,500 with a $4,000 down payment, and you
are borrowing the rest from a bank at 5.5% annual interest compounded monthly. Your
monthly payments are $400. Write an Excel formula to determine the number of years it
will take you to pay off this loan. - Answer-=(NPER(5.5%/12,-400,21500,0))/12
Consider a $150,000 mortgage at 5% annual interest compounded monthly, to be paid
back over the next 30 years. The loan will have a $5,000 balloon payment due at the
end of the loan. Write an Excel formula to determine the payments that must be made
each month on this loan. - Answer-=PMT(5%/12,30*12,150000,-5000)
Assume that you are investing $3,000 in a savings plan today and will make additional
contributions of $300 per quarter. The plan pays 3% interest per year compounded
quarterly at the beginning of each period. Write an Excel formula to determine how
much your savings will be worth in five years. - Answer-=FV(3%/4,5*4,-300,-3000,1)
Write an Excel formula to determine the amount of money that can be depreciated each
year, using straight line depreciation, for a new packaging machine purchased by your
company. The machine originally cost $150000 and has a useful life of 5 years and an
estimated salvage value of $5,000. - Answer-=SLN(150000,5000,5)
QUESTIONS AND ANSWERS
Which Excel function should you use when you want to look up a value from a two-
dimensional table, where both the columns and rows can be varied? - Answer-INDEX
Write a formula to choose the third day of the week from the list starting with Sunday,
Monday, Tuesday,...Saturday - Answer-
=CHOOSE(3,"SUNDAY","MONDAY","TUESDAY","WEDNESDAY","THURSDAY","FRID
AY","SATURDAY")
What function returns the relative position of an item from a list? - Answer-MATCH
Write a formula in cell C13 in the Pricing worksheet to determine the total cost of
making copies for this order (275 copies). Write a formula so that it works when copied
into cells C14:C15. - Answer-=VLOOKUP(B13,$B3:$C9,2,TRUE)*B13
Function to calculate the value at the end of a financial transaction? - Answer-FV
Function to calculate the interest percentage per period of a financial transaction -
Answer-RATE
Function to calculate the value at the beginning of a financial transaction. - Answer-PV
Function to calculate the number of a compounding periods in a financial transaction. -
Answer-NPER
Function to calculate periodic payments into or out of a financial transaction - Answer-
PMT
Use a 0 for this argument to indicate that interest will be paid at the end of each
compounding period. - Answer-Type
This type of interest is calculated based on original principal regardless of the previous
interest earned. - Answer-Simple Interest
This type of interest is calculated based on principal and previous interest earned. -
Answer-Compound Interest
Function to calculate straight line depreciation based on the initial capital investment,
number of years to be depreciated, and salvage value. - Answer-SLN
Function to calculate the cumulative interest paid between two period - Answer-
CUMIPMT
, Function to calculate the amount of a periodic payment that is interest in a given period.
- Answer-IPMT
Function to calculate the amount of a specific periodic payment that is principal in a
given period. - Answer-PPMT
Function to determine the value of a variable set of cash flows discounted to its present
value - Answer-NPV
Function to determine the rate of return, when the net present value of the cash flows is
0 - Answer-IRR
Assume that you have been left an inheritance and want to save part of it toward the
purchase of a car upon graduation, which is three years from now. Write an Excel
formula to determine the amount of money you need to invest now to have $17,000 at
the end of the three-year period. Assume that you will place this money in a CD that
pays 2% interest compounded quarterly and that you will be making no additional
deposits into this account. - Answer-=PV(2%/4,3*4,0,17000)
Write an Excel formula to determine the yearly interest rate being charged by the bank
on a $400,000, 20-year mortgage. You make a monthly mortgage payment of $3,400,
and the value of the loan at the end of 20 years is 0. Interest is compounded monthly. -
Answer-=RATE(20*12,-3400,400000,0,0)
Assume that you are buying a car for $25,500 with a $4,000 down payment, and you
are borrowing the rest from a bank at 5.5% annual interest compounded monthly. Your
monthly payments are $400. Write an Excel formula to determine the number of years it
will take you to pay off this loan. - Answer-=(NPER(5.5%/12,-400,21500,0))/12
Consider a $150,000 mortgage at 5% annual interest compounded monthly, to be paid
back over the next 30 years. The loan will have a $5,000 balloon payment due at the
end of the loan. Write an Excel formula to determine the payments that must be made
each month on this loan. - Answer-=PMT(5%/12,30*12,150000,-5000)
Assume that you are investing $3,000 in a savings plan today and will make additional
contributions of $300 per quarter. The plan pays 3% interest per year compounded
quarterly at the beginning of each period. Write an Excel formula to determine how
much your savings will be worth in five years. - Answer-=FV(3%/4,5*4,-300,-3000,1)
Write an Excel formula to determine the amount of money that can be depreciated each
year, using straight line depreciation, for a new packaging machine purchased by your
company. The machine originally cost $150000 and has a useful life of 5 years and an
estimated salvage value of $5,000. - Answer-=SLN(150000,5000,5)
