Need help with formula to set up Excel spreadsheet?

I am needing to set up an Excel spreadsheet based on the following word problem.......





Suppose Mary has $6,000 to invest. She divides the money in three parts and found a CD (certificate of deposit), a simple passbook savings account, and a money market account into which she places her investment dollars. She puts three times as much into the CD as she puts in to the passbook savings account. She puts 500 less in the passbook savings account than in the money market account. The passbook savings account pays interest at the lowest rate of the three investment vehicles, and the CD pays interest at the highest rate. Each investment vehicle鈥檚 rate differs from the vehicle with the next highest rate by 1.5%. How much does each savings vehicle pay in simple annual interest if the total value of her investment after 1 year is $6499.50?


Hint: Using a Spreadsheet, solve first part of problem: How much will Susan have to invest to Pass Book Saving, to Money Market and CD. Using this data create another table and use absolute and relative addresses of cells. Below you can find an example of a column heading for second table and one row of right results for your reference. Remember, you can solve this problem differently, using one complicated table for example. For people with a mathematical mind I would recommend you solve this problem on the paper algebraically at first. At least you would understand that table better begin with 6% value and increasing it by 0.025%. If you will start with very low % value you will have to look through a thousands rows.


%Interest for Pass AccountInterest for Money Market AccountInterest for CD accountTotal Value of Investment


6.000%661202976483.00|||I labeled three columns Passbook, CD, and Money Market.


In A2 I put 100, just to get started. In B2 I placed = 3*A2, and in C2 I placed = A2 + 500. Then in D2 I placed = sum(A2:C2).


(I'm doing this in Open Office, so the formulas might be a little different). Then I changed the number in A2 until the answer in D2 became 6000. the answers become 1100, 3300, and 1600.





Now in A3 place =A$2*(D3-0.03)


in B3 place =B$2*D3


in C3 place = C$2*(D3-0.015)


In D3 place an estimate of the interest. .09


in E4 place = sum(A4:C4)


in D4 place = D3 + .001


Drag everything down for a few rows.


Now keep changing the values in D3 until one of the values in E4 becomes 499.5, the amount of interest earned.


My answer is 0.09275. That's the highest interest rate.|||That is a long question. I would need an hourly fee to work on that one. but you might im me on yahoo for some free help. jimcmillan is my id. In real life, I might write a program in basic to get the answer you are looking for. But algebraically may be the most straight forward.|||Aye. It's a long problem. I can't exactly help you with the equation without actually working out the problem myself.





So the only thing I can tell you is





=(cell%26lt;operand%26gt;cell) is how you make excel equations.|||I got the answer through my financial calculator. I'll figure it out through Excel and send you the info in about 30 minutes. I have a MBA in Mathematics and Economics.