# Do all of your calculations within the spreadsheet so that your

### Question Description:

35

Do all of your calculations within the spreadsheet so that your solution process can be checked. If cell calculations are not there, it will be assumed that they did not happen and points deducted. The time value of money is relevant to all questions. Assume all transactions happen at the end of periods unless indicated differently. Assume annual compounding if not stated differently. Assume that “today” is the last day of year 0. State any additional assumptions clearly. Format rates as percentages with two decimal places. Some questions may have information that is not needed. Copy of 2013-1.xls Midterm Exam, Summer 2013 Name Question 1 2 3 4 5 6 7 8 9 10 Late Total Score 0 0 0 0 0 0 0 0 0 0 0 0 Question 1 Score 0 Jenny’s New year’s resolution is to start a retirement fund . She has opened an account with a local broker by depositing \$1000 in an investment fund. In the future she plans to invest \$500 monthly into the fund. The investment funds averages a return of 6% APR with monthly compounding a What will she have available for retirement after 30 years? b If Jenny wants a retirement fund of \$1 million when she retires in 30 years, how much will she have to invest each month with everything else the same? Solution Question 2 Score 0 Juan is evaluating his retirement plan. He estimates that he will have saved \$750,000 when he retires in an account that earns at an APR of 4.00% compounded monthly. He plans to withdraw \$60,000 annually at the end of each year after retiring. a How many years will Juan’s funds last? b To make the funds last 30 years, what APR (with monthly compounding) will he need? Solution Question 3 Score 0 Peggy Sue has \$9,000 balance on a credit card today. She is committed to pay it off. The rate on the card is 18% APR with monthly compounding. a How many months would it take for Peggy Sue to reduce the balance to zero if she makes no more charges and makes an electronic payment of \$250 today and then every month thereafter makes a payment of \$450? b How many months would it take Peggy Sue to reduce the balance to \$1,000 if she makes no more charges and makes a payment at the end of every month of \$500 (no electronic payment today)? c If part a of this question is changed to making a payment of \$100, (everything else the same) the function yields a #NUM! error. Why is this? Solution Question 4 Score 0 Honest John’s Car Dealership needs an easy way to determine monthly payments for customers who are inquiring about cars. John wants a spreadsheet the will allow salespeople to enter the price of the car, the customer’s down payment, length of the loan in years, and the APR (monthly compounding). For any values of these, the spreadsheet should automatically display the monthly payment. Prepare this spreadsheet. Solution Question 5 Score 0 Pat Davis has \$750,000 in a retirement fund. Pat plans to start taking out funds at the end of the coming year. He expects that as he ages, he will need less money each year. His plan is to take out 12 monthly payments of \$10,000 in the first year. In the second year he would take out 5% less or 12 payments of \$9,500. Each year thereafter the payments would be similarly reduced by 5% less than the previous year, etc. The fund earns interest at a rate of 4% EAR. a How much will be in the fund at the end of five years. b How long will the funds last for Pat with this scheme? Solution Question 6 Score 0 It has been proposed to Ruby that she invest \$300 at the end of each month (starting at the end of the upcoming month) for 40 years in a high risk fund (no initial deposit) that earns 11% APR with monthly compounding, and then switch the funds in her retirement years to a more conservative fund that earns a guaranteed 4% APR with monthly compounding. Assuming that the high risk fund indeed pays at the stated rate, how much could she withdraw (equal amounts) at the start of each year during a 25 retirement period. Solution Question 7 Score