**Unformatted text preview:**

Chapter 6 Conceptual Review 1. L Function to calculate the interest percentage per period of a financial transaction. 2. K Function to calculate the value at the beginning of a financial transaction. 3. C Function to calculate the value at the end of a financial transaction. 4. I Function to calculate periodic payments into or out of a financial transaction. 5. F Function to calculate the number of compounding periods in a financial transaction. 6. P Use a 1 for this argument to indicate that interest will be paid at the beginning of each compounding period. 7. A This type of interest is calculated based on original principal regardless of the pervious interest earned. 8. N This type of interest is calculated based on principal and previous interest earned. 9. O Function to calculate straight line depreciation based on the initial capital investment, number of years to be depreciated, and salvage value. 10. J Function to calculate the amount of a specific periodic payment that is principal in a given period. 11. D Function to calculate the amount of a periodic payment that is interest in a given period. 12. B Function to calculate the cumulative principal paid between two periods. 13. G Function to determine the value of a variable set of cash flows discounted to its present value. 14. E 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) =FV(5.0%/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) =RATE(30*12, -3000, 375000, 0, 0) 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) =NPER(6.0%/12, -370, -20500, 0) Since there is a down payment of $3,000, in order to account for that amount, you have to take it off the PV amount. Therefore $23,500 – $3,000 = $20,500 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) =PMT(5.0%/12, 15*12, 100000, -5000) Balloon payments are incorporated into the function by including them as a negative FV. A balloon payment simply allows the payments to be lower because a lump sum will be paid out at the end of the loan life. RATE argument is divided by however it is compounded. In this case quarterly, therefore the quarterly interest rate is the rate divided by 4. NPER argument is found by multiplying the number of payments per year by the years. In this case 5 years multiplied by 4 because payments are made quarterly. These values, the PMT amount and PV are negative because the money is outflowing, i.e. the money is being invested. They would be positive if they were money received such as the money received from a loan or payments received,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(rate,nper,pmt,fv,type) =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(cost,salvage,life) =-SLN(250000,10000,10) There is a negative sign (-) in front of the function so as to show that the resulting value is to be subtracted. Depreciation is the amount of worth something loses.Chapter 7 Conceptual Review 1. List and describe the steps you would take to create a structured list of data from a test file that contains values stores on separate lines. a. Determine format needed (most likely Comma-separated values - CSV) b. Copy data from file and paste into workbook c. In the column next to the data, use: CONCATENATE(A1,“,”,A2,“,”,A3,“,”,A4,“,”,A5,“,”,A6) to merge data into one cell. By inserting “,” the data in the CONCATENATE function will be separated by commas. d. In an adjacent cell use the RIGHT function to sort through the data to sort the data using a common text. In this case the data would be sorted out by a dash followed by the last four numbers of the phone number. The function would look as follows RIGHT (cell selected, 5). e. To ensure the right function returns the values desired, next a TRIM function into the concatenate function in order to remove unnecessary spaces in each value. CONCATENATE(TRIM(A1),”,”,TRIM(A2),”,”,TRIM(A3)….)) f. Use the FIND function in order to sort through the data. The function FIND(cell selected, “-“) will search through the columns with the RIGHT functions and return a value of 1 if the first letter of the text in that cell is a dash. Otherwise if the first letter of that text is not a dash, it will return #VALUE. g. Select all data and copy and paste values only onto a new worksheet to avoid potential problems with the functions updating. h. Once again select all data and click the sort and filter button on the ribbon on the home tab. i. In the sort dialog box, sort the values by column D in ascending order, which would place the values of 1 at the top of the data. j. Delete all other data except the data in column B that has a value of 1 in

View Full Document