SAVE YOUR FILE AS

LASTNAMEFIRSTNAME

e.g. BealLauraPlease type in your name hereDue dateOctober 30th, 11:59 pm.Points100REQUIREMENTS TO OBTAIN ALL 100 POINTSINPUT DATADefinitionExcel function nameAnswers MUST be derived using cell references using a formula OR Excel function.5.00%nominal interest ratenominalJust typing in your answer derived using your calculator will NOT result in any points.$5,000.00Present valuePVYou MUST FORMAT ALL of your interest rates as PERCENTAGES with 2 DECIMALS.1.00Total Number of yearsnperYou MUST FORMAT ALL of your dollar amounts as CURRENCY with 2 DECIMALS.1# of compounding periods per yearnperyYou MUST display all of your answers in the blue shaded cells below.0paymentPMT0Future ValueFV0effective interest rateeffectFor questions 1 - 4 refer to the input data box highlighted in yellow above. You need to adjust your inputs as necessary. (The first question is done for you).1. What is the future value of a 1 year Certificate of Deposit (CD) and what is the effective annual rate under each of the following scenarios?Future ValueEFFa. annual compounding?($5,250.00)5.00%b. semi-annual compounding?c. quarterly compounding?d. monthly compounding?e. daily compounding?2. What is the future value of the CD in 5 years and what is the effective annual rate under each of the following scenarios?Future ValueEFFa. annual compounding?b. semi-annual compounding?c. quarterly compounding?d. monthly compounding?e. daily compounding?3. Assume you want to invest $1000 into your savings account at the END of every year for the next 5 years.Future Valuea. What is the future value in year 5, if interest is compounded annually?b. What is the future value in year 5 if interest is compounded semiannually? Hint: You MUST use EFF for your interest rate4. Rework question 3, assuming payments are made at the BEGINNING of each period.Future Valuea. What is the future value in year 5, if interest is compounded annually?b. What is the future value in year 5 if interest is compounded semiannually? Hint: You MUST use EFF for your interest rateFor problems 5 & 6 use required nominal annual return of :8.00%5. Consider the following end-of-year cash flows:YearCash flow0$0.001$40.002$60.003$60.00Net Present Value (NPV)a. What is the present value of these cash flows (in year 0)?b. If the purchase price of this investment is $140 today, would you buy it? Why? (Compare instrinsic value to actual price)c. What is the expected rate of return on this investment if the purchase price is $140?YearCash flowInternal Rate of Return (IRR)0-$140.001$40.002$60.003$60.00d. Would you buy this investment based on your answer to part c. and why?(Compare expected return to required return).6. Consider the present value of the following different investments.Present Value Recall the nominal rate8.00%a. What is the present value of $1,000,000, due 25 years from now?b. What is the present value of a $40,000 ordinary annuity for 25 years? (Hint: $40,000 is the payment)c. What is the present value of a $40,000 perpetuity, if the first payment is 1 year from now?d. What is the present value of a $40,000 perpetuity, if the first payment is now?7. You take out a loan today and will make equal annual payments for the next 3 years, starting in 1 year.Amount borrowed50,000a. What is the value of the annual payment if the nominal interest on the loan is:6.00%Paymentb. Show the amortization of the loan in the table set up below:YearBeg. BalancePaymentInterestPrincipalEnding Balance123TOTAL. This is finance class. pls someone help