Question

Instructions: Using Financial Formulas in Excel, answer the following time value of money problems in a single Excel worksheet. You can round to the nearest dollar. You must turn in your answers in an Excel document.

1. How much interest is earned in an account by the end of 5 years if $30,000 is deposited and interest is 4% per year, compounded semi-annually?

2. What is the balance in an account at the end of 10 years if $6,500 is deposited today and the account earns 3% interest compounded annually?

3. If you wish to accumulate $50,000 in 10 years, how much must you deposit today in an account that pays annual interest rate of 8%, with semi-annual compounding of interest?

4. If you deposit 20,000 per year for 6 years in an account that pays an annual interest rate of 6%, what will your account be worth at the end of 6 years?

5. You decide that you need $88,000 in 10 years in order to make a down payment on a house. You plan to make annual deposits to achieve your goal. If interest rate is 8%, how much should be deposited each time? Hint use the Financial Function “PMT” to solve for the payment and PV will be zero.

6. You owe $46,000 to your parents for funding some of your college. You promise to make 8 annual payments of $8,000 to settle your debt. Approximately what interest rate are your parents charging (estimated up to 2 decimal places), if you make the 8 annual payments beginning one year from now? Hint use the Financial Function “RATE”. Note if you need help use the “Help on this function” feature. This help will include an example for you to follow.

7. You are offered an investment that will pay $36,300 per year for 9 years. If you feel that the appropriate discount rate is 7%, what is the investment worth to you today?

8. Your grandparents offered you some money. You are offered the following options. Assuming an annual interest rate of 5.0%, which option should you choose?

Show your work for each alternative

a. Receive $20,000 immediately

b. Receive $3,200 at the end of each six months for four years. You will receive the first check in six months

c. Receive $2,900 at the end of each year for four years, and then $20,000 at the end of the fifth year.

Answer 1

Ans. 1:

Principle (P) = $30000

Interest Rate (r)= 4% i.e. 2% Semi-Annual

Time (n)= 5 Years i.e. 10 Semi-Annual

Interest Earned = [FVIF(2%,10)-1] * 30000

= [(1.02)¹⁰-1] * 30000

= (1.2190-1) * 30000

= .2190 * 30000

= $6570

Ans. 2:

Principle (P) = $6500

Interest Rate (r)= 3%

Time (n)= 10 Years

Balance at end of 10th year = FVIF(3%,10) * 6500

= (1.03)¹⁰ * 6500

= 1.3439 * 6500

= $8735

Ans. 3:

Maturity Value (P) = $50000

Interest Rate (r)= 8% i.e. 4% Semi-Annual

Time (n)= 10 Years i.e. 20 Semi-Annual

Present Value of Maturity amount $50000 = [1/FVIF(4%,20)] * 50000

   = [1/(1.04)²⁰] * 50000

   = (1/2.1911) * 50000

   = .4560 * 50000

= $22800

Ans. 4: It has been assumed that the payment is made at the start of the year

Annual Payment (P) = $20000

Interest Rate (r)= 6%

Time (n)= 6 Years

Balance at end of 6th year = FVIFA(6%,6) * 20000

= [{(1.06)⁶-1)/.06}*1.06] * 20000

= [{(1.4185-1)/.06}*1.06] * 20000

= [(.4185/.06)*1.06] * 20000

= [6.975*1.06] * 20000

= 7.3935 * 20000

= $147870