Computer Science and Computer Engineering 115
Solve it with the Computer!
Fall 2006

Lab 10
Review of Finances

Goal:

Review financial functions.

The exercise:

In one workbook, create a worksheet for each of following problems. Each sheet should be readable but not necessarily fancy. In multipart problems, make sure you include the part numbers so the paper grader can find your answers.

Note: The users must be able to easily change all the values (shown in italics) given in these problem to fit their special circumstances. To make it easy for users to make changes, input cells must be colored differently than other cells.

Problem 1: Joe wants to buy a new car but is not sure how expensive a car he can afford. He is willing to pay $300 a month for the next 5 years. He understands that he can borrow the funds on a special deal for 2.99% APR compounded monthly. How much can he afford to pay for the car (including taxes, licences, fees and so on.)?

Problem 2: The Northwest Youth Charities organization is investigating using a lottery for a fund raising project. They want to give away $2,000,000 (over a 20 year period with annual payments of $100,000) as the grand prize. They expect to be able to sell 400,000 tickets at $4 each.

The organization will buy an annuity for the $2,000,000 grand prize. The annuity will pay the winner $100,000 at the beginning of each year for the next 20 years. If the current interest rate is 7.2% APR, compounded annually, how much will they have to pay for the annuity? (Remember the first payment will be on the date when the prize is awarded.)

The organization will have $10,000 in other expenses to conduct the lottery.

Calculate:

1. The cost of the annuity.
2. The "profit" for the organization. That is, the total for the ticket sales less the cost of the annuity and the cost of other expenses.

Hints:

• An annuity is just a payment of a sum of money by fixed regular intallments. While we don't normally think of it that way, one could say that paying off a loan with fixed regular payments is an annuity. It is more common to say that a retirement fund that makes fixed regular payments is an annuity. It is fairly common to give another organization a lump sum from which they make those payments. Especially when this done over a long period of time, one would take into account the interest that could be earned on the funds before they are paid out when trying to determine the fair price for the one making the making the payments.

  For a very simple example, suppose that Fran needed to pay Joe $110 today and again one year from now. Fran doesn't want to monkey with this herself so she decides to pay Sue to take care of things. For simplicity, lets assume the interest rate is 10%. How much does she need to pay Sue? Sue needs $110 to make the first payment immediately. But she can invest the rest of the money at 10% for one year before she has to make the second $110 payment. So she only needs $100 for the second year. Sue will collect $10 interest on it and have the $110 needed to make the payment. So Sue will charge Fran $110 + $100 for the carrying out the task. (We are ignoring any extra charges the Sue would make to cover her time and effort involved in this transaction.)

• Any many respects, this problem is similar to the previous one even though the numbers and context are different.
• Ask yourself "What do we know?". We know the interest rate. We know the number of payments. We know that after all the payments, no money is left in the annuity. (Hopefully the winner has some money left but what the winner does with the money does not change the value of the annuity.) What don't we know? The beginning value.
• The problem is different than the other financial problems we have done because payments are made at the beginning of the year (the period in this problem) instead of at the end. If the person wins the grand prize, they collect the first $100,000 immediately. They don't have to wait for a year to collect the first payment. Excel financial functions allow for this using the 5th argument - "Type". When the "Type" is 0 or omitted, the payments are at the end of each period. This has always been the case in previous problems so we just omitted the argument. "Type" needs to be set to 1 if the payments are at the beginning of the period.

Problem 3: Finance: Create a very general spreadsheet to determine solutions to the following problems. While "beauty" is not important, "readability" is.

1. Sally is only 21. If she were to retire today, she believes she would need $2000 a month from her savings. However she will not retire for 44 years. If the annual inflation rate averages 3% per year compounded annually for that period, how much will she need per month from savings at age 65? Hint: one way to calculate this is to use the formula

   Final amount = initial amount * (1 + inflation rate)^years

   Alternatively you can consider this problem to be a future value problem with a present value of $2000 but having a periodic payment of 0. To test your formula, try calculating it for initial amount $100, interest rate 10%. The answer should be $110.00 for one year, $121.00 for 2 years and 133.10 for 3 years. When you are sure your formulas is correct, replace the simplistic values by the required values.

2. To achieve the monthly income calculated in part a during 27 years of retirement, how much will she need in her account at age 65 if she earns 8% APR (compounded monthly) on her investment? Assume the account is completely depleted after the 27 years. Note: This problem is similar to the first two problems because what we need to find is the beginning value. Unlike the second problem, payments are made at the end of the period.
3. She decides to deposit a fixed amount in an account every month for 44 years until she retires. How much does she need to put in her retirement account every month in order to have the amount calculated in part b after 44 years?
4. What if...? The inflation rate drops to only 2.9% and Sally changes her mind and decides that she would need $2200 per month in today's dollars. She decides to retire at age 67 so she needs her retirement income for 25 years. She decides that she can invest her funds at 8.5%. Determine the amount she needs each month during retirement, the amount needed in the account at age 67, and how much she needs to save each month to achieve that amount. You should be able to solve this problem by changing input values without changing any formulas.

Submitting your workbook:

1. Use the URL http://www.cs.plu.edu/submit/login.php
2. Select your class (csce115-brink, Fall 2006) from the drop down box.
3. Enter your submit system username and password.
4. Click "Login".
5. In the add Assignment box, browse to find the workbook for lab 10.
6. Select "Lab 10" for the assignment and then click "Upload".
7. Double check to make sure the file has been uploaded.
8. Click "Sign Off" near the top right hand corner of the window.

Due date:

Monday, Dec. 4. There will be a 24 hour grace period. After the grace period late submittals will not be accepted except with written permission from the instructor and will be subject to a 20% per day penalty. (Late assignments turned in over 24 hours after the due date are 2 days late and are subject to a 40% penalty.)

lab10.html, 12/4/06