Question

Instructions: After you have completed the calculations on your spreadsheet that correlates to the questions below, you will copy and paste the portion of that spreadsheet into the Word document for each of the questions. Be sure to also provide a sentence that explains the solution.

To save for her newborn son ’s college education, Lea Wilson will invest $1,000 at the end of each year for the next 20 years. The interest rate is 10%. What is the future value? Answer: Excel sheet - copy and paste into this document

Sharon Smith will receive $1 million in 20 years. The discount rate is 10%. As an alternative, she can receive $200,000 today. Which should she choose? $200,000 Answer: Excel sheet - copy and paste into this document Dr. J. wants to buy a Dell computer that will cost $3,000 three years from today. He would like to set aside an equal amount at the end of each year in order to accumulate the amount needed. He can earn an 8% annual return. How much should he set aside at the end of each year? Answer: Excel sheet - copy and paste into this document

You will deposit $200,000 today. It will grow for five years at 12% interest, but compounded semi-annually. What will your investment grow to? Answer: Excel sheet - copy and paste into this document

John Doeber borrowed $150,000 to buy a house. His loan cost was 6% and he promised to repay the loan in 10 equal annual payments. What are John’s annual payment amounts? Answer: Excel sheet - copy and paste into this document

An organization would like to expand a large piece of equipment in their factory that would help make the process more efficient in the future. If they are able to invest $400,000 for an initial payment, determine the approximate future value to begin installation at the beginning of the third year. We can assume an interest rate of 7% annually. Answer: Excel sheet - copy and paste into this document

After 10 years, some shares of stock originally purchased for $500 total were sold for $900 total. What was the yield on the investment? Hint PV is input as a negative value Answer: Excel sheet - copy and paste into this document

Kathy has $50,000 to invest today and would like to determine whether it is realistic for her to achieve her goal of buying a home for $150,000 in 10 years with this investment. What return must she achieve in order to buy her home in 10 years? Hint PV is input as a negative value Answer: Excel sheet - copy and paste into this document

Answer 1

1]

Future value is calculated using FV function in Excel :

rate = 10% (interest rate)

nper = 20 (number of years)

pmt = -1000 (Yearly investment. This is entered with a negative sign because it is a cash outflow)

FV is calculated to be $57,275.00

2]

Sharon Smith should choose the option with the higher present value.

Present value of receiving $1 million in 20 years is calculated using PV function in Excel :

rate = 10% (interest rate)

nper = 20 (number of years)

pmt = 0 (yearly payment is zero)

fv = -1000000 (Amount received after 20 years. This is entered with a negative sign because we are calculating PV of an amount received in the future)

PV is calculated to be $148,643.63

Sharon Smith should choose to receive $200,000 today because it is higher than the present value of receiving $1 million in 20 years.

3]

We calculate the yearly saving required to accumulate the required amount in 3 years using PMT function in Excel :

rate = 8% (rate of return earned)

nper = 3 (number of years)

pv = 0 (amount currently saved is zero)

fv = 3000 (required amount in 3 years)

PMT is calculated to be $924.10

4]

Future value is calculated using FV function in Excel :

rate = 12% / 2 (convering annual interest rate into semiannual rate)

nper = 5 * 2 (total number of semiannual periods = number of years * 2)

pmt = 0 (Yearly investment is zero)

pv = -200000 (amount invested today)

FV is calculated to be $358,169.54

FV is calculated to be $358,169.54