Help needed please, must be in excel.

EXERCISE 3.2 1. Carol charged her credit card 10,000 dollars on December 31, 2011. The monthly interest rate charged by the credit card company is 1% of the unpaid balance at the end of each month. Carol wishes to pay off all her debt in three years, with 36 equal payments, at the beginning of each month, beginning February 1, 2012. What should the amount of her monthly payments be? Answer: $332.14 (Hint: Use the PMT function) 2. Use the set up in Section 2.4, Fig 2.4.1, to prove that monthly payments 1933.28 as calculated by the PMT function, in Fig 3.2.3, is a correct amount, and the ending balance after 60 months will become 0. Answer 3.2.2: 1. In fig 2.4.1, make the following changes: change the loan amount. Enter 100000 in B5. 2. Change the monthly payments. Enter "=1933.280153 in C6 3. Change the monthly rate. Enter =6%/12)*F5" in G5 4. Copy all other formula, fill up the entire table until row 66. ( 60 payments periods.) D 1 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 2 =B2/1.06 =C2/1.06 =D2/1.06 =E2/1.06 =F2/1.06 =G2/1.06 =H2/1.06 =12/1.06 -J2/1.06 =K2/1.06 1000 B D E F G 1 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 2 558.39 591.90 627.41 665.06 704.96 747.26 792.09 839.62 890.00 943.40 1000.00 Fig. 2.3.2 Paid A B D E H 1 Balance 2 Fixed Immedia- Interest 3 Beginning Monthly interest Principal tely After Charge for Ending 4 Month Balance Payment Paid Payment the Period Balance 50 10000 =B5 |=1%*F5 =F5+G5 6 1 =H5 =300 |=G5 =C6-D6 =B6-C6 =1%*F6 |=F6+G6 72 =H6 =300 =G6 |=C7-D7 =B7-C7 |=1%*F7 =F7+G7 8 3 =H7 =300 =G7 =C8-D8 =B8-C8 =1%"F8 |=F8+G8 94 =H8 =300 =G8 =C9-09 =B9-C9 =1%"F9 =F9+G9 Fig. 2.4.1 closing, which is the beginning of the first period. Your first payment to the bank is at the end of the first period (type =0.) FY LO AN PV Future Value Function: Payments at the beginning of each period Present Value and PMT Function: Payments at end of each period ..... 1st Pd. 2nd Pd 60th Pd 2nd Pd 60th Pd 1st Pd. Fig. 3.3.8 Fig. 3.3.7. 33 Goal 1000000 OK Cancel Fig. 3.4.2 (formula version of Fig. 3.4.1) D $5,526,59 30 10% 28 Annual Deposit 29 year 30 Annual Return 31 32 FV 33 Goal Fig. 3.4.3 $1,000,000.00 $1,000,000.00 EXERCISE 3.4 1. Mr. Kenneth borrowed 10,000 dollars at the beginning of last month. The monthly rate is 1%, and is charged at end of each month. Mr. Kenneth wishes to pay off his debt by paying $400 dollars each month, starting beginning of this month. How long will it take in order to pay off the entire debt? (Refer to Figure3.3.8 for timing of payments.) 40 2. Use Goal Seek to redo Exercise 2.3.2. Answer Exl.: Use PMT function and use Goal Seek. Set B5 to -400 by changing B3. Goal Seek Status ? X Booki 1 Loan 2 Monthly Rate 3 Number of Months 10000 1% 28.91180402 Goal Seeking with Cell B5 found a solution Target value -400 Current value: (400.00) Pause OK Cancel Ans.:28.9 5 PMT (S400.00) Answer Ex2: Use FV function and Goal Seek. Planning for Your Retirement with Goal Seek In order to be able to live comfortably after retirement, you estimate that you will need 50,000 dollars each year for 20 years, starting one year from the date of retirement. In Figure 3.4.7 EXERCISE 3.4 continued 3. Ms. Melody decided to invest 800 dollars per month in her pension fund at the beginning of each month (6% annual rate), for thirty years, before her retirement. Ms. Melody also decided that, at time of retirement, she will not take out all of the accumulated money at once. Instead, she will take her money out in 240 equal monthly installments, beginning one month after the date of retirement ( refer to Figure 3.4.7 for timing of payments.) How much will Ms. Melody be receiving each month after retirement? 4. In section 3.4 (Fig. 3.4.2) our goal has been to accumulate 1,000,000 dollars by changing your annual investment amount. An alternative way to accumulate 1,000,000 dollars is to change the number of years of investment. At a 10% fixed annual rate, and at an annual investment of 3,000 dollars, how many years will be needed to reach the goal? 5. Suppose that you wish to receive 5,000 dollars, once a month, for 20 years after retirement. You also plan to invest once a month into your pension fund for 35 year before retirement. Your investment will start 35 years before the date of retirement, and will end one month before the date of retirement. The date of first payment to you will be one month after the date of retirement. Assuming that a fixed annual rate of 6% before 42 and after retirement is guaranteed by your pension company, what amount do you need to deposit each month? (Refer to Figure 3.4.7 for timing of payments.) Answers: EX2: (1). First calculate the future value of 360 payments, $ 800 each month at a 0.5% monthly rate. Use the Future Value function. The FV is $ 807,630.09. (2) Next use the mortgage payment function (PMT) to calculate the x $D$32 D 28 Annual Deposit 5000 29 year 30 30 Annual Return 0.1 31 32 Fv =- FV(D30,029,028,0,1) 33 Goal 1000000 Fig. 3.4.2 (formula version of Fig. 3.4.1) Goal Seek Set cell: To value: By changing cell: 1000000 $D$281 OK Cancel 28 Annual Deposit 29 year D $5,526,59 30 10% 30 Annual Return 31 32 FV 33 Goal Fig. 3.4.3 $1,000,000.00 $1,000,000.00 and after retirement is guaranteed by your pension company, what amount do you need to deposit each month? (Refer to Figure 3.4.7 for timing of payments.) Answers: EX2: (1). First calculate the future value of 360 payments, $ 800 each month at a 0.5% monthly rate. Use the Future Value function. The FV is $ 807,630.09. (2) Next use the mortgage payment function (PMT) to calculate the payment amount, using the future value in step (1) as the Present value. The monthly interest rate is 6%/12 (or 0.5%) and the number of payments equals 240. The calculated monthly payment (PMT) is $5,786.11 EX3: (make sure that B4 is a Future Value formula): Goal Seek -3000 10% OK Cancel B 1 Annual Deposit Set cel: $B$4 2 Number of Years: 30 To yalue: 1000000 3 Annual Rate By changing cel: $B$2 4 FV $542,830.27 5 Click the OK. The answer is 36.13 year. EX4: You have to use the Goal Seek function. In the following chart I randomly assumed that the monthly deposit before retirement is $300. My monthly payment received after retirement turned out to be $3,077.47. Use Goal Seek by setting cell D4 to value 5000. The monthly deposit will become $487.42. B D A 1 Monthly Deposit 300 PV =B4