on Chapter 4 discusses the time value of money, including future value and present value based upon interest rates and time. Use this formatted Excel spreadsheet to answer questions on the scenarios below. (Add information from the scenario into the yellow highlighted cells in the spreadsheet. Percentages need to be entered in decimal format, for instance 3% would be entered as .03 in the interest rate cells.) Click Submit Assignment below to type in the answer the following scenarios: 1. If Samantha invests $700 today in an account that pays 4% interest compounded annually, how much will she have in her account four years from today? How much will she have in eight years and 12 years from today? (Use Future Value of a Lump Sum) (5 points possible) 2. What is the present value of $1,500 due in 14 years at a 5% interest rate? At a 10% interest rate? Explain why the present value is lower when the interest rate is higher. (Use Present Value of a Lump Sum) (10 points possible) 3. Matt is considering the purchase of an investment that will pay him $12,500 in 12 years. If Matt wants to earn a return equal to 7% per year (annual compounding), what is the maximum amount he should be willing to pay for the investment today? (Use Present Value of a Lump Sum Amount) (5 points possible) 4. At the end of each of the past 14 years, Vanessa deposited $450 in an account that earned 8% compounded annually. How much is in the account today? How much would be in the account if the deposits were made at the beginning of each year (PMT Type) than at the end of each year? (Use Future Value of an Annuity) (10 points possible) 5. Suppose your opportunity cost interest rate/year) is 11% compounded annually. How much must you deposit in an account today if you want to pay yourself $230 at the end of each of the next 15 years? How much must you deposit if you want to pay yourself $230 at the beginning of each of the next 15 years? (10 points possible) 6. Bruce invested $1.250 (present value enter as a negative number) 10 years ago. Today, the investment is worth $3.550 (future value). If interest is compounded annually, what annual rate of return did Bruce earn on his investment? (Use Solving forr. Rate of Return on a lump Sum) (5 points possible) 7. Mario wants to take a trip that costs $4,750 (future value), but currently he only has $2,260 (present value. enter as a negative number) saved. If Mario invests this money at 7% compounded annually, how long will it take for his investment to grow to the needed amount of $4.750? Round to the nearest whole number representing the number of years. (Use Solving foin - Time on a Lump Sum) (5 points possible) E H D F G |Add information into yellow highlighted cells Definition of Terms in the Formulas: 1 Future Value of a Lump Sum Amount 2 Number of Years NE 3 Interest Rate / Year IY 4 Present Value PV= 5 Payment made each period PMT= 6 PMT Type 7 FV: $ 8 9 Future Value of a an Annuity 10 Number of Years N= 11 Interest Rate / Year I/Y 12 Present Value PV- 13 Payment made each period PMT 14 PMT Type 15 FV$ 16 17 18 Present Value of a Lump Sum Amount 19 Number of Years N= 20 Interest Rate / Year I/Y 21 Present Value PV= $0.00 22 Payment made each period PMT= 23 PMT Type 24 FV= N is the number of years. 1/Y is the interest rate per year can be divided by 12 to determine a monthly interest rate or 4 to determine a quarterly interest rate. PV is the present value. This is ALWAYS entered as a negative value showing a cash outflow. PMT is the amount of payment made each period. For lump sum problems, this is left blank since no payments are made PMT Type is a value representing the timing of payment: 1 equals payment at the beginning of the period, and for blank cell) equals payment at the end of the period. 18 25 26 Present Value of a an Annuity 27 Number of Years NE 28 Interest Rate / Year 1/ 29 Present Value PV= $ 30 Payment made each period PMT= 31 PMT Type 32 FV 33 34 Solving for r (Rate of Return) on a Lump Sum 35 Number of Years NE 36 Interest Rate / Year IY ANUMI 37 Present Value PV: 38 Payment made each period PMT= 39 PMT Type 40 FV 42 Solving for n (Time) on a Lump Sum 43 Number of Years N 10.97834233 44 Interest Rate / Year 1 7% 45 Present Value PV= $ 12,260.00) 46 Payment made each period PMT- PMT Type 48 FV= $ 4,750.00 49 Sheet1
