Can you complete the below questions I need the answers in an Excel spreadsheet I need it by this time tomorrow (24 hours)? The PDF attachment is for one of the questions on the Word document (the last three questions) let me know if you have any questions.

Week 3 Questions Problem Set 1: $ Ret and % Ret One year ago, you bought a stock for $36.48 a share. You recently received a dividend of $1.62 per share and sold the stock today for $40.18 a share. What is the dollar return and percentage return on this investment? One year ago, you bought 480 shares of ABC Company for $28.32 a share. You recently received a dividend of $0.75 per share and sold the stock today for $35 a share. What is the dollar return and percentage return on this investment? Problem Set 2: Avg Ret, Var and Std of Ret Over the past five years, a stock provided annual returns of 12.6 percent, 5.8 percent, 7.9 percent, -11.2 percent and -2.4 percent. What is the arithmetic average return? What is the variance of these returns? What is the standard deviation of these returns? A company had returns of 5%, 10%, -15%, 20%, -12%, 22%, 8% in the last few years. Compute the arithmetic average return and standard deviation of returns. Refer to Problem 2. If inflation rate is 2.5% and the risk-free rate is 3%, solve for the real rate and the risk premium. Note: Use arithmetic average return as nominal rate Problem Set 3: Fisher Equation Suppose the nominal rate is 15%, the real rate is 10.5%, what is the inflation rate? If the investors require a 10% real rate of return and the inflation rate is 8%, what is the nominal rate? The nominal rate is 15.5% and the inflation rate is 5%, what is the real rate? Problem Set 4: Portfolio Ret and Portfolio Beta Assume that your portfolio comprises of Stocks A, B, and C. Based on the following information, calculate the portfolio expected return and portfolio beta: Security Value Return Beta Stock A $10,000 10% 1.1 Stock B $20,000 5% 0.2 Stock C $30,000 15% 2.1 Problem Set 5: Portfolio Ret and Portfolio Beta A stock has a beta of 0.90, the expected return on the market is 13%, and the risk-free rate is 6%. What must the expected return on this stock be? A stock has an expected return of 17%, the risk-free rate is 5.5%, and the market risk premium is 8%. What must the beta of this stock be? You own a portfolio that has $1,200 invested in Stock A and $1,900 invested in Stock B. If the expected returns on these stocks are 11% and 16%, respectively, what is the expected return on the portfolio? Problem Set 6: Portfollio Return There are 3 stocks in a portfolio: Stock A, Stock B, and Stock C. The portfolio has a return of 3.3%. You are given below the weight of each stock in the portfolio and rate of return. Calculate the return for Stock C. Stock Weight Rate of Return A 35% -4% B 60% 7% C 5% ? Problem Set 7: Beta Consider the previous example with the following four securities Security Weight Beta DCLK .133 4.03 KO .2 0.84 INTC .267 1.05 KEI .4 0.59 What is the portfolio beta? Which security has the highest systematic risk? Which security has the lowest systematic risk? Is the systematic risk of the portfolio more or less than the market? Problem Set 8: CAPM Assume Risk-free rate = 3%, Expected return on the market = 8%. Calculate the expected return on the stock if the beta is 0 0.5 1 2 Interpret your answers Problem Set 9: Portfolio Beta A $100,000 portfolio is invested in a risk-free security and two stocks. The beta of stock A is 1.80 while the beta of stock B is 0.20. One-half of the portfolio is invested in the risk-free security. How much is invested in stock A if the beta of the portfolio is 0.75? Problem Set 10 (Problems 6-8, 6-10 from end of chapter) Suppose you hold a diversified portfolio consisting of $7,500 investment in each of 20 different common stocks. The portfolio's beta is 1.12. Now, suppose you sell one of the stocks with a beta of 1.0 for $7,500 and use the proceeds to buy another stock whose beta is 1.75. Calculate your portfolio's new beta. You have a $2 million portfolio consisting of a $100,000 investment in each of 20 different stocks. The portfolio has a beta of 1.1. You are considering selling $100,000 worth of one stock with a beta od 0.90 and using the proceeds to purchase another stock with a beta of 1.40. What will the portfolio's new beta be after these transactions? Problem Set 11: Portfolio Beta You want to create a portfolio equally as risky as the market. Given this information, fill in the rest of the following table: Asset Investment Beta Stock A 20% 0.8 Stock B 25% 1.3 Stock C ? 1.5 Risk-free Asset ? ? Hint: Some questions to answer before you start solving the above table: What is the beta of the market portfolio? risk-free asset? What should the weights of individual securities in a portfolio sum to? Bottom of Form Problem Set 12: CAPM The Treasury Bill rate is 4%, and the expected return on the market portfolio is 12%. What is the risk premium on the market? What is the required return on an investment with beta of 1.5? If the market expects a return of 11.2% from Stock X, what is its beta? Problem Set 13: CAPM A stock has a beta of 1.2 and an expected return of 16%. A risk-free asset currently earns 5%. What is the beta of the risk-free asset? What is the expected return on the portfolio that is equally invested in the two assets (i.e. stock and riskfree asset)? If a portfolio of the two assets has a beta of 0.75, what are the portfolio weights? If a portfolio of the two assets has an expected return of 8%, what is its beta? Market Efficiency: Concept Questions If the security prices reflect only past prices and trading volume information, then the market is (choose one from below): weak-form efficient semi-strong form efficient strong-form efficient What is the difference between intrinsic value and market value of the asset? The instrinsic value of an undervalued asset is likely _________ than the asset's market value. Real-world: Diversification Benefits during Market Crashes Please attached PDF to answer this question Download the monthly Adjusted Closing Price for Target Corp. (Ticker = TGT) and S&P 500 (ticker = ^GSPC) from Yahoo website from October 31, 2009 to October 31, 2014 and calculate following: Average monthly return for Target and S&P 500 Variance of returns for Target and S&P 500 Standard deviation of returns for Target and S&P 500 Covariance between Target and S&P 500 Correlation coefficient between Target and S&P 500 Beta for Target Instructions for downloading the data from Yahoo!: To obtain the monthly data for each company, on Yahoo! Finance website, enter the ticker symbol under Quote Lookup. Then, click on \"Historical Data\". Enter \"Time Period\" as given above. For \"Frequency\FIN\t5130 Prepared\tby\tDr.\tPalkar Real-world\tApplication\ton\tBenefits\tof\tDiversification\tduring\tCrisis What\twe\tknow\tfrom\tthe\tlecture\tmaterial: Market\tRisk:\tcannot\tbe\teliminated\tby\tdiversification Unsystematic\tRisk:\tcan\tbe\teliminated\tor\tgreatly\treduced\tby\tdiversification S&P\t500\tportfolio:\tproxy\tfor\tmarket\tportfolio Note:\tFigure\tcreated\tusing\tCRSP\tdata Understanding\tthe\tFigure Period\tcovered:\t1962-2015 The\torange\tgraph\tis\tmarket\trisk,\tshows\tthe\thistorical\tvolatility\tof\tthe\tS&P\t500 portfolio. The\tblue\tgraph\tis\tunsystematic\trisk,\tshows\tthe\taverage\tvolatility\tof\tthe individual\tstocks\tin\tthe\tportfolio\t(as\tweighted\tby\ttheir\tmarket\tcapitalization). What\tdoes\tthe\tfigure\tshow? The\tfigure\tshows\tthat\tmarket\trisk\tclearly\tincreases\tduring\ttimes\tof\tcrisis. o Market\trisk\tincreased\tto\taround\t70%\tin\t2008. The\tfraction\tof\trisk\tthat\tcan\tbe\tdiversified\taway\talso\tvaries,\tand\tseems\tto\tdecline during\tperiods\tof\tcrisis. o In\tmost\tyears,\tthe\tblue\tarea\tis\tabout\t50%\tof\tthe\ttotal.\tThat\tmeans,\ton average,\tabout\t50%\tof\tthe\tvolatility\tof\tindividual\tstocks\tis\tdiversifiable. o But,\tduring\tthe\t1987\tcrash\tand\tthe\t2008\tfinancial\tcrisis,\tthis\tpercentage fell\tdramatically,\tso\tthat\tonly\tabout\t20%\tof\tthe\tvolatility\tof\tindividual securities\tcould\tbe\tdiversified. Thus,\talthough\tyou\tare\tbetter\toff\tdiversifying,\tit\tis\timportant\tto\tkeep\tin\tmind\tthat the\tbenefits\tof\tdiversification\tdepend\ton\teconomic\tconditions.\tIn\ttimes\tof\tcrisis,\tthe benefits\tof\tdiversification\tgo\tdown,\tmaking\teconomic\tdownturns\teven\tmore\tpainful for\tinvestors. FINA 362 SET 1 Coupon Rate NPER RATE PV PMT FV PV Chapter 6 Instructor: Dr. Palkar BOND RELATIONSHIP I B 10% 20 8.00% ? $100.00 $1,000.00 $1,196.36 A 10% 20 10.00% ? $100.00 $1,000.00 $1,000.00 If coupon rate = discount rate Bond will sell AT PAR If coupon rate > discount rate Bond will sell AT PREMIUM in interest rate in value The change in value caused by changing interest rates is called interest rate risk. SET 2 Coupon Rate NPER RATE PV PMT FV PV A 10% 10 10.00% ? $100.00 $1,000.00 $1,000.00 BOND RELATIONSHIP II B 10% 10 8.00% ? $100.00 $1,000.00 $1,134.20 Comparing Set 1 and Set 2, reducing the maturity rate, other things remaining same => the bond-holder holding a long term bond is exposed to greater interest rate risk than when owning a short term bond. BOND RELATIONSHIP III SET 3 N I/Y P/Y PV PMT FV PV A 20 10.00% 1 ? 50 1000 $574.32 Comparing Set 1 and Set 3, reducing the coupon rate, other things remaining constant => low coupon rate bonds are subject to more interest rate risk thanhigh coupon rate bonds. FINA 362 Chapter 6 C 10% 20 12.00% ? $100.00 $1,000.00 $850.61 If coupon rate greater interest rate risk than when her things remaining constant => isk thanhigh coupon rate bonds. Instructor: Dr. Palkar FIN 5080: Chapter 5 Important Formulae Dr. Palkar 1) Variables needed in Bond Valuation calculation NPER = remaining time to maturity RATE = YTM, Yield, quoted rate, nominal rate, required rate, discount rate, before-tax cost of debt PV = Today's price of bond PMT = Periodic Coupon Amount based on par value and coupon rate FV = Par Value Unless otherwise mentioned, assume that i. Par value of bond is $1,000 ii. Payments are made semi-annually 2) Changes to be made when solving for YTC NPER = remaining time to first call FV = Call price Rest is similar t o solving for YTM 3) Current Yield Current Yield = Annual Coupon Amount/ PV of Bond Note: Even if the bond pays semi-annual payment, you will still put Annual coupon in the numerator of Current Yield formula 4) Bond Relationships i. Interest Rate Risk: changes in the bond prices due to changes in the market rate. Coupon Rate = YTM: Bond will sell at par Coupon Rate > YTM: Bond will sell at premium Coupon Rate 10% 8.00%