Question

Answer the following problems using an Excel spreadsheet. All calculations MUST use Excel's Time Value of Money Functions (demonstrated in class) as well as cell references to receive FULL credit. Make sure to set up the data for each problem so you can "point" to the appropriate values in your formulas. Each problem is worth 20 points. This assignment must be completed INDIVIDUALLY. THIS IS NOT A GROUP ASSIGNMENT. The file you submit in CANVAS must be one that YOU manually typed/entered into Excel. Failure to adhere to the Nichols College Academic Honesty Policy will result in consequences outlined in the student handbook. Problem #1 -- Assume the total cost of a college education will be $345,000 when your child enters college in 18 years. You presently have $73,000 to invest. What annual rate of interest must you earn on your investment to cover the cost of your child's college education? Problem #2—You expect to receive $15,000 in two years. You plan on investing it at 9 percent until you have $75,000. How long will you have to wait from now? Problem #3—Jillian invested $2,000 six years ago at 4.5 percent interest. She spends all of her interest earnings immediately so she only receives interest on her initial $2,000 investment. How much interest is she earning each year? Problem #4-You have just made your first $5,000 contribution to your retirement account. Assume you carn a return of 10 percent per year and make no additional contributions. a. What will your account be worth when you retire in 45 years? b. What if you wait 10 years before contributing? c. After calculating parts a and b, what is the lesson learned? Problem #5—You are committed to buying a $225,000 backhoe for your new construction business. If you believe your mutual fund can achieve a 12% annual rate of return and you want to buy the car in 9 years for your 30th birthday, how much must you invest today?

Answer 1

Problem1:

r: Annual interest rate

Amount = Principal*(1+r)^t

345000 = 73000*(1+r)^18

(345000/73000)^(1/18) = 1+r

1.09 = 1+r

r = 0.09 = 9% pa

Problem 2:

After 2 years Principal = 15000

Amount (time t beyond 2 years) = 75000

r = 9%

A = P*(1+r)^t

75000 = 15000*(1+9%)^t

75000/15000 = 5 = 1.09^t

taking ln on both sides

ln5 = ln1.09^t

ln5 = t*ln1.09

t = ln5/ln1.09 = 18.68years

Therefore total time = 18.68 + 2 = 20.68 years

Problem 3

Interest earned per year = 4.5% on 2000 = 90

Problem 4:

After 45 years

Amount = 5000*(1+10%)^45 = 5000*1.1^45 = 364,452.4

If invests after 10 years

Amount = 5000*(1+10%)^35 = 5000*1.1^35 = 140,512.18

Lesson learnt, start investing early

Problem5:

Amount = Principal*(1+r)^t

225000 = Principal*(1+12%)^9

Principal = 225000/1.12^9 = 81137.3