Chapter 6 Conceptual Review Match the following lettered items with Questions 1 14 A Compound Interest B CUMPRINC C FV D IPMT E IRR F NPER G NPV H Payback Period I PMT J PPMT K PV L RATE M ROI N Simple Interest O SLN P Type 1 L RATE Function to calculate the interest percentage per period of a financial transaction transaction I PMT transaction 2 K PV Function to calculate the value at the beginning of a financial 3 C FV Function to calculate the value at the end of a financial transaction 4 Function to calculate the periodic payments into or out of financial 5 F NPER Function to calculate the number of compounding periods in a financial transaction 6 P TYPE Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period 7 N Simple Interest This type of interest is calculated based on original principal regardless of the previous interest earned 8 A Compound Interest This type of interest is calculated based on principal and previous interest earned 9 O SLN Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value 10 J PPMT Function to calculate the amount of a specific periodic payment that is principal in a given period interest in a given period between two periods 11 D IPMT Function to calculate the amount of a periodic payment that is 12 B CUMPRINC Function to calculate the cumulative principal payment paid 13 G NPV Function to determine the value of a variable set of cash flows discounted to its present value 14 E IRR Function to determine the rate of return where the net present value of the cash flows is 0 15 Assume that you are investing 6 000 in a savings plan today and will make additional contributions of 200 per quarter The plan pays 5 interest per year compounded quarterly Write an Excel formula to determine how much your savings will be worth in five years FV rate nper pmt pv type rate is 05 4 because 5 is compounded quarterly nper is 5 4 because 5 years is compounded quarterly pmt is 200 and is a negative cash flow pv is 6000 and is a negative cash flow type is 0 which can be left omitted FV 05 4 5 4 200 6000 0 16 Write an Excel formula to determine the yearly interest rate being charged by the bank on a 375 000 30 year mortgage You make a monthly mortgage payment of 3 000 and the value of the loan at the end of 30 years is 0 Interest is compounded monthly RATE nper pmt pv fv type nper is 30 12 because interest is compounded monthly which means the number of periods in a year is 30 years times 12 months pmt is 3000 which is a negative cash flow pv is 375000 and is a POSITIVE cash flow fv is 0 type is 0 Since you are looking for ANNUAL interest you MULTIPLY the RATE by 12 aka 12 months RATE 30 12 3000 375000 0 0 12 17 Assume that you are buying a car for 23 500 with a 3 000 down payment and you are borrowing the rest from a bank at 6 0 annual interest compounded monthly Your monthly payments are 370 Write an Excel formula to determine the number of years it will take you to pay off this loan NPER rate pmt pv fv type rate is 06 12 pmt is 370 and is a negative cash flow pv is 23500 BUT there s a down payment of 3000 so you just subtract from PV 23500 3000 20500 and is a Positive cash flow fv is 0 type is 0 Since you are looking for number of YEARS to pay off you must DIVIDE NPER by 12 NPER 06 12 370 20500 0 0 12 18 Consider a 100 000 mortgage at 5 annual interest compounded monthly to be paid back over the next 15 years The loan will have a 5 000 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 PMT rate nper pv fv type rate is 05 12 nper is 15 12 pv is 100000 and is positive fv is 5000 because there is a Balloon Payment pg 381 type is 0 PMT will be a negative number because it is a payment PMT 05 12 15 12 100000 5000 0 19 Assume that you have been left an inheritance and want to save part it towards 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 15 000 at the end of the three year period Assume that you will place this money in a CD that pays 3 interest compounded quarterly and that you will be making no additional deposits into this account PV rate nper pmt fv type rate is 03 4 nper is 3 4 pmt is 0 and you MUST put 0 otherwise it will mistake the fv as pmt fv is 15000 type is 0 PV will be a negative number because it is an investment PV 03 4 3 4 0 15000 0 20 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 250 000 and has a useful life of 10 years and an estimate salvage value of 10 000 SLN cost salvage life Cost 250000 Salvage 10000 Life 10 Chapter 7 Conceptual Review 1 List and describe the steps you would take to create a structured list of data from a text file that contains values stored on separate lines PAGE 434 Working with Text Data A common way of sorting data so that it is usable in other programs is to save it in a comma delimited file This formats separates the values in each record with commas data stored in this way is also called comma separated values or CSV Excel can convert data stored in this file so that each value in a record appears in a separate cell of the worksheet where you import it 2 Why should you remove unnecessary spaces from data imported from another source How do you remove unnecessary spaces from a text string PAGE 538 Incorrect values can be stored because of unnecessary spaces Use the TRIM function Removes all spaces in a text string expect for the single spaces between words This function can be very useful when importing data from another data source in which the data might contain spaces at the end of values 3 How do the FIND and SEARCH functions work and how are they different FIND find text within text start num Finds one text string within another text string IS case sensitive using the start num character …
View Full Document
Unlocking...