Reference no: EM13954119

Use EXCEL to solve the following:

1. You need $150,000 in 5 years to pay for your son's college, how much must you deposit today in an account that pays an annual interest rate of 2%?

2. An investment specialist estimates he can earn 6% over the next 10 years in an investment account. If he is correct, how much will your $225,000 be worth at the end of the 10 years?

3. You received $90,000 from your former employer in an annuity. How long will it take to grow to $500,000 at a 6% discount rate?

4. You want your current stock portfolio to be worth $1,000,000 in 12 years. What annual rate of return must you achieve if your current value is $238,000?

5. A CD advertises an annual interest rate of 2.5% with semi-annual compounding of interest. If you want to accumulate $205,000 in 5 years, how much do you need to invest?

6. How much will $384,000 grow to be in 18 years if it is invested today into a savings account that quotes an annual interest rate of .5% with monthly compounding of interest?

7. You have 65,000 invested in an account with an annual interest rate of 3% with monthly compounding of interest. How long before the account grows to be worth $100,000?

8. At what annual investment rate would you need in order for $176,000 to grow to be $839,000 in 25 years if interest is compounded weekly?

9. What is the effective annual interest rate on an investment with a quoted annual interest rate of 13% with quarterly compounding of interest?

10. You win the lottery. You can receive 100,000 today, or an annuity that pays $24,000 per year for 11 years (the first payment will occur one year from today). If you estimate the discount rate to be 13%, what is the better option?

11. You can earn $16,000 per year for 6 years while in college part time working for your dad. He pays you at the end of each year, which you deposit in an account that pays an annual interest rate of 3%. What will your account be worth when you graduate?

12. You plan to borrow $389,000 now to start a business; you agree to repay the loan in 25 equal annual installments (payments will be made at the end of each year). If the annual interest rate is 8%, how much will your annual payments be?

13. Your best friend says he can make you $683,000 at the end of the 23 years if you invest $10,000 at the beginning of each year for the entire period of time. What annual rate of return is he assuming?

14. You are offered an annuity that will pay $8,800 per year for 15 years (the first payment will be made today). If you feel that the appropriate discount rate is 9%, what is the annuity worth to you today?

15. For the next 5 years, if you deposit $11,000 at the beginning of each year in an account that pays an annual interest rate of 6%, what will your account be worth at the end of the period?

16. You estimate that you need $2,000,000 to retire. You plan to retire in 30 years. How much do you need to deposit at the start of each year (assume equal annual deposits) into an account that pays an annual interest rate of 4% in order to reach your goal at retirement?

17. An advisor tells you that if you invest $5,500 in a particular investment at the beginning of each year for 15 years, it will have grown to $250,000 at the end of the period. What annual rate of return is the investment offering?

18. You plan to buy a car that costs $35,850, with a down payment of $7,500. You want to finance the balance over a period of 5 years, making equal monthly payments. Your quoted annual interest rate is 8% with monthly compounding of interest, with the first payment due one month after the purchase date. What will your monthly payment be?

19. You are considering leasing a car. You notice an ad that says you can lease the car you want for $508.00 per month. The lease term is 60 months with the first payment due at inception of the lease. You must also make an additional down payment of $2,370. The ad also says that the residual value of the vehicle is $25,350. After much research, you have concluded that you could buy the car outright for a price of $40,100. What is the quoted annual interest rate you will pay with the lease?

20. A certain investment that will pay you at the end of each year according to the following schedule: Year 1: $12,000

Year 2: $15,000
Year 3: $18,000
Year 4: $21,000
Year 5: $24,000
Year 6: $27,000

If the discount rate is 10.25%, what it the value of the investment to you now?

21. You are evaluating an investment that will pay you $12,000 per year for the first ten years, $20,000 per year for the next ten years, and $50,000 per year the following ten years (all payments are at the end of each year). If the appropriate annual discount rate is 6.00%, what is the value of the investment to you today?

22. You and your spouse recently purchased a 300,000 home with 75,000 down, and financing the rest using a 30-year mortgage with a nominal interest rate of 4.25%. Mortgage payments are made at the end of each month. How much of the first 3 years of mortgage payments will go towards repayment of principal?

23. You are valuing an investment options that will pay you $26,000 per year for the first 9 years, $34,000 per year for the next 11 years, and $47,000 per year the following 14 years (all payments are at the end of each year). Another similar risk investment option has a quoted annual interest rate of 9.00% with monthly compounding of interest. What is the present value of the first investment option?

24. You have just won the Powerball Lottery with a jackpot of $56,000,000. Your winnings will be paid to you in 26 equal annual installments with the first payment made immediately. If you feel the appropriate annual discount rate is 6%, what is the present value of the stream of payments you will receive?

25. You have just won the Indiana Lottery with a jackpot of $5,500,000. Your winnings will be paid to you in 20 equal annual installments with the first payment made immediately. If you had the money now, you could invest it in an account with a quoted annual interest rate of 2% with monthly compounding of interest. What is the present value of the stream of payments you will receive?

26. You are planning for retirement 34 years from now. You plan to invest $5,500 per year for the first 7 years, $10,000 per year for the next 11 years, and $16,500 per year for the following 16 years (assume all cash flows occur at the end of each year). If you believe you will earn an effective annual rate of return of 7.5%, what will your retirement investment be worth 34 years from now?

27. You plan to retire 13 years from now. You expect that you will live 26 years after retiring. You want to have enough money upon reaching retirement age to withdraw $150,000 from the account at the beginning of each year you expect to live, and yet still have $1,800,000 left in the account at the time of your expected death (39 years from now). You plan to accumulate the retirement fund by making equal annual deposits at the end of each year for the next 13 years. You expect that you will be able to earn 14% per year on your deposits. However, you only expect to earn 4% per year on your investment after you retire since you will choose to place the money in less risky investments. What equal annual deposits must you make each year to reach your retirement goal?

28. Today is January 1, 2015. You deposit $1,000 into a savings account that pays 8%.

a. If the bank compounds interest annually, how much will you have in your account on Jan 1, 2018?

b. What will your Jan. 1, 2018 balance be if the bank uses quarterly compounding?

c. Suppose you deposit $1,000 in 3 payments of $333.33 each on Jan 1 of 2016, 2017, 2018. How much will you have in your account on Jan 1, 2018 based on 8% annual compounding?

d. How much will be in your account if the 3 payments begin on Jan 1, 2015?

e. Suppose you deposit 3 equal payments into your account on Jan 1, 2016, 2017, and 2018. Assuming an 8% interest rate, how large must your payments be to have the same ending balance as in Part a?

29. It is now Jan 1, 2015, and you will need $1,000 on Jan 1, 2019, in 4 years. Your bank compounds interest at an 8% annual rate.

a. How much must you deposit today to have a balance of $1,000 on Jan 1, 2019?

b. If you want to make 4 equal payments on each Jan 1 from 2016 through 2019 to accumulate the $1,000, how large must each payment be? (Note: payments begin 1 year from today)

c. If you father offers to make the payments calculated in Part b ($221.92) or to give you $750 on Jan 1, 2016 (1 year from today), which would you choose and why?

d. If you have only $750 on Jan 1, 2016, what interest rate, compounded annually for 3 years, must you earn to have $1,000 on Jan 1, 2019?

e. Suppose you can deposit only $200 each Jan 1 from 2016 through 2019 (4 years). What interest rate, with annual compounding, must you earn to end up with $1,000 on Jan 1, 2019?

f. Your father offers to give you $400 on Jan 1, 2016. You will then make 6 additional equal payments each 6 months from July 2016 through Jan 2019. If you bank pays 8% compounded semiannually, how large must each payment be for you to end up with $1,000 on Jan 1, 2019?

g. What is the EAR (or EFF %) earned on the bank account in Part f? What is the APR earned on the account?

30. Bank A offers loans at an 8% nominal rate (its APR) but requires that interest be paid quarterly; that is, it uses quarterly compounding. Bank B wants to charge the same effective rate on its loans but it wants to collect interest on a monthly basis, that is, use monthly compounding. What nominal rate must Bank B set?