A B D Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use cell references to the input data. I will change the input data in your problem to check alternate solutions. Include all computations and solutions on the worksheet tab, do not add tabs for ns/computations. This assignment is to be solely your own work, do not work with others or ask for help. You will turn in a complete working Excel spreadsheet with your solution. All work and solutions are to be on this worksheet tab. Do not create a new tab for work or solutions. solutions/ Given the following set of cash flows: Reversed Cash Flow 34 4 Cash Flow N4 5 6 Period 1 2 3 4 5 6 7 8 9 101 11 12 13 14 Cash Flow #1 & 2 Reversed Cash Flow 3 $ 41,000 $ 6,000 $ 35,000 $ 10,000 $ 30,000 $ 15,000 $ 25,000 $ 20,000 $ 20,000 $ 25,000 $ 15,000 $ 30,000 $ $ 10,000 $ 35,000 $ 6,000 $ 41,000 $ $ $ $ $ $ $ $ $ (150,000) 41,000 35,000 30,000 25,000 20,000 15,000 10,000 6,000 $ $ $ $ $ $ (150,000) 6,000 10,000 15,000 20,000 25,000 30,000 35,000 41,000 $ PV FV Rate of Return PV Rate of Return 8% 15 16 17 18 19 20 21 1. If your required rate of return is 8% per year, what is the present value of the above cash flows? 2. If your required rate of return is 8% per year, what is the future value of the above cash flows? 3. Suppose that you are offered another investment that is identical, except the cash flows are reveresed. Compute the PV and FV, is this investment worth more on less than #1 and #2? 4. If you paid $150,000 for the original investment, what average annual rate of return would you earn? What return would you earn on the reversed cash flows? What is the PV of these cash flows? 22 23 24 25 26 27 Stock & Bond Excel Assignment Advanced Financial Management Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use cell references to the input data. I will change the input data in your problem to check alternate solutions. This assignment is to be solely your own work, do not work with others or ask for help. You will turn in a complete working Excel spreadsheet with your solution. 1) a. What is the price of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.85% and is yielding 5.25%? 28 29 30 31 32 33 34 35 36 37 b. What is the YTM of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.85% and price of 973.47? 2) a . 2) Coffee shop chain Java the Hut just paid a $2.70 dividend. There are two possible pricing scenarios: Scenario 1: You expect the stock will grow at 7% per year forever. Assuming 13.5% required return, what is the value of Java's stock? b. Scenario 2: For the next two years, you think it will grow by 12% a year, getting it back to its pre-recession level. After that, you expect it to grow at 5% a year forever. Assuming 13.5% required return, what is the value Java's stock? 1) 1 Par Years Coupon YTM Price Price YTM DIO) G Reg Rtn Value 2) a) 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 b) DIO) G1 G2 Reg Ret Value Value Capital Investment Excel Assignment Advanced Financial Management Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use cell references to the input data. I will change the input data 59 60 1. A firm is considering the two mutually exclusive investments projects. Project Alpha requires an initial outlay of $600 and will return $165 per year for the next seven years; Project Beta requires an initial outlay of $1,100 and will return $355 per year for the next five years. Assuming a 11.5% required return, calculate the NPV, IRR, Payback Period, PI, and MIRR of each project. Which project is preferable and why? 61 62 63 64 65 66 67 6B 69 70 71 72 73 Alpha Beta NPV IRR Payback PI MIRR A B D Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use cell references to the input data. I will change the input data in your problem to check alternate solutions. Include all computations and solutions on the worksheet tab, do not add tabs for ns/computations. This assignment is to be solely your own work, do not work with others or ask for help. You will turn in a complete working Excel spreadsheet with your solution. All work and solutions are to be on this worksheet tab. Do not create a new tab for work or solutions. solutions/ Given the following set of cash flows: Reversed Cash Flow 34 4 Cash Flow N4 5 6 Period 1 2 3 4 5 6 7 8 9 101 11 12 13 14 Cash Flow #1 & 2 Reversed Cash Flow 3 $ 41,000 $ 6,000 $ 35,000 $ 10,000 $ 30,000 $ 15,000 $ 25,000 $ 20,000 $ 20,000 $ 25,000 $ 15,000 $ 30,000 $ $ 10,000 $ 35,000 $ 6,000 $ 41,000 $ $ $ $ $ $ $ $ $ (150,000) 41,000 35,000 30,000 25,000 20,000 15,000 10,000 6,000 $ $ $ $ $ $ (150,000) 6,000 10,000 15,000 20,000 25,000 30,000 35,000 41,000 $ PV FV Rate of Return PV Rate of Return 8% 15 16 17 18 19 20 21 1. If your required rate of return is 8% per year, what is the present value of the above cash flows? 2. If your required rate of return is 8% per year, what is the future value of the above cash flows? 3. Suppose that you are offered another investment that is identical, except the cash flows are reveresed. Compute the PV and FV, is this investment worth more on less than #1 and #2? 4. If you paid $150,000 for the original investment, what average annual rate of return would you earn? What return would you earn on the reversed cash flows? What is the PV of these cash flows? 22 23 24 25 26 27 Stock & Bond Excel Assignment Advanced Financial Management Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use cell references to the input data. I will change the input data in your problem to check alternate solutions. This assignment is to be solely your own work, do not work with others or ask for help. You will turn in a complete working Excel spreadsheet with your solution. 1) a. What is the price of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.85% and is yielding 5.25%? 28 29 30 31 32 33 34 35 36 37 b. What is the YTM of a semiannual $1,000 par value bond with four years left until maturity that pays a coupon of 3.85% and price of 973.47? 2) a . 2) Coffee shop chain Java the Hut just paid a $2.70 dividend. There are two possible pricing scenarios: Scenario 1: You expect the stock will grow at 7% per year forever. Assuming 13.5% required return, what is the value of Java's stock? b. Scenario 2: For the next two years, you think it will grow by 12% a year, getting it back to its pre-recession level. After that, you expect it to grow at 5% a year forever. Assuming 13.5% required return, what is the value Java's stock? 1) 1 Par Years Coupon YTM Price Price YTM DIO) G Reg Rtn Value 2) a) 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 b) DIO) G1 G2 Reg Ret Value Value Capital Investment Excel Assignment Advanced Financial Management Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas.....only use cell references to the input data. I will change the input data 59 60 1. A firm is considering the two mutually exclusive investments projects. Project Alpha requires an initial outlay of $600 and will return $165 per year for the next seven years; Project Beta requires an initial outlay of $1,100 and will return $355 per year for the next five years. Assuming a 11.5% required return, calculate the NPV, IRR, Payback Period, PI, and MIRR of each project. Which project is preferable and why? 61 62 63 64 65 66 67 6B 69 70 71 72 73 Alpha Beta NPV IRR Payback PI MIRR