Help needed, has to be in excel.
PV: Returns the present value of an investment; the total amount that a series of future payments is worth now. PMT: Calculates the payment for a loan based on constant payments and a constant interest rate. FV PV PV Given This Given This Compute This Compute This Compute This Given This 11 1 1 1 1 FV function PV function PMT function EXERCISE 3.3 continued 3. Calculate the Present Value of an annuity of 10,000 dollars, once a year for ten years. Payments are at the end of each period, starting a year from the date of closing (6% rate.) Compare the result with the value in Fig. 2.3.6, Chapter 2. (Refer to Figure 3.3.8 for timing of payments.) 4. Calculate the present value of an annuity of $50,000 each year, once a year, for twenty years, paid at the end of each year, at 6% annual rate. 5. Assume that you have accumulated $573,496.10 in your pension account at time of retirement. You decide that you will withdraw your money in 20 equal installments, once a year, over the next 20 years, starting one year after your date of retirement. Assume that the annual rate of 6% is fixed. How much will you receive each year? (Hint: Use the PMT function.) (Refer to Figure 3.3.8 for timing of payments.) 6. What is the Present value of an annuity, 10,000 each year for 10 years, and at annual rate of 6%. The first payment is 2 years form today. The second payment is 3 years from today and the last payment is 11 years from today. Hint use the Figure in section 2.3. Then try to use the PV function with an adjustment. Answers: EX.3: 73601; EX4: 573496.1; EX5: 50,000; EX6:69434.78 5. Assume that you have accumulated $573,496.10 in your pension account at time of retirement. You decide that you will withdraw your money in 20 equal installments, once a year, over the next 20 years, starting one year after your date of retirement. Assume that the annual rate of 6% is fixed. How much will you receive each year? (Hint: Use the PMT function.) (Refer to Figure 3.3.8 for timing of payments.) 6. What is the Present value of an annuity, 10,000 each year for 10 years, and at annual rate of 6%. The first payment is 2 years form today. The second payment is 3 years from today and the last payment is 11 years from today. Hint use the Figure in section 2.3. Then try to use the PV function with an adjustment. 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 =16%/12)*F5 in G5 4. Copy all other formula, fill up the entire table until row 66. ( 60 payments periods.) A B D E F G H J 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 = 2/1.06 =K2/1.06 1000 A B D E F G H J 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 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 Ex1.: Use PMT function and use Goal Seek. Set B5 to -400 by changing B3. Booki Goal Seek Status [?] Goal Seeking with Cell B5 found a solution. Step 1 Loan 2 Monthly Rate 3 Number of Months B 10000 1% 28.91180402 Pause Target value: -400 Current value: $400.00) 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. Booki Goal Seek Status ? x B Goal Seeking with Cell B5 Step 1 Loan 10000 found a solution 2 Monthly Rate 1% Pause Target value: -400 3 Number of Months 28.91180402 Current value: ($400.00) 4 OK Cancel 5 PMT ($400.00) Ans. :28.9 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 order to achieve that goal Pagepla41t0 in 147 a fixed amount o toney, once a year, into a pension fund, starting 35 years before the date of retirement (Refer to Figure 3.4.7 for 07 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
