Chapter 6 a Rate a PV a FV a PMT a NPER a Type a SLN a PPMT a IPMT a NPV a IRR 1 Calculates the interest percentage per period of a financial transaction 2 Calculates the value at the beginning of a financial transaction 3 Calculates the value at the end of a financial transaction 4 Calculates periodic payments into or out of a financial transaction 5 Calculates the number of compounding periods in a financial transaction 6 Use a 1 for this argument to indicate that interest will be paid at the beginning 7 This type of interest is calculated based on original principal regardless of the previous interest earned a Simple Interest a Compound Interest be depreciated and salvage value 8 This type of interest is calculated based on principal and previous interest earned 9 Calculates straight line depreciation based on the initial capital investment number of years to 10 Calculates the amount of a specific periodic payment that is principal in a given period 11 Calculates the amount of a periodic payment that is interest in a given period 12 Calculates the cumulative principal paid between two years a CUMPRINCE 13 Determines the value of a variable set of cash flows discounted to its present value 14 Determines 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 Formula for savings in 5 years FV 05 4 5 4 200 6000 0 16 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 30 12 3000 375000 0 0 17 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 06 12 370 20500 0 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 05 12 15 12 100000 5000 19 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 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 03 4 3 4 0 15000 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 estimated salvage value of 10 000 SLN 250000 10000 10 Chapter 7 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 use the CONCATENATE TRIM RIGHT and FIND functions to combine multiple text strings into a single text string to trim unnecessary spaces from a text string to find and extract characters from a text string and to find specific characters in a text string time 2 Why should you remove unnecessary spaces from data imported from another source How do you remove unnecessary spaces from a text string May cause errors within Excel using the TRIM function 3 How do the FIND and SEARCH functions work and how are they different The FIND function returns the starting position of one text value within another text value and it is case sensitive SEARCH function does the same thing as FIND but it is not case sensitive 4 What options are available for parsing data when you use the Convert Text to Columns Wizard i Identify the character that delimates the data In comma delimited files commas separate the data Another common way is the tab character ii Set field widths to identify the breaks between data that appears in columns 5 What are the advantages and disadvantages of using the Subtotal tool to analyze data Advantages are that it creates summary reports that quickly organize data into categories with subtotal calculations and allows you to collage and expand the level of detail in a report The disadvantages are that it works only with one category and one subtotal calculation at a 6 What is the difference between an unstructured list and an Excel list Excel lists are in a structured format you can use the filter feature to sort through the data using different methods 7 List and describe the six available options when using the AutoFilter feature in an Excel Table i Sort A to Z sorts data in ascending order ii Sort Z to sorts in descending order iii Sort by Color if cells are manually or conditionally changed to have background color it filters these colors iv Clear Filter From removes any filters and restores original data v Text Filters filters alphanumeric text by specific characters vi Number Filters vii Date Filters filters by date and time values using comparison operators viii Custom Filter all filters include option to filter custom 8 What is the primary advantage of storing data in a database and importing that data into Excel It ensures that your data is secured and protected but can be analyzed using a spreadsheet easily thus reducing redundancy 9 Explain the steps you must take to import data stored in an Access database into Excel i Open a workbook where you want to import the database data ii Select the first blank cell where you want the data to appear and then click the From Access button in the Get External Data group on the Data tab on the Ribbon The Select Data Source dialog box opens 10 What is the Query Wizard and when would you use it It lets you choose your data source and select the database table and fields you want to import into a workbook It prompts you to define any criteria for the data by selecting row to meet those criteria You would use the query wizard when you are trying to select specific data prior to importing it into Excel 11 How does Excel store date and time values Excel uses serial numbers to represent
View Full Document
Unlocking...