Please show the Excel Formulas & Abbreviations for Excel for the answers ***

OO Time Value of Money Excel Assignment.xlsx GO E41 Y= fx L 75% Q- Search in Sheet A Home Layout Tables Charts SmartArt Formulas Data Review Edit Font Alignment Number Format Cells Th Fill abc Wrap Text General Normal Bad Good XI HH LA HA Clear Arial 16 A- A BI U - A - X fx SAVE YOUR FILE AS E Merge - % % ) 4.0 .00 Check Cell .00 400 Neutral Calculation Check Cell Paste Conditional Formatting Insert Delete Format Theme Al + B D E F G H 1 J LKL M 2 3 4 7 Excel function name nominal PV nper npery PMT FV effect SAVE YOUR FILE AS LASTNAMEFIRSTNA egBealLaura Please type in your name here Due date October 30th, 11:59 pm. Points 100 5 REQUIREMENTS TO OBTAIN ALL 100 POINTS INPUT DATA Definition 6 Answers MUST be derived using cell references using a formula OR Excel function. 5.00% nominal interest rate Just typing in your answer derived using your calculator will NOT result in any points. $5,000.00 Present value 8 You MUST FORMAT ALL of your interest rates as PERCENTAGES with 2 DECIMALS. 1.00 Total Number of years 9 You MUST FORMAT ALL of your dollar amounts as CURRENCY with 2 DECIMALS. 1 # of compounding periods per year 10 You MUST display all of your answers in the blue shaded cells below. o payment 11 o Future Value 12 o effective interest rate 13 14 For questions 1 - 4 refer to the input data box highlighted in yellow above. You need to adjust your inputs as necessary. (The first question is done for you). 15 16 1. What is the future value of a 1 year Certificate of Deposit (CD) and what is the effective annual rate under each of the following scenarios? 17 17 Future Value EFF 18 a. annual compounding? ($5,250.00) 5.00% 19 19 20 b. semi-annual compounding? 21 22 c. quarterly compounding? 23 24 d. monthly compounding? 25 26 e. daily compounding? 27 H 27 28 29 2. What is the future value of the CD in 5 years and what is the effective annual rate under each of the following scenarios? Future Value EFF 31 a. annual compounding? 32 33 b. semi-annual compounding? 34 35 c. quarterly compounding? 36 37 d. monthly compounding? 38 39 e. daily compounding? 40 41 42 3. Assume you want to invest $1000 into your savings account at the END of every year for the next 5 years. 43 Future Value 44 a. What is the future value in year 5, if interest is compounded annually? 45 46 b. What is the future value in year 5 if interest is compounded semiannually? 47 Hint: You MUST use EFF for your interest rate 48 D E F Future Value 48 49 50 4. Rework question 3, assuming payments are made at the BEGINNING of each period. 51 52 a. What is the future value in year 5, if interest is compounded annually? 53 54 b. What is the future value in year 5 if interest is compounded semiannually? 55 Hint: You MUST use EFF for your interest rate 56 57 For problems 5 & 6 use required nominal annual return of: 58 5. Consider the following end-of-year cash flows: 59 Year Cash flow 60 0 61 1 $40.00 62 2 $60.00 63 3 $60.00 8.00% $0.00 64 Net Present Value (NPV) 65 a. What is the present value of these cash flows (in year O)? 66 67 b. If the purchase price of this investment is $140 today, would you buy it? Why? 68 (Compare instrinsic value to actual price) 69 70 71 72 73 74 75 76 c. What is the expected rate of return on this investment if the purchase price is $140? 77 Year Cash flow Internal Rate of Return (IRR) 78 0 -$140.00 79 1 $40.00 2 $60.00 81 3 $60.00 82 80 82 83 d. Would you buy this investment based on your answer to part c. and why? 84 (Compare expected return to required return). R7 o Present Value 89 6. Consider the present value of the following different investments. Recall the nominal rate 8.00% 91 92 a. What is the present value of $1,000,000, due 25 years from now? 93 94 95 b. What is the present value of a $40,000 ordinary annuity for 25 years? (Hint: $40,000 is the payment) 97 98 c. What is the present value of a $40,000 perpetuity, if the first payment is 1 year from now? 99 100 d. What is the present value of a $40,000 perpetuity, if the first payment is now? 101 102 $ 50,000.00 6.00% Payment 102 103 7. You take out a loan today and will make equal annual payments for the next 3 years, 104 starting in 1 year. Amount borrowed 105 106 a. What is the value of the annual payment if the nominal interest on the loan is: 107 108 109 110 111 112 b. Show the amortization of the loan in the table set up below: 113 Year Beg. Balance Payment 1114 115 116 117 TOTAL 118 119 120 121 122 123 124 125 Interest Principal Ending Balance OO Time Value of Money Excel Assignment.xlsx GO E41 Y= fx L 75% Q- Search in Sheet A Home Layout Tables Charts SmartArt Formulas Data Review Edit Font Alignment Number Format Cells Th Fill abc Wrap Text General Normal Bad Good XI HH LA HA Clear Arial 16 A- A BI U - A - X fx SAVE YOUR FILE AS E Merge - % % ) 4.0 .00 Check Cell .00 400 Neutral Calculation Check Cell Paste Conditional Formatting Insert Delete Format Theme Al + B D E F G H 1 J LKL M 2 3 4 7 Excel function name nominal PV nper npery PMT FV effect SAVE YOUR FILE AS LASTNAMEFIRSTNA egBealLaura Please type in your name here Due date October 30th, 11:59 pm. Points 100 5 REQUIREMENTS TO OBTAIN ALL 100 POINTS INPUT DATA Definition 6 Answers MUST be derived using cell references using a formula OR Excel function. 5.00% nominal interest rate Just typing in your answer derived using your calculator will NOT result in any points. $5,000.00 Present value 8 You MUST FORMAT ALL of your interest rates as PERCENTAGES with 2 DECIMALS. 1.00 Total Number of years 9 You MUST FORMAT ALL of your dollar amounts as CURRENCY with 2 DECIMALS. 1 # of compounding periods per year 10 You MUST display all of your answers in the blue shaded cells below. o payment 11 o Future Value 12 o effective interest rate 13 14 For questions 1 - 4 refer to the input data box highlighted in yellow above. You need to adjust your inputs as necessary. (The first question is done for you). 15 16 1. What is the future value of a 1 year Certificate of Deposit (CD) and what is the effective annual rate under each of the following scenarios? 17 17 Future Value EFF 18 a. annual compounding? ($5,250.00) 5.00% 19 19 20 b. semi-annual compounding? 21 22 c. quarterly compounding? 23 24 d. monthly compounding? 25 26 e. daily compounding? 27 H 27 28 29 2. What is the future value of the CD in 5 years and what is the effective annual rate under each of the following scenarios? Future Value EFF 31 a. annual compounding? 32 33 b. semi-annual compounding? 34 35 c. quarterly compounding? 36 37 d. monthly compounding? 38 39 e. daily compounding? 40 41 42 3. Assume you want to invest $1000 into your savings account at the END of every year for the next 5 years. 43 Future Value 44 a. What is the future value in year 5, if interest is compounded annually? 45 46 b. What is the future value in year 5 if interest is compounded semiannually? 47 Hint: You MUST use EFF for your interest rate 48 D E F Future Value 48 49 50 4. Rework question 3, assuming payments are made at the BEGINNING of each period. 51 52 a. What is the future value in year 5, if interest is compounded annually? 53 54 b. What is the future value in year 5 if interest is compounded semiannually? 55 Hint: You MUST use EFF for your interest rate 56 57 For problems 5 & 6 use required nominal annual return of: 58 5. Consider the following end-of-year cash flows: 59 Year Cash flow 60 0 61 1 $40.00 62 2 $60.00 63 3 $60.00 8.00% $0.00 64 Net Present Value (NPV) 65 a. What is the present value of these cash flows (in year O)? 66 67 b. If the purchase price of this investment is $140 today, would you buy it? Why? 68 (Compare instrinsic value to actual price) 69 70 71 72 73 74 75 76 c. What is the expected rate of return on this investment if the purchase price is $140? 77 Year Cash flow Internal Rate of Return (IRR) 78 0 -$140.00 79 1 $40.00 2 $60.00 81 3 $60.00 82 80 82 83 d. Would you buy this investment based on your answer to part c. and why? 84 (Compare expected return to required return). R7 o Present Value 89 6. Consider the present value of the following different investments. Recall the nominal rate 8.00% 91 92 a. What is the present value of $1,000,000, due 25 years from now? 93 94 95 b. What is the present value of a $40,000 ordinary annuity for 25 years? (Hint: $40,000 is the payment) 97 98 c. What is the present value of a $40,000 perpetuity, if the first payment is 1 year from now? 99 100 d. What is the present value of a $40,000 perpetuity, if the first payment is now? 101 102 $ 50,000.00 6.00% Payment 102 103 7. You take out a loan today and will make equal annual payments for the next 3 years, 104 starting in 1 year. Amount borrowed 105 106 a. What is the value of the annual payment if the nominal interest on the loan is: 107 108 109 110 111 112 b. Show the amortization of the loan in the table set up below: 113 Year Beg. Balance Payment 1114 115 116 117 TOTAL 118 119 120 121 122 123 124 125 Interest Principal Ending Balance