CGS 2518 Gower Winter Final Review CH 6 9 C hapter 6 Conceptual Review 1 Function to calculate the interest percentage per period of a financial transaction 2 Function to calculate the value at the beginning of a financial transaction 3 Function to calculate the value at the end of a financial transaction 4 Function to calculate periodic payments into or out of a financial transaction a RATE a PV a FV a PMT a NPER 5 Function to calculate 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 of each compounding period a Type interest earned a Compound Interest a Simple Interest a SLN 7 This type of interest is calculated based on original principal regardless of the pervious 8 This type of interest is calculated based on principal and previous interest earned 9 Function to calculate straight line depreciation based on the initial capital investment number of years to be depreciated and salvage value 1 CGS 2518 Gower Winter Final Review CH 6 9 10 Function to calculate the amount of a specific periodic payment that is principal in a given period a PPMT a IPMT value a NPV a IRR a CUMPRINCE 11 Function to calculate the amount of a periodic payment that is interest in a given period 12 Function to calculate the cumulative principal paid between two periods 13 Function to determine the value of a variable set of cash flows discounted to its present 14 Function to determine the rate of return where the net present value of the cash flows is 0 15 FV rate nper pmt pv type is used this way cid 171 FV 5 0 4 5 4 200 6000 0 16 RATE nper pmt pv fv type this way cid 171 RATE 30 12 3000 375000 0 0 17 NPER rate pmt pv fv type this way cid 171 NPER 6 0 12 370 20500 0 18 PMT rate nper pv fv type this way cid 171 PMT 5 0 12 15 12 100000 5000 19 PV rate nper pmt fv type this way cid 171 PV 3 0 4 3 4 0 15000 20 SLN cost salvage life this way cid 171 SLN 250000 10000 10 2 CGS 2518 Gower Winter Final Review CH 6 9 C hapter 7 Conceptual Review 1 Listed steps 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 cid 38 cid 50 cid 49 cid 38 cid 36 cid 55 cid 40 cid 49 cid 36 cid 55 cid 40 cid 11 cid 36 cid 20 cid 15 cid 179 cid 15 cid 180 cid 15 cid 36 cid 21 cid 15 cid 179 cid 15 cid 180 cid 15 cid 36 cid 22 cid 15 cid 179 cid 15 cid 180 cid 15 cid 36 cid 23 cid 15 cid 179 cid 15 cid 180 cid 15 cid 36 cid 24 cid 15 cid 179 cid 15 cid 180 cid 15 cid 36 cid 25 cid 12 cid 3 cid 87 cid 82 cid 3 cid 80 cid 72 cid 85 cid 74 cid 72 cid 3 cid 71 cid 68 cid 87 cid 68 cid 3 cid 76 cid 81 cid 87 cid 82 cid 3 cid 82 cid 81 cid 72 cid 3 cel cid 79 cid 17 cid 3 cid 37 cid 92 cid 3 cid 76 cid 81 cid 86 cid 72 cid 85 cid 87 cid 76 cid 81 cid 74 cid 3 cid 179 cid 15 cid 180 cid 3 cid 87 cid 75 cid 72 cid 3 cid 71 cid 68 cid 87 cid 68 cid 3 cid 76 cid 81 cid 3 cid 87 cid 75 cid 72 cid 3 cid 38 cid 50 cid 49 cid 38 cid 36 cid 55 cid 40 cid 49 cid 36 cid 55 cid 40 cid 3 cid 73 cid 88 cid 81 cid 70 cid 87 cid 76 cid 82 cid 81 cid 3 cid 90 cid 76 cid 79 cid 79 cid 3 cid 69 cid 72 cid 3 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 cid 38 cid 50 cid 49 cid 38 cid 36 cid 55 cid 40 cid 49 cid 36 cid 55 cid 40 cid 11 cid 55 cid 53 cid 44 cid 48 cid 11 cid 36 cid 20 cid 12 cid 15 cid 180 cid 15 cid 180 cid 15 cid 55 cid 53 cid 44 cid 48 cid 11 cid 36 cid 21 cid 12 cid 15 cid 180 cid 15 cid 180 cid 15 cid 55 cid 53 cid 44 cid 48 cid 11 cid 36 cid 22 cid 12 cid 171 cid 17 cid 12 cid 12 f Use the FIND function in order to sort through the data The function FIND cell cid 86 cid 72 cid 79 cid 72 cid 70 cid 87 cid 72 cid 71 cid 15 cid 3 cid 179 cid 179 cid 12 cid 3 cid 90 cid 76 cid 79 cid 79 cid 3 cid 86 cid 72 cid 68 cid 85 cid 70 cid 75 cid 3 cid 87 cid 75 cid 85 cid 82 cid 88 cid 74 cid 75 cid 3 cid 87 cid 75 cid 72 cid 3 cid 70 cid 82 cid 79 cid 88 cid 80 cid 81 cid 86 cid 3 cid 90 cid 76 cid 87 cid 75 cid 3 cid 87 cid 75 cid 72 cid 3 cid 53 cid 44 cid 42 cid 43 cid 55 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 j i home tab 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 Delete all other data except the data in column B that has a value of 1 in its corresponding D column cell 2 It may cause errors in Excel the trim function 3 The FIND function returns the starting position of one text value within another text value It is also case sensitive The SEARCH function does the same thing as find however …
View Full Document
Unlocking...