This is using excel

1. Mary has just completed her undergraduate degree from Northwestern University and is already planning on entering an MBA program four years from today. The tuition will be $20,000 per year for two years, payable at the beginning of each year when she starts the program. Calculate the amount that Mary must save at the end of each of years 1-3 to cover her tuition expenditures. Interest rates are 7%.
=FV(0.07,2,-20000,,1) = 44,298

2. James would like to retire 15 years from today and receive a pension of $60,000 every year for twenty years and receive the first payment 15 years from today. He can borrow at a rate of 7%, compounded annually. He believes that he will be able to begin saving at the beginning of the sixth year. Calculate the amount that James must save to cover his retirement requirements.
=FV(0.07,20,,-60000) = 232,181.07

3. Congrats! You just had a boy! Assume the total cost of a college education will be $200,000 when he enters college in 18 years. You presently have $27,000 to invest. What annual rate of interest must you earn on your investment to cover the cost of his college education?
= RATE(200000/27000,18,-27000,200000) = 31%



I don't need the answers but can you help me start these 2 questions, What method would I use?



4. Michael is trying to save money; he recognizes that he could save $2 every day by ordering regular coffee instead of latte at the local coffee shop. Since he buys a cup of coffee every workday (5 days a week), he wants to determine how much money he will have accumulated as a result of this drastic savings measure, if he is 25 years old today and expects to retire at age 65 (interest rates are 4% and assume the savings take place at the end of each year).

5. You are desperately trying to save to buy a new Ferrari. You have $40,000 today that can be invested at your bank. The bank pays 5.5% annual interest (reality?) on its accounts. How long will it be before you have enough to buy the car?

On the first one, note that it wouldn't make sense for Mary to have to invest 44,298 each year for three years to cover the tuition. The cost will only be 20,000 paid twice; even if she earned zero interest on her investment, a single savings of 44,298 would be more than enough.

First use the PV function to determine the present value of those two 20K tuition payments, at 7%. This will be the amount Mary needs to have at the end of the third year, since the PV function delivers the present value as of one period prior to the first payment.

Then use the FV function to figure how much she'll have to put into the investment at the end of the next three years, in order to accumulate to exactly that amount you calculated in the first step. You'll just have to try different inputs into the "payment amount" argument of the FV function, until it outputs the required amount. If you're familiar with Excel's Goal Seek utility, you could use that to speed up the process.

For the second one, first use PV to calc the present value of 60K per year for 20 years. This will give you the amount James must have 14 years from today (one year before the first withdrawal). Then, just as with the first problem, use the FV function to determine what annual payment amount, at 7% and for 10 years, will produce that amount (if his first savings investment is 5 years from today, and the final one will be 14 years from today, that's 10 investment savings in all).

The third one sets up as



Use Excel to solve for r.

In the fourth one Michael is going to sock away 2 x 5 x 52 = $520 at the end of each year for 40 years. Use the FV function.

You didn't say what the car's price is, in the last one, but let's say it's F. The problem sets up as



You can solve for n either by taking logarithms or via a little trial-and-error work in Excel.

Quote:

---

Originally Posted by ArcSine

On the first one, note that it wouldn't make sense for Mary to have to invest 44,298 each year for three years to cover the tuition. The cost will only be 20,000 paid twice; even if she earned zero interest on her investment, a single savings of 44,298 would be more than enough.

First use the PV function to determine the present value of those two 20K tuition payments, at 7%. This will be the amount Mary needs to have at the end of the third year, since the PV function delivers the present value as of one period prior to the first payment.

Then use the FV function to figure how much she'll have to put into the investment at the end of the next three years, in order to accumulate to exactly that amount you calculated in the first step. You'll just have to try different inputs into the "payment amount" argument of the FV function, until it outputs the required amount. If you're familiar with Excel's Goal Seek utility, you could use that to speed up the process.

For the second one, first use PV to calc the present value of 60K per year for 20 years. This will give you the amount James must have 14 years from today (one year before the first withdrawal). Then, just as with the first problem, use the FV function to determine what annual payment amount, at 7% and for 10 years, will produce that amount (if his first savings investment is 5 years from today, and the final one will be 14 years from today, that's 10 investment savings in all).

The third one sets up as



Use Excel to solve for r.

In the fourth one Michael is going to sock away 2 x 5 x 52 = $520 at the end of each year for 40 years. Use the FV function.

You didn't say what the car's price is, in the last one, but let's say it's F. The problem sets up as



You can solve for n either by taking logarithms or via a little trial-and-error work in Excel.

---

1. Present Value =PV(0.07,2,-20000)= $36,160.36
Future Value =FV(0.07,3, -$36,160.36)= $44,298.00

2. Present Value =PV(0.07,20, -60000)= $15,505.14
Future Value =FV(0.07,10, -15,505.14) = $30,500.96

3. Rate Return =RATE(18, -27000,200000) = 12%

4. Stock Away =2*5*52 = 520
Future Value =FV(0.04,40,-520) = $49,413.27

5. =40000*1.055 = 42,200

You're good on (3) and (4).

On (1) you've got the PV part correct. But then the question wants to know how much will she have to put into the investment account each year for 3 years in order to accumulate to $36,160. In that FV function, you need to keep changing that third argument until it gives 36,160 as the result:

=FV(0.07, 3, some amount) = 36,160.

Two problems with (2). First, something went haywire on your first step; re-do that PV calculation. Then, the approach to the answer is similar to what you have to do with the second step of (1) as I've described above.

(5) is asking how long before your 40K investment grows, at 5.5%, to an amount sufficient to buy the car. Your proposed answer---42,200---seems like a rather long time to wait for a car. Re-think this one. Excel is a neat little time-saving device, but it does not replace the need to understand what's in the textbook.