XYZ Bank has 690,595 customers’ deposit record. The information in Excel is a part of the deposit records. If Deposit type (column C) is “S-Single CF”, it means the customer made a single deposit of the amount as indicated in column A and kept that amount in the bank for the number of periods indicated in column D. For example, Client #1 deposited a single amount of $2000 and kept that amount in the bank for 10 years (10y).

If Deposit type is “D-Annuity Due”, it means the customer made the same amount of deposit indicated in column A at the beginning of each month during the period indicated in Column C. For example, Client #2 deposited $2500 at the beginning of each month and did it for 24 months (24m).

If Deposit type is “O-Ordinary Annuity”, it means the customer made the same amount of deposit indicated in column A at the end of each month during the period indicated in Column C. For example, Client #5 deposited $4000 at the end of each month and did it for 24 months (24m).

1. You are required to use IF(), Left(), and Right() functions in column E to convert the information in Column D to number of months. For example, Client #1 deposited $2000 and kept it in the bank for 10 years (10y). You are required to convert “10y” in Column D to it to “120” in Column E so that you can use this number when you compute FV in Column F. Once you get your formula in Cell E4, you are expected to copy down to all other rows. Note, if you do NOT use IF(), Left(), and Right() or similar excel functions, but simply type in numbers one-by-one, you will get 0 grade for this part. (3 points)
2. You are required to use IF(), Left(), and Right(), and combined with other equations to computing future values in column F. Once you get a formula in Cell F4, you are required to copy it to all other rows. Note, if you do NOT use IF(), Left(), and Right() or similar excel functions, but simply type in numbers one-by-one, you will get 0 grade for this part. (7 points)

Transcribed Image Text:

AutoSave oIF 合日ちい5▼ E Excel 1 Formulas Review View Developer 2 Share O Comments Home Insert Draw Page Layout Data | 图了 日 , 。朝。翻 Ls! Cenrectians Clear E Show Dctail E Froserties Reesply 71 Sort Filter Consalidate Urgrcup Subtatal Fom From New Catabase Rerresh All Stocks Gcography Text ta Flash Remove Date What-It Graua Hde Detail Data HTML Text Query Edit Links Acvanced Columns Fill Duplicates Velidation Arrelysis Analysis Ofice Update To keep up-to-date with security upcates, tixes, and improvements, cho0se Check for Updates. Chock for Updatos v fx E13 A B D E F G H J K L M N 1 Problem 4: Customer's deposit record 2 Annual Intereat Rate 8.0% Depceit Period 10y Deposit Amount Deposity Type S-Single CF D-Annulty Due D-Annuity Due S-Single CF 0-Ordinary Annuity 0-Ordinary Annuity S-Single CF D-Annuity Due D-Annuity Due S-Single CF 0-Ordinary Annuity O-Ordinary Annulty S-Singe CF D-Annuity Due D-Annuily Due S-Single CF 0-Ordinary Annuity 0-Ordinary Annuity S-Single CF D-Annuity Due D-Annuity Due S-Singe CF 3 Clent ID Deposit Perlod in # of months FV 4 #1 2000 5 #2 2500 24m 6 #3 7 4 8 #5 9 #6 3000 12m 3500 10y 4000 24m 4500 12m 10 #7 5000 10y 11 #8 5500 24m 12 #9 6000 12m 13 #10 6500 10y 14 #11 7000 24m 15 #12 7500 12m 16 #13 BD00 10y 17 #14 B500 24m 18 415 9000 12m 19 #16 9500 10y 20 #17 21 #18 10000 24m 10500 12m 22 #19 11000 10y 24m 23 #20 11500 24 #21 12000 12m 25 #22 12500 10y 26 #23 13000 0-Ordinary Annulty o-Ordinary Annulty S Singe CF D-Annuity Due D-Annuity Due S-Single CF 0-Ordinary Annuity O-Ordinary Annuity D-Annuity Due S-Single CF 0-Ordinary Annuity 24m 27 #24 13500 12m 28 #25 14000 10y 29 #26 14500 24m 30 #27 15000 12m 31 #28 15500 10y 32 #29 16000 24m 33 #30 16500 12m 34 31 12m 10y 3100 35 #32 3750 36 #33 4400 24m Probem1 tomp Problem2 temp Problema temp Problem4 temp Problems temp Ready 国回 + 133% 的 AutoSave oIF 合日ちい5▼ E Excel 1 Formulas Review View Developer 2 Share O Comments Home Insert Draw Page Layout Data | 图了 日 , 。朝。翻 Ls! Cenrectians Clear E Show Dctail E Froserties Reesply 71 Sort Filter Consalidate Urgrcup Subtatal Fom From New Catabase Rerresh All Stocks Gcography Text ta Flash Remove Date What-It Graua Hde Detail Data HTML Text Query Edit Links Acvanced Columns Fill Duplicates Velidation Arrelysis Analysis Ofice Update To keep up-to-date with security upcates, tixes, and improvements, cho0se Check for Updates. Chock for Updatos v fx E13 A B D E F G H J K L M N 1 Problem 4: Customer's deposit record 2 Annual Intereat Rate 8.0% Depceit Period 10y Deposit Amount Deposity Type S-Single CF D-Annulty Due D-Annuity Due S-Single CF 0-Ordinary Annuity 0-Ordinary Annuity S-Single CF D-Annuity Due D-Annuity Due S-Single CF 0-Ordinary Annuity O-Ordinary Annulty S-Singe CF D-Annuity Due D-Annuily Due S-Single CF 0-Ordinary Annuity 0-Ordinary Annuity S-Single CF D-Annuity Due D-Annuity Due S-Singe CF 3 Clent ID Deposit Perlod in # of months FV 4 #1 2000 5 #2 2500 24m 6 #3 7 4 8 #5 9 #6 3000 12m 3500 10y 4000 24m 4500 12m 10 #7 5000 10y 11 #8 5500 24m 12 #9 6000 12m 13 #10 6500 10y 14 #11 7000 24m 15 #12 7500 12m 16 #13 BD00 10y 17 #14 B500 24m 18 415 9000 12m 19 #16 9500 10y 20 #17 21 #18 10000 24m 10500 12m 22 #19 11000 10y 24m 23 #20 11500 24 #21 12000 12m 25 #22 12500 10y 26 #23 13000 0-Ordinary Annulty o-Ordinary Annulty S Singe CF D-Annuity Due D-Annuity Due S-Single CF 0-Ordinary Annuity O-Ordinary Annuity D-Annuity Due S-Single CF 0-Ordinary Annuity 24m 27 #24 13500 12m 28 #25 14000 10y 29 #26 14500 24m 30 #27 15000 12m 31 #28 15500 10y 32 #29 16000 24m 33 #30 16500 12m 34 31 12m 10y 3100 35 #32 3750 36 #33 4400 24m Probem1 tomp Problem2 temp Problema temp Problem4 temp Problems temp Ready 国回 + 133% 的

Answer rating: 100% (QA)

Answer Formula for Deposit period in months Co... View the full answer