CGS 2518 FINAL, CH. #5 - 9, FSU EXAM
QUESTIONS WITH COMPLETE
SOLUTIONS
Explain the difference between the lookup table in cells A3:E4 of the Grades worksheet
and the lookup table in cells A6:E7 in the same worksheet. - Answer-The lookup table in
A3:E4 of the Grades worksheet is sorted in descending order, and the lookup table in
cells A6:E7 is sorted in ascending order, which allows you to use the HLOOKUP
function with a TRUE lookup type.
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 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 periods - Answer-
CUMPMT
Function to calculate the amount of 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, where the net present value of the cash flow 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(.02/4,4*3,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)
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(.055/12,-400,25500,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 $5,00 balloon payment due at the end of
the loan. Write an Excel formula to determine the payment that must be made each
month on this loan. - Answer-=PMT(.05/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 you savings will be worth in five years. - Answer-=FV(.03/4,5*4,300,-3000)
Write an Excel formula to determine the amount of money that ban be depreciated each
year, using straight line depreciation, for a new packaging machine purchased by your
company. The machine originally cost $150,000 and has a useful life of 5 years and an
estimated salvage value of $5,000. - Answer-=SLN(150000,5000,5)
List and deceive the steps you would take to create a structured list of data from a text
file that contains values stored on separate lines - Answer-1. Click the DATA tab on the
ribbon and then file from text
2. Find your text
3. Make sure it is delimited
4. Select which delimiters you will use (commas)
5. Select the data format
QUESTIONS WITH COMPLETE
SOLUTIONS
Explain the difference between the lookup table in cells A3:E4 of the Grades worksheet
and the lookup table in cells A6:E7 in the same worksheet. - Answer-The lookup table in
A3:E4 of the Grades worksheet is sorted in descending order, and the lookup table in
cells A6:E7 is sorted in ascending order, which allows you to use the HLOOKUP
function with a TRUE lookup type.
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 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 periods - Answer-
CUMPMT
Function to calculate the amount of 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, where the net present value of the cash flow 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(.02/4,4*3,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)
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(.055/12,-400,25500,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 $5,00 balloon payment due at the end of
the loan. Write an Excel formula to determine the payment that must be made each
month on this loan. - Answer-=PMT(.05/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 you savings will be worth in five years. - Answer-=FV(.03/4,5*4,300,-3000)
Write an Excel formula to determine the amount of money that ban be depreciated each
year, using straight line depreciation, for a new packaging machine purchased by your
company. The machine originally cost $150,000 and has a useful life of 5 years and an
estimated salvage value of $5,000. - Answer-=SLN(150000,5000,5)
List and deceive the steps you would take to create a structured list of data from a text
file that contains values stored on separate lines - Answer-1. Click the DATA tab on the
ribbon and then file from text
2. Find your text
3. Make sure it is delimited
4. Select which delimiters you will use (commas)
5. Select the data format
