**Unformatted text preview:**

Chapter 61. Function to calculate the interest percentage per period of a financial transaction.RATE2. Function to calculate the value at the beginning of a financial transaction.PV3. Function to calculate the value at the end of a financial transactionFV4. Function to calculate periodic payments into or out of a financial transaction.PMT5. Function to calculate the number of compounding periods in a financial transaction.NPER6. Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period.Type7. This type of interest is calculated based on original principal regardless of the previous interest earnedSimple Interest8. This type of interest is calculated based on principal and previous interest earned.Compound Interest9. Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value.SLN10. Function to calculate the amount of a specific periodic payment that is principal in a given period.PPMT11. Function to calculate the amount of a periodic payment that is interest in a given period.IPMT12. Function to calculate the cumulative principal paid between two periods.CUMPRINC13. Function to determine the value of a variable set of cash flows discounted to its previous valueNPV (Net Present Value)14. Function to determine the rate of return, where the net present value of the cash flows is 0IRR (Internal Rate of Return)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(5.0%/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(6.0%/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(5.0%/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(3.0%/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 71. 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-First determine the format you need so you can find the best way to change the unstructured data into structured data. Then copy the data from the text file and paste it into a new workbook. Then change the data into comma separated values. Then use a text function to link together, or concatenate, the information from multiple cells into one.2. Why should you remove unnecessary spaces from data imported from another source? Ho do you remove unnecessary spaces from a text string? Because sometimes there are blank spaces at the end of texts. You can remove these blank spaces from a text string by using the TRIM function.3. How do the FIND and SEARCH functions work, and how are they different?The FIND and SEARCH functions return the starting position of one text value within another. The difference is that the FIND function is case sensitive and the SEARCH function is not.4. What options are available for parsing data when you use the convert Text to Columns Wizard? First way is to identify the character that separates the data. The second way is to 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? The advantages are that it creates summary reports that quickly organize data into categories with subtotal calculations and lets you collage and expand the level of detail in the report. A disadvantage of the subtotal command is that it works only with one category and one subtotal calculation at a time.6. What is the difference between an unstructured list and an Excel list?The difference between an unstructured list and an Excel list is that because all the data in an excel list are in a structured format, one 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- Sort A to Z, Sort Z to A, Sort by Color (if cells are manually or conditionally changed to have background color, it filters these colors), Clear Filter From (removes any filters and restores original data), Text Filters (filters alphanumeric text by specific characters), Number Filters, Date Filters (filters by date and time values),(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- Open a workbook where you want to import the database data. Select the first blank cell, and then click the From Access button in the Get External Data group on the Data tab on the ribbon. In the dialog box, find the file, select it, and click to open it.10. What is the Query Wizard and when do you use it? The query wizard lets you choose your data source and select the

View Full Document