I need help with question #1 .. a) and b).

Excel File Edit View Insert Format Tools Data Window Help 53% Thu 1:20 PM a FIN3312-finalexam-Summer2020-stu Q Search Sheet Home Insert Page Layout Formulas Data Review View 9+ Share Cut AutoSum Calibri (Body) 12 A- A Wrap Text General WO 49 Copy Fill - Paste B I U 7 A Merge & Center $ - % % ) 5.0 .00 .00 0 Insert Delete Format Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter C10 4 x fx A B DE F G H 0 1 M 21) 3 4 You are looking to buy a vacation home down the Jersey shore and figure that you'll need a $80,000 downpayment to put 20% down on a $400,000 shore cottage. You are looking to be able to do this in 10 years, and you have $10,000 now to start. 5 a) T If you can save nothing additionally each year, what annual rate of return must your portfolio earn to reach your objective? Round to xx.yyy% (also 0.xxyyy in decimal form) 6 7 8 Fill in the information in the cells below and then use the Excel function to find the rate of return, or describe/show in detail how you used the financial calculator to find it. A N I PMT PV FV 9 10 11 ME 12 13 14 b) Given the answer you should see to (a) ... a rate that would likely be difficult to achieve ... what if you can save $4800 annually? Find the annual rate of return your portfolio must earn to reach your objective. 15 16 Fill in the information in the cells below and then use the Excel function to find the rate of return, or describe/show in detail how you used the financial calculator to find it. Round to xx.yyy% (also 0.xxyyy in decimal form) N I PMT PV FV 10 17 0-18 : 19 20 21 22 22 23 2) 24 25 = 26 27 Now, as we saw in class (back in Session ...), investments outside of retirement plans are subject to tax when you sell. Let's assume this "portfolio" was a mutual fund (or funds) which did not pay any distributions during the 10 years, so no tax implications there. But! when you sell at the end of your 10-year horizon, you WILL trigger a tax event ... you will have generated capital gains on your investments if they're worth $80,000 at that time. a) How much is your gain, considering that you started with $10,000 and saved $4800 per year? 28 29 20 30 31 b) If the tax rate on capital gains is 15%, how much would you lose to taxes? 32 33 What does your investment portfolio have to be worth, to pay the 15% capital gains tax and still leave you with the $80,000 for your downpayment? 34 35 36 37 d) Recalculate the required annual rate of return you'd need from your investment portfolio to reach the objective in c) ... Fill in the information in the cells below and then use the Excel function to find the rate of return, or describe/show in detail how you used the financial calculator to find it. Round to xx.yyy) (also 0.xxyyy in decimal form) 38 39 40 N PMT PV FV 41 Pri Grades MFs Investment Proc Port Model CAPM Bonds EquityVal PEs and Relval + Ter Ready O + 100%