Exercise 6 Finance Functions Using the appropriate functions in Excel, compute the answers for each of the following six problems in separate non-overlapping areas of the same worksheet. 1. You expect a solid job offer after you graduate from Mercer's accounting program. You plan to deposit $500 per month into an individual retirement account (IRA) in Bank of America's Decatur branch. The deposit will be automatically deducted from your monthly paycheck from E&Y Atlanta at the end of each month starting June 30, 2021. The IRA is paying 8% per year compounded monthly. What is the value of your account at the end of the 10-year period? 2. You plan to purchase an office space in Chamblee's Chinatown for $50,000 at the end of year 2021. You estimate that by renting out that office space, you will receive a stream of rental income for the coming eight years at the end of each year as shown in below. After eight years, you estimate that you can still sell the office space for $45,000 at the end of the eighth year. Is this project a good investment if you project that the normal rate of return in this line of business is 12%? How about if the general rate of return is 15%? 8%? Year 1 $6,000 Year 2 $6,500 Year 3 $7,000 Year 4 $7,500 Year 5 $7,500 Year 6 $8,500 Year 7 $8,500 Year 8 $8,500 3. Based on the information provided in Step 2 above, compute the Internal Rate of Return for the investment. 4. While you were waiting for your first job interview results to come, you spent several dollars to buy a Georgia Educational Lotto and were lucky enough to win a $1 million prize. The prize is to be awarded in 20 annual payments of $50,000 each, payable directly to your Bank of America account in Duluth at the end of the year each year. When the news of your winning was out, a person from Cash-Today-Enjoy-Now approached you immediately to offer you the option of receiving a lump sum payment of $400,000 right away, which you can invest in an IRA account that pays 12% compounded annually for the coming 20 years. a. What is the present value of the $50,000 annuity? Use the projected 12% rate as the rate that the alternative IRA account will pay. b. What should your decision be per the prize you win in lotto? Why? 5. After you have graduated from MBA, your uncle Bean gave you $10,000 gift to celebrate. You immediately invested that $10,000 into the Brighter Atlanta Bond that matures in five years with a value of $18,000 at maturity. If the bond interest is compounded monthly, what is the monthly interest rate for this investment? 6. You determine to deposit some money each year into your bank account for future investments. Since you were from New England area originally, you would like to accumulate $100,000 as your down payment for a house in suburban Boston so that you could enjoy the fun of snow plowing in winter. If you deposit $2,000 per year into an account that earns 10% interest per year compounded annually, how long does it take for you to accumulate the $100,000 target? If you would like to shorten the number of years to five years with the same 10% return annually, how much do you need to deposit per year at the end of each year? Required: Print your answers to Parts 1-6 into a single PDF file It is your decision as for how many pages you want to use to provide your answers to the whole exercise. Make sure that you clearly label your answers for easy grading. (Still use your last name + Exercise 6.PDF when you submit via Canvas.) Note: You do not need to worry about "hardwiring rules for this exercise. You will be fine so long as you get the correct answers. Grading: Each of the six parts will be counted for 15%, 90% total, overall format is for another 10%. Total = 100%. Exercise 6 Finance Functions Using the appropriate functions in Excel, compute the answers for each of the following six problems in separate non-overlapping areas of the same worksheet. 1. You expect a solid job offer after you graduate from Mercer's accounting program. You plan to deposit $500 per month into an individual retirement account (IRA) in Bank of America's Decatur branch. The deposit will be automatically deducted from your monthly paycheck from E&Y Atlanta at the end of each month starting June 30, 2021. The IRA is paying 8% per year compounded monthly. What is the value of your account at the end of the 10-year period? 2. You plan to purchase an office space in Chamblee's Chinatown for $50,000 at the end of year 2021. You estimate that by renting out that office space, you will receive a stream of rental income for the coming eight years at the end of each year as shown in below. After eight years, you estimate that you can still sell the office space for $45,000 at the end of the eighth year. Is this project a good investment if you project that the normal rate of return in this line of business is 12%? How about if the general rate of return is 15%? 8%? Year 1 $6,000 Year 2 $6,500 Year 3 $7,000 Year 4 $7,500 Year 5 $7,500 Year 6 $8,500 Year 7 $8,500 Year 8 $8,500 3. Based on the information provided in Step 2 above, compute the Internal Rate of Return for the investment. 4. While you were waiting for your first job interview results to come, you spent several dollars to buy a Georgia Educational Lotto and were lucky enough to win a $1 million prize. The prize is to be awarded in 20 annual payments of $50,000 each, payable directly to your Bank of America account in Duluth at the end of the year each year. When the news of your winning was out, a person from Cash-Today-Enjoy-Now approached you immediately to offer you the option of receiving a lump sum payment of $400,000 right away, which you can invest in an IRA account that pays 12% compounded annually for the coming 20 years. a. What is the present value of the $50,000 annuity? Use the projected 12% rate as the rate that the alternative IRA account will pay. b. What should your decision be per the prize you win in lotto? Why? 5. After you have graduated from MBA, your uncle Bean gave you $10,000 gift to celebrate. You immediately invested that $10,000 into the Brighter Atlanta Bond that matures in five years with a value of $18,000 at maturity. If the bond interest is compounded monthly, what is the monthly interest rate for this investment? 6. You determine to deposit some money each year into your bank account for future investments. Since you were from New England area originally, you would like to accumulate $100,000 as your down payment for a house in suburban Boston so that you could enjoy the fun of snow plowing in winter. If you deposit $2,000 per year into an account that earns 10% interest per year compounded annually, how long does it take for you to accumulate the $100,000 target? If you would like to shorten the number of years to five years with the same 10% return annually, how much do you need to deposit per year at the end of each year? Required: Print your answers to Parts 1-6 into a single PDF file It is your decision as for how many pages you want to use to provide your answers to the whole exercise. Make sure that you clearly label your answers for easy grading. (Still use your last name + Exercise 6.PDF when you submit via Canvas.) Note: You do not need to worry about "hardwiring rules for this exercise. You will be fine so long as you get the correct answers. Grading: Each of the six parts will be counted for 15%, 90% total, overall format is for another 10%. Total = 100%