solve in excel form
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 acint that earned 3.5%, compounding annually? 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? 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? 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 6 th payment will be interest? (Set up a loan amortization schedule) (c) What is his monthly payment if he makes a $5,000 down payment? (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 155 - 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? 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? 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 6 th payment will be interest? (Set up a loan amortization schedule) (c) What is his monthly payment if he makes a $5,000 dpwn payment? Goal Seek Function 1. In the Data menu, click on What if analysis and select the Goal Seek option. 2. 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). 3. In the To value box, type the result you want (e.g. maximum monthly payment). 4. 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). 5. Click OK
