Q1. Calculate the future value of each following pattern of cash flows: a) You deposit $12,500 today into a 10 year term deposit. The investment pays a 6.5% annual interest rate, compounding quarterly. How much does the term deposit pay you upon maturity? b) You deposit $8,750 today in a 5 year term certificate of deposit. The term deposit pays an 8.25% annual interest rate, compounding monthly. How much does the term deposit pay you upon maturity? c) You sign up for a Canada Savings Bond payroll deduction plan. Your employer deducts $200 at the beginning of each month from your wages. The CSB pays 4.0% annual interest, compounding monthly, which is added to the principal of your CSB. How much will your investment be worth in 7 years? d) You plan to retire in 15 years. To help save for retirement, you deposit $75,000 today into an RRSP mutual fund and your bank transfers $300 at the end of each month to the mutual fund. The mutual fund is expected to yield 8% compounding monthly. How much will your investment be worth in 15 years? e) You plan to invest $15,000 for 9 years in a term deposit, which pays back principal and interest on maturity. You are reviewing two investment alternatives. Circle the option which will give you the best return on investment. 1). 7.5% annual interest rate, compounding annually 2). 7.3% annual interest rate, compounding monthly Question #2 - Present Value Calculations: Financial function that you will need: PV: present value is the total amount that a series of future payments is worth in today's dollars. PV can be used to calculate the present value of an annuity or the present value of a lump sum amount received in the future. . . . - PV(rate,nper,pmt, fv, type) Rate - interest rate for each period nper - number of payment interest compounding periods Pmt - payment made each period (e.g. amount of the annuity) FV- the future value, or a cash balance you want to attain after the last payment is made. If PV is omitted, it is assumed to be zero. Type - 0: payment at end of period, 1: payment at beginning of period. If type is omitted, it is assumed to be zero. . . Q2. Calculate the present value of each following pattern of cash flows: a) You are considering an investment. A five-year term deposit will provide a 6.25% interest rate, compounding every 2 months. How much must invest today to receive $17,500 at the end of five years? b) You just signed a 6 year loan, requiring that you make a payment of $525 at the end of each month. The loan's interest rate is 8%, compounding monthly. What is the principal of the loan? c) What is the present value of receiving 3,750 at the end of each year for five years, along with another $25,000 at the end of five years, assuming a 3.25% interest rate, compounding annually? Q3. Paul buys a new Toyota Prius for $37,600. He pays a $7.250 down payment and finances the balance through CIBC bank loan over 6 years at 8%, compounding monthly. He will be making monthly car payments. (a) How much is his monthly payment? (b) How much of his 6th payment will be interest? (Set up a loan amortization schedule) (c) What is his monthly payment if he makes a $5,000 down payment? Goal Seek Function 1. 2. 3. 4 In the Data menu, click on What if analysis and select the Goal Seek option. In the Set cell box, enter the reference for the cell that contains the formula you want to resolve (e.g. cell containing payment function). In the To value box, type the result you want(e.g. maximum monthly payment). In the By changing cell box, enter the reference for the cell that contains the value you want to adjust. This cell must be referenced by the formula in the cell you specified in the Set cell box (e.g. cost of car). Click OK 5. (d) Use the Goal Seek function (tools menu) to calculate the maximum price of the car that George can afford, given a $600 monthly payment and $5,000 down. Question #4 - Net Present Value: Financial function that you will need: - NPV: Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). NPV(rate, value1,value2, ...) Rate - discount rate for one period Value 1, etc. - cash amounts at the end of each period. . . Q4. (a) How much would you invest today to receive $7,500 at the end of each year for the first five years, then $9,000 per year for the following four years? Assume an annual interest rate of 6% compounding annually (hint: list cash flows separately and use the NPV function). (b) What would those payments be worth at the end of the ninth year, assuming they were deposited in a bank account that earned 3.5%, compounding annually? Question #5 - NPER( number of periods): Financial function that you will need: NPER: calculates the number of periods for an investment based on periodic, constant payments and a constant interest rate. . . NPER(rate, pmt, pv, fv, type) rate - interest rate for each period pmt - amount of the payment pv - beginning balance (principal) of the loan fv - the future value or cash balance at the end of the loan. If fv is omitted, it is assumed to be zero. type - 0 payment at end of period, 1 payment at beginning of period. Q5. (a) How many months would it take to repay a $25,000 student loan if the interest rate is 6%, compounding monthly and the payments of $414.32 were made at the end of each month? (b) An annuity pays $40.00 each period, the appropriate discount rate per period is 6.0%, and the present value is $168.49. What is the number of periods? Question #6 - RATE (Interest Rate & Growth Rate) RATE: Calculates the interest rate per period of an annuity. If the successive results of RATE do not converge after 20 iterations, RATE returns the #NUM! value. RATE(nper,pmt,pv,fv, type,guess) nper- number of periods pmt - amount of the payment PV - beginning balance (principal) of the loan FV - the future value or cash balance at the end of the loan. Type - 0 payment at end of period, 1 payment at beginning of period. . . Q6. (a) An annuity pays $173.00 each period for 13 periods, and the present value is $513.94. What is the discount rate per period of this annuity? (b) Suppose that you were approached with an offer to purchase an investment which will provide cash flows of $1,500 per year for ten years. The cost of purchasing this investment is $10,500. If you have alternative investment opportunity, of equal risk, which will yield 8% per year, which should you accept and why