Please answer question #6 in it's entirety :)

Thank you.

Finance 221 Time Value of Money Excel Project Compass Submission Due by 11:59 PM, Tuesday, Feb. 9 Please use Excel financial functions or algebraic time value of money equations to answer these questions in your spreadsheet. Please type the names of everyone in your group along with your TA's name and discussion section at the top of the first page of your workbook. Part 1: In the future. Construct a table and a graph showing the relationship between interest rates, time, and future value by showing how $8000 would grow each successive year over a 20 year period at different interest rates. Use $8000 for your present value and calculate the future value of this $8000 each year over the 20 year period at 0%, 3%, 6%, 9%, 12%, 15%, 18%, and 21% compounded annually. Future Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of future values. Part 2: Today's Value Construct a table and a graph showing the relationship between interest rates, time, and present value by showing how $8,000 pushed a year further into the future over a 20 year period would be discounted at different interest rates. Use $8,000 for your future value and calculate the present value of this $8,000 each year over the 20 year period at 0%, 3%, 6%, 9%, 12%, 15%, 18%, and 21% compounded annually. Present Value should be the y-axis for your graph and years (or time) should be your x-axis and you should end up with a line for each interest rate on your graph. Please insert your graph (chart) under your table of present values. Part 3: Casino.com mini-case Casino.com Corporation is building a $50 million office building in Las Vegas and is financing the construction at a 75% loan-to-value ratio, where the loan is in the amount of $37,500,000. This loan has a ten-year maturity, calls for quarterly payments, and has a stated annual interest rate of 8% APR. Using this information, answer the following questions. 1. What is the quarterly payment? 2. Construct a complete Amortization Schedule for this loan, see section 5.18 of your book for details on how to set up this table. How much of the first payment is interest? How much of the first payment is principal? 3. How much will Casino.com Corporation owe on this loan after making quarterly payments for four years (the amount owed immediately after the 16th payment)? 4. Should this loan be refinanced after four years with a new six-year 6% loan (with quarterly payments), if the cost to refinance is $250,000? To make this decision, calculate the new loan payments and then the present value of the difference or savings in the loan payments at 6% APR. Part 4. New Car You're looking to buy a new 2015 Kia Optima SX Turbo with popular options at a price of $30,000. Kia is offering two great incentives to clear their 2015 inventory with the 2016 models rolling out. Being a poor college student, you only have cash to pay taxes, title, license & fees and need to finance the car's purchase price. You smartly researched your finance options and got pre-approval at a 2.3% APR for 60 months on your own so you're not at the mercy at depending on financing from the car dealership. Kia is offering 0.0% APR financing for 60 months along with $1,500 cash back which would be used as a down payment that reduce that amount that you would need to finance or $2,500 cash back which you would also use as a down payment without the 0% financing. The Kia dealer is also willing to match your 2.3% APR pre-approved financing for 60 months if you elect the $2,500 cash back option. Answer the following questions. 1. What is the effective annual rate of the 2.3% APR compounded monthly? 2. What would be your monthly car loan payment under the Kia's 0% APR financing offer with $1500 cash back (assume a 60-month loan term)? 3. What would be your monthly car loan payment under the Kia's $2,500 cash back offer and 2.3% APR financing (assume a 60-month loan term)? 4. At what APR would you be indifferent between the two offers? In other words, at what APR would you have the same monthly payment (assuming a 60-month loan term) for the $2,500 cash back offer as you would with the 0% APR financing offer with $1,500 cash back? Part 5: Baby's Harvard MBA? Congratulations you just had a baby! The baby already takes after you and shows amazing business acumen. You want your baby to get an undergraduate degree and work for 3 years and then quit their job to get a Harvard MBA twenty-five years from now. The issue is this a 2year program with a current annual cost of $50,000 and this cost is expected to grow 3.5% annually over the next 25 and 26 years. You wish to save up for your baby's future MBA costs using a tax-sheltered investment account expected to earn 6.5% compounded annually to make easier for baby to quit their job and get their MBA. Please answer the following questions. 1) What is the expected future cost of each year of baby's Harvard MBA 25 and 26 years from now (assume the cost will continue to increase from year 1 to year 2 of the program)? 2) How much would you have to deposit today in a single lump sum deposit in order to fund baby's future expected MBA costs (assume your investment account will continue to earn 6.5% between year 1 and 2 of the program)? 3) The deposit in the previous question is a huge chuck of change. Instead, you decide it might be best to fund your baby's MBA with a series of 25 equal annual deposits beginning today. What does this annual deposit need to be given the 6.5% expected annual compound return? Part 6: Financing Retirement Prof. Business has a self-managed retirement plan through her University and would like to retire in 13 years and wonders if her current and future planned savings will provide adequate future retirement income. Here's her information and goals. Prof. Business wants a 23-year retirement annuity that begins 13 years from today with an equal annual payment equal to $90,000 today inflated at 2.5% annually over 13 years. Her first retirement annuity payment would occur 13 years from today. She realizes her purchasing power will decrease over time during retirement. Prof. Business currently has $400,000 in her University retirement account. She expects these savings and any future deposits into her University and any other retirement account will earn 7.5% compounded annually. Also, she expects to earn this same 7.5% annual return after she retires. Answer the following questions to help Prof. Business finalize her retirement planning. 1. What is Prof. Business' desired annual retirement income? 2. How much will Prof. Business need 13 years from today to fund her desired retirement annuity? 3. In addition to the $400,000 balance today, Prof. Business will fund her future retirement goal from question 2 by making 13 annual equal deposits at 7.5% compounded annually into her retirement accounts starting a year from today (the last deposit will be made when Prof. Business retires). How large does this annual deposit need to be in addition to the initial $400,000 invested in Prof. Business' retirement fund? 4. This annual figure from #3 makes Prof. Business feel a little anxious about her future planned retirement since her current annual contribution is $14,600. Also, Prof. Business' annual retirement account contribution is based on a percentage of her salary and will increase as her salary increases. However, Prof. Business is worried about her purchasing power eroding during retirement. She would like her first retirement withdrawal to be equal to the amount you found in #1, and then she increase each successive retirement withdrawal by 2.5% annually over the remaining 22 withdrawals. How much will Prof. Business need now at retirement given Prof. Business' 7.5% expected return? 5. In addition to the $400,000 balance today, Prof. Business will fund her adjusted future retirement goal from question 4 by making 13 annual equal deposits at 7.5% compounded annually into her retirement accounts starting a year from today (the last deposit will be made when Prof. Business retires). How large does this annual deposit need to be in addition to the initial $400,000 invested in Prof. Business' retirement fund? 6. Wow, the annual deposit required to fund the growing retirement annuity in question 5 gives Prof. Business some sticker shock. However, she may be willing to accept a lower annual retirement annuity than described in question 4 that loses purchasing power over time but that is hopefully higher than the retirement annuity in questions 1 and 2. Let's account for the fact that her and the University's contributions to Prof. Business' University retirement plan are based on a certain percentage of her salary and will increase as her salary increases. Based on this formula, her first upcoming end of the year deposit will be $14,600 and let's assume that her annual deposit and salary will grow at a 2.5% annual rate over the remaining 12 years (13 total deposits) to Prof. Business' retirement. Also, she plans to contribute an additional $6,000 at the end of each year until she retires. This will make her first year total deposit $20,600, but only the $14,600 part of this deposit will increase 2.5% annually, the $6000 part of the deposit will remain fixed each year under current laws. These deposits are in addition to the $400,000 she currently has today in the University retirement plan. Answer the following based on these assumptions. a) How much money will Prof. Business have in her retirement account immediately after her last deposit 13 years from today? b) What would be the equal annual payment from her 23-year retirement annuity whose first payment occurs exactly 13 years from today