Hello, can you please help me solve these questions in Excel? I want to see the solution on Excel, how to do these questions? thank you.
C D B 8 9 You MUST FORMAT ALL of your interest rates as PERCENTAGES rich 2 DECINALS You MUST FORMAT ALL of your dollar amounts as CURRENCY rith 2 DECIMALS You MUST display all of your ansrers in the blue shaded cells below 10 11 14 12 For questions 1 - 4 use a required nominal annual return: 5.00% 13 1. Consider a 1-year CD. Principal value $5,000.00 What is the future value of the CD in 1 year AND what is the effective annual rate? Future Value EFF a. annual compounding? 15 16 17 1 b. semi-annual compounding? 19 20 c. quarterly compounding? 21 22 d. monthly compounding? 23 24 e. daily compounding? 25 26 27 2. Rework problem #1 assuming a 5-year CD with $5000 principal. Future Value EFF 24 a. annual compounding? 30 21 b. semi-annual compounding? 32 c. quarterly compounding? 34 25 d. monthly compounding? 36 21 e. daily compounding? 38 HINT 44 3. Assume you want to invest $1000 at the END of every year for the next 5 years. Future Value a. What is the future value in year 5, if interest is compounded annually? 45 47 b. What is the future value in year 5 if interest is compounded semiannually? MUST use EFF for your interest rate 4* 49 50 51 4. Rework question 3, assuming payments are made at the BEGINNING of each perid HINT 52 Future Value 53 a What is the future value in year 5, if interest is compounded annually 55 b. What is the future value in year Sif interest is compounded semiannually? MUST use EFF for your interest rate 54 56 D 56 10.00% 59 Year 60 57 For problems 5&6 use required nominal annual return; 58 5. Consider the following end-of-year cash flows: Cash flow 0 $0.00 1 $40.00 2 $60.00 3 $60.00 Present Value 65 a. What is the present value of these cash flows (in year 0)? 61 62 63 64 66 67 b. If the purchase price of this investment is $140 today, would you buy it? Why? (Compare instrinsic value to actual price) 68 70 71 72 73 75 T8 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 0 -$140.00 1 $40.00 2 $60.00 3 $60.00 79 80 $1 82 6 d. Would you buy this investment based on your answer to part c. and why? * (Compare expected return to required return). 85 86 88 Present Value 6a. What is the present value of $1,000,000, due 25 years from now? 90 91 92 b. What is the present value of a 540,000 ordinary annuity for 25 years? 93 14 95 c. What is the present value of a $40,000 perpetuity, if the first payment is 1 year from now? 16 97 4 d. What is the present value of a $40,000 perpetuity, if the first payment is now? 99 100 102 101 7. You borrow $35,000 today and will make equal annual payments for the next 3 years, starting in 1 year a. What is the value of the annual payment if the nominal interest on the loan is: 9.50% Payment 105 106 107 101 Principal Ending Balance 1 104 b. Show the amortization of the loan in the table set up below: 1 Year Beg, Balance Payment Interest 111 2 10 14 TOTAL 115 112 3
