CGS 2518 FINAL FSU ARMSTRONG
QUESTIONS WITH COMPLETE
ANSWERS
Function to calculate the value at the beginning of a financial transaction - Answer-PV
(Present Value)
Function to calculate the number of compounding periods in a financial transaction -
Answer-NPER (Number of Periods)
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
SML - Answer-Straight Line
Simple Interest - Answer-Interest calculated based on original principal
Compound Interest - Answer-Interest calculated base on principal and previous interest
earned
Function to calculate the cumulative interest paid between two periods - Answer-
CUMIPMT (Cumulative Interest Payment)
Function to calculate the amount of periodic payment that is interest in a given period -
Answer-IPMT (Interest Payment)
Function to calculate the amount of specific period payment that is principle in a given
period - Answer-PPMT (Prinicipal Payment)
Function to determine the value of a variable set of cash flows discounted to its present
value - Answer-NPV (Net Present Value)
Function to determine the rate of return, where the net present value of the cash flows is
0 - Answer-IRR (Internal Rate of Return)
Write an Excel formula to determine the amount of money you need to invest now to
have $17,000 at the end of a 3-year period. Assume that you will place money on a CD
that pays 2% interest compounded quarterly and you will make no additional deposits in
the amount - Answer-PV(2%/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 value of loan at the end of 20 years is 0. Interest in compounded monthly - Answer-
RATE(12*20,-3400,400000,0)
Assume that you are buying a car for $25,000 with a $4,000 down payment. You are
borrowing the rest from the bank at 5.5% annual interest compound monthly. Your
monthly payments are $400. Write and Excel formula to determine the number of years
it will take you to pay off this loan - Answer-NPER(5.5%/12,-400-25500,-4000)/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 that must be
made each month of 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 and Excel formula to determine how
much you savings will be worth in five years - Answer-FV(3%/4,5*4,-300,-3000,1)
Write and 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 $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 describe the steps you take to create a structure list of data from a text file that
contains values stored on separate lines - Answer-Text Import Wizard
Why should you remove unnecessary spaces from data imported from another source?
How do you remove unnecessary spaces from a text string - Answer-Spaces can cause
errors in formulas, TRIM
How do the FIND and SEARCH functions work? How are they different? - Answer-
Returns the starting position of one text value within another text value. SEARCH in not
case sensitive
What options are available for parsing data when you use the ConvertText to Columns
Wizard? - Answer--Identify character that delimits
-Set field width
What are the advantages and disadvantages to using the subtotal tool to analyze data?
- Answer-Pros: Summary Reports, Subtotals, Collapse/expand
Cons: Data rows must be together, Sorted by chosen column, Not available with Excel
table
QUESTIONS WITH COMPLETE
ANSWERS
Function to calculate the value at the beginning of a financial transaction - Answer-PV
(Present Value)
Function to calculate the number of compounding periods in a financial transaction -
Answer-NPER (Number of Periods)
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
SML - Answer-Straight Line
Simple Interest - Answer-Interest calculated based on original principal
Compound Interest - Answer-Interest calculated base on principal and previous interest
earned
Function to calculate the cumulative interest paid between two periods - Answer-
CUMIPMT (Cumulative Interest Payment)
Function to calculate the amount of periodic payment that is interest in a given period -
Answer-IPMT (Interest Payment)
Function to calculate the amount of specific period payment that is principle in a given
period - Answer-PPMT (Prinicipal Payment)
Function to determine the value of a variable set of cash flows discounted to its present
value - Answer-NPV (Net Present Value)
Function to determine the rate of return, where the net present value of the cash flows is
0 - Answer-IRR (Internal Rate of Return)
Write an Excel formula to determine the amount of money you need to invest now to
have $17,000 at the end of a 3-year period. Assume that you will place money on a CD
that pays 2% interest compounded quarterly and you will make no additional deposits in
the amount - Answer-PV(2%/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 value of loan at the end of 20 years is 0. Interest in compounded monthly - Answer-
RATE(12*20,-3400,400000,0)
Assume that you are buying a car for $25,000 with a $4,000 down payment. You are
borrowing the rest from the bank at 5.5% annual interest compound monthly. Your
monthly payments are $400. Write and Excel formula to determine the number of years
it will take you to pay off this loan - Answer-NPER(5.5%/12,-400-25500,-4000)/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 that must be
made each month of 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 and Excel formula to determine how
much you savings will be worth in five years - Answer-FV(3%/4,5*4,-300,-3000,1)
Write and 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 $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 describe the steps you take to create a structure list of data from a text file that
contains values stored on separate lines - Answer-Text Import Wizard
Why should you remove unnecessary spaces from data imported from another source?
How do you remove unnecessary spaces from a text string - Answer-Spaces can cause
errors in formulas, TRIM
How do the FIND and SEARCH functions work? How are they different? - Answer-
Returns the starting position of one text value within another text value. SEARCH in not
case sensitive
What options are available for parsing data when you use the ConvertText to Columns
Wizard? - Answer--Identify character that delimits
-Set field width
What are the advantages and disadvantages to using the subtotal tool to analyze data?
- Answer-Pros: Summary Reports, Subtotals, Collapse/expand
Cons: Data rows must be together, Sorted by chosen column, Not available with Excel
table
