Please help. I did this project for class and my professor gave me this feedback.

"I cannot determine which problems you solved, nothing is labeled.You also need to clearly label data and use cell referencing.Do not create PV and FV formulas but please use the built in functions.Regarding the written analysis, the tables are not necessary.Please keep the analysis to a few paragraphs per problem and refer to the sample."

Please help me make this perfect!

Financial Analysis Project 1 Please select and complete 5 of these 10 mini projects.

You are the newest Financial Analyst in Investments, you need to demonstrate your prowess in Excel, your outstanding written skills and ability to communicate.

Mr. Richards is the Executive Vice President and Chief Investment officer in your new firm. You are being asked to complete series of "pet" projects for Mr. Richards. You have been told not to try to impress him, just do the work and stick to the facts. Please complete only 5 of the 10 mini? projects. Each mini?project needs to have its own tab in an Excel spreadsheet. It is critical to use cell referencing and the standard setup for TVM problems provided, he's a stickler and wants to see all of the detail. Using you must also do written part for each mini? project, making a recommendation based on the results that you obtain in Excel. Be thorough, include an introduction and conclusion to the whole body of the mini?project.

Mini?Project List:

1. A friend of Mr. Richards recently won a law suit for $30 million. They have the ability to either take the payments over 10 years or settle today for cash of $25 million today. Mr. Richard is optimistic that he can earn a 6% return on the money and that they should settle for $25 today and he will invest it for them.

Excel:

You'll need to demonstrate the present value of the $30 today versus the future value of the $25 million in 5 years to make your argument.

Written:

Briefly describe which settlement is maximizes the value for the client and explain why?

2. AclientofMr.Richardswantstopurchasealargecommercialbuilding.Thebuildingcosts$20 million and he will make a down payment of 15% and finance the rest with a local bank. The bank terms are 10 year bullet loan with 30 year amortization at 4.5% interest.

The building earns annual rent of $2,500,000 and it pays annual taxes of $1,000,000 per year. What will be the annual cash flow from the building? What will be the ROI on the investment? Given only this information, what are some of the obvious pieces missing that also need to be considered? What is your recommendation?

Excel:

Using the basic TVM setup, calculate the mortgage for the property and calculate the cash flow, and ROI for this project.

Written:

Briefly describe the analysis that you have performed, why how you calculated the cash flow, mortgage payment and ROI. Briefly give your recommendation and the list any missing risks that should be considered.

3. A client of Mr. Richards wants to purchase one of three bonds:

A) 10 year corporate bond with a 2.00% coupon, paying annually, and par value of $1,000. B) 7 year corporate bond with a 1.75% coupon, paying annually and par value of $1,000. C) 5 year corporate bond with a 1.50% coupon, paying annually and par value of $1,000.

What are the current prices for each of these bonds? How will the value of these bonds change if the respective market rates increase by 50 basis points? How will the value of these bonds change if their respective market rates decrease by 50 basis points? What recommendation would you make about purchasing one of these three bonds? Would you suggest any further analysis that might include the use of a relative interest rate risk measure used for bonds?

Excel:

Using the Basic TVM setup, calculate the value of these bonds, where FV is the par value, pmt is the current coupon (rate * par / payment frequency), PV is the current price, rate is the current market rate or in this case the coupon and rate changes, NPR is the years * M. For the rate changes, literally,copythethreebondsandthenchangetheRateto+/?.005or50BPS. Setupasummary to show the average sensitivity of each bond and for the rate changes. Include this summary in the written analysis.

Written:

Briefly describe the analysis that you have performed detailing the relative prices. Also explain how the value of the bonds change in the up/down rate changes. Provide your recommendation about which bond to buy. Discuss what risk measures should be considered.

4. Mr. Richards wants additional analysis on these bonds. He wants you to assume that a year has transpired and to make the following assumptions about the bonds: each bond is exactly 1 year shorter in term rate levels are 1.75% for 9 years, 1.50% for 6 years and 1.25% for 4 years. Calculate the value of each bond and their relative rate sensitivity from a +/? 50 BPS rate change.

Excel:

Using the Basic TVM setup from question 3. Now change both NPER and rates to those indicated above. Compare the change in value from par. Summarize this potential gain in a table and include it in the written analysis.

Written:

Briefly describe the analysis that you have performed detailing how the values changed as they rolled down the yield curve. Based on each one of these bonds rolling down the yield curve and having a gain, which bond looks to have the most gain in market value and the highest overall yield? Based on this analysis which bond would you now recommend and what additional analysis should have been performed before purchase?

5. Mr. Richard now wants you to apply the effective duration formula he learned in CFA training to the bonds at issue and 1 year forward from questions 3 and 4.

Effective Duration = (MV- ^50BPS - MV+ ^50BPS)

______________________

2 * MV ^base *50

He would also like a marginal analysis performed for both the original and the forward bond analysis. This should show the base duration and yield for the shortest bond and then the change in yield and duration for each longer bond. He explains that the 5 year is the base and the change shows the additional risk/reward for buying the longer maturities.

Excel:

Using the results from questions 3 and 4 and calculate the effective duration as shown in the formula. Be very careful to use the brackets as shown above. The durations for these bonds should all be between 1 and 3 as a range so any larger or smaller values means a formula problem. Perform the marginal analysis for both sets of bonds as indicated above and include the tables in the written analysis.

Written:

Briefly describe the analysis that you have performed detailing how the effective durations changes as the bonds down the yield curve. Based on the short bonds yield and duration, which bond appears to provide the most marginal yield for the least marginal duration? Look at the years of yield and duration (divided both by the term and then look at the marginal change for the longer bonds.) Based on this analysis which bond would you recommend at issue and why?

6. Mr. Richards has a rich client that has come to him for advice, purchase or lease a new Porsche Carrara? The car costs $108,000 and he would finance it for 84 months at a 3.5% rate with 20% down plus 6% sales tax. The lease would be for 36 months, require $10,000 cash buy down and it would cost $1,393.08 including tax each month. The client will purchase this as a company vehicle. Leasing allows full deduction of the capital buy down and the lease payment and all other related expenses. The purchase is subject to MACSR depreciation limits and deduction of interest. More importantly, the clients company earns an ROE of 10%. Should he lease or purchase? What is the real cost of the lease each month if the company tax rate is 35%?

Excel:

Use the standard TVM setup to determine the monthly payments for the purchasing the vehicle given the information provided. Calculate the total out of pocket expenses for both the purchase and the lease. Calculate the opportunity cost (not keeping capital in the company earning 10%) of each transaction. Calculate the cost of the lease after taxes.

Written:

Briefly describe the analysis that you have performed detailing the comparison of the purchase versus lease. Explain the difference in out of pocket expenses and the opportunity cost of each. Intuitively, what would be your recommendation to purchase or lease? Describe the real cost of the lease after tax and why that is important.

7. Mr. Richards is trying to update his client presentations. He would like you to perform some future value calculations showing how great his returns are and how compounding works. He would like to show how $100,000 invested for 10, 20 and 30 years grows with his superior record

of 8% annual returns. Just to underscore this he would also like the same compounding information with the historical annual market returns of 4%.

Excel:

Use the standard TVM setup to determine the FV value given the PV of $100,000, rate of .04 and .08 and NPR of 10, 20, 30. Set this up & Copy and change values as described for each of the additional five scenarios. Make a summary table showing the years and rates and the compound interest earned. Also, make a summary table showing the year and rates and calculate the overall growth of each FV net of the PV. Use both of these tables in the write up.

Written:

Briefly describe the analysis that you have performed and explain the effect of compound interest. Describe what the growth rate of the money over, 10, 20 and 30 year spans. What is the difference in growth between the 4% and 8% rates? What do these rates say about the level of overall returns required to grow money and create wealth?

8. Mr. Richards needs more analysis to update his client presentations. He would like additional future value calculations. Using the same framework as in the last mini?project, calculate the future value of bank CDs that would pay 1% and 2% annual interest.

Excel:

Copy the framework from the last mini?project and change the rates. Combine the summary tables from these analysis with the prior analysis to show the compound interest earned and overall growth rates for 10, 20, 30 years and annual returns of 1, 2, 4 and 8%.

Written:

Briefly describe the analysis that you have performed and explain the effect of compound interest. Explain how the compound interest and growth rates differ across the different returns. Provide 3 summary points that could be used in a presentation about these returns.

9. Mr. Richards has a new client will earn much higher returns than normal because of their risk profile. The client will role $75,000 into an account with the firm and then they will make additional monthly deposits of $2,000 per month for the next 25 years. He also wants to illustrate the returns for a bank CD at 2%, market returns at 4% and normal returns for him of 8%. To further illustrate the power of his returns, he wants to show the returns at 12% for 20 years.

Excel:

Use the standard TVM setup. Remember M is 12 for monthly. Setup a summary table showing the rates and overall portfolio value and one additional 20 year return. Also show the overall growth rates as in the previous analysis.

Written:

Briefly describe the analysis that you have performed and explain the effect of compound interest. Explain how the compound interest and growth rates differ across the different returns. Provide 3 summary points to sell this client for Mr. Richards.

10. Mr. Richards has a client ready to retire. They have approximately, $1.5 million in an account with the firm. They are very conservative clients and he wants to give them options. The first is to have the money paid out over 30 years earning a 2% rate, then 4%. He also wants to show them that a little risk could preserve their equity so he wants to show the monthly payout of interest only on full balance with rates of 4% and 8%.

Excel:

Use the standard TVM setup and calculate the pmt using the two different rates. Calculate the month income by multiplying the rate by the account balance for each different rate. Make a summary table show the four different methods, the rates and another column showing the balance after 30 years for each different method.

Written:

Briefly describe the analysis that you have performed and explain the effect of compound interest on the payouts. What are the benefits of paying interest only and what are the risks? If this client were retiring in good health with a life expectancy of 40 years, and current income needs of $80,000 per year (not eligible for social security) which payout strategy would you recommend for these clients and why?

1. A saver places $1,000 in a certification of deposit that matures after 20yrs and that each year pays 4 percent interest, which is compounded annually until the certificate matures. a) How much interest will the saver earn if the interest is left to accumulate? b) How much interest will the saver earn if the interest is withdrawn each year? c) Why are the answers to (a) and (b) different? Principal Tenure Interest a. Amount Interest b Interest 1000 20 years 4% 2191.123143 1191.123143 800 Difference is because when interest is allowed to accumulate, he gets interest on the interest also. 2. At the end of each year a self-employed person deposits $1,500 in a retirement account that earns 7 percent annually. a account when the individual retires at the age of 65 if the contributions start when the person is 45 years old? b) How muc the account if the individual stops making the contribution at age 65 but defers retirement until age 70? c) How much addi account if the individual continues making the contribution but defers retirement until age 70? d) Compare the answers to of continuing the contributions? How much is the difference between the two answers? Annual deposit Interest a Tenure Amount 1500 7% 20 $61,493.24 b Additional money $21,522.63 c Total amount at age 70 Additional funds $94,873.56 $33,380.32 d Difference in b and c= additional funds in continuing contributions $11,857.68 3. A saver wants $100,000 after ten years and believes that it is possible to earn an annual rate of 8 percent on invested funds. a) What amount must be invested each year to accumulate $100,000 if (1) the payments are made at the beginning of each year or (2) they are made at the end of each year? b) How much must be invested annually if the expected yield is only 5 percent? Fv Tenure Rate 100000 10 8% a Annual contribution made at the beginning of each year $6,391.62 b Annual contribution made at the end of each year $6,902.95 c Ordinary annuity Annuity due $7,950.46 $7,571.86 23. Which is the better choice when purchasing a $30,000 car: a) a four-year loan at 6 percent, b) an immediate rebate of $2,000 and a four-year loan at 8 percent? a Interest on loan (Assuming simple interest) b Interest on loan 7200 8960 Option 1 is a better choice 3. You are given the following information concerning two stocks: A B Expected return 10% 14% Standard deviation of the expected return 3.0 5.0 Correlation coefficient of the returns 2.1 a) What is the expected return on a portfolio consisting of 40 percent in stock A and 60 percent in stock B? b) What is the standard deviation of this portfolio? Stock Expected return SD Correlation coefficient Expected return of portfolio SD of portfolio A B 10% 3% -0.1 12.400% 3.117691454 14% 5% 5. What is the beta of a portfolio consisting of one share of each of the following stocks, given their respective prices and beta coefficients? Stock Price Beta A $10 1.4 B 24 0.8 C 41 1.3 D 19 1.8 How would the portfolio beta differ if (a) the investor purchased 200 shares of stocks B and C for every 100 shares of A and D and (b) equal dollar amounts were invested in each stock? Stock A B C D Sum Beta of portfolio a Stock A B C D Sum Beta of portfolio b Stock A B C D Sum Beta of portfolio Price Beta Investment 1.4 14 0.8 19.2 1.3 53.3 1.8 34.2 120.7 Beta Shares 10 24 41 19 94 1.284042553 Price 10 24 41 19 1.4 0.8 1.3 1.8 InvestmentInvestment in stock 100 1000 1400 200 4800 3840 200 8200 10660 100 1900 3420 15900 19320 1.21509434 Price Beta 10 24 41 19 1.325 Shares 1.4 0.8 1.3 1.8 InvestmentInvestment in stock 100 1000 1400 41.66667 1000 800 24.39024 1000 1300 52.63158 1000 1800 4000 5300 ays 4 percent interest, st will the saver earn if arns 7 percent annually. a) How much will be in the 45 years old? b) How much additional money will be in ge 70? c) How much additional money will be in the Compare the answers to (b) and (c). What is the effect 8 percent on invested re made at the ted annually if the an immediate rebate tandard deviation of rn on a portfolio f this portfolio? Question no.1 Amount today Over 10 years Return Settle on Present Value of $30Million today or (A) F.V 25 Where, (1+0.06)^5 Future Value Question no.2 Costs Down payment Finance Bank loan Amortisation Interest Annual rent Annual taxes Annual cash flow Interest Payment ROI on the investment $30 Million $25 Million 6% $25 Million P.V(1+i)^n 25(1+0.06)^5) 1.3382 33.46 2000000 300000 1700000 10year 30 year 4.50% $2,500,000 $1,000,000 Principal(1+(rate/100))^n 1778187.41 78187.41 (Annual inflows-Cost of investment)/Cash of investment) -28.91% Question no.3 (A) 10 year corporate bond coupon rate 2% Par value 1000 Year 1 to 10 10 Value of bond Cash flow 20 1020 Market rate increases by 50 basic points Year 1 to 10 10 Value of bond Cash flow 25 1025 Market rate decreases by 50 basic points Year 1 to 10 10 Value of bond Cash flow 15 1015 (B) 7 year corporate bond coupon rate Par value Year 1 to 7 7 Value of bond Market rate increases by 50 basic points Year 1 to 7 7 Value of bond Market rate decreases by 50 basic points Year 1 to 7 7 Value of bond (C) 1.75% 1000 Cash flow 17.5 1017.5 Cash flow 22.5 1022.5 Cash flow 12.5 1012.5 5 year corporate bond coupon rate Par value Year 1 to 5 5 Value of bond 1.50% 1000 Cash flow 15 1015 Market rate increases by 50 basic points Year 1 to 5 5 Value of bond Cash flow 20 1020 Market rate decreases by 50 basic points Year 1 to 5 5 Value of bond Cash flow 10 1010 Question no.4 Car costs $108,000 84 months Interest rate Down payment Balance Amount Sales tax 3.50% $22,896 $86,400 6% Installment 14722.88 Total price for car Balance amount Year 0 1 2 3 4 5 6 7 Question no.5 100000 For 10, 20, and 30years Annual return Annual market returns Future Value 108000 6480 114480 Installment 14722.88 14722.88 14722.88 14722.88 14722.88 14722.88 14722.88 8% 4% For 8% P.V(1+i)^n 2.16 215892.50 Future Value P.V(1+i)^n 4.66 466095.71 Future Value P.V(1+i)^n 10.06 1006265.69 DP Remaining Amt Rate Years Amount 108000 22896 85104 3.50% 7 108276.054378486 Total Expense 131172.054378486 1 Par Value 1000 Coupon 2% Rate T 10 2 1000 3 1000 1.75% 1.50% 7 5 Discount 10% Rate DF10% 6.1446 0.3855 DF10% 6.1446 0.3855 DF10% 6.1446 0.3855 DF10% 4.8684 0.5132 DF10% 4.8684 0.5132 DF10% 4.8684 0.5132 Present Value 122.8913 393.25 516.15 Present Value 153.6142 395.18 548.80 Present Value 92.1685 391.33 483.49 Present Value 85.1973 522.1384 607.34 Present Value 109.5394 524.7042 634.24 Present Value 60.8552 519.5726 580.43 10% 10% DF10% 3.7908 0.6209 DF10% 3.7908 0.6209 DF10% 3.7908 0.6209 Present Value 56.8618 630.2351 687.10 Present Value 75.8157 633.3397 709.16 Present Value 37.9079 627.1305 665.04 Leasing 36 0 months Cash down price 10000 1.000 Remaining amount 98000 Cost for each month 1393.08 For year 16716.96 PVf for third year 2.487 @10% Time period Net outflow under 41572.61 lease Lease 36months Down payment 10000 Costs 1393.08 ROE of 10% 1 2 0.909 5.8684 0.826 Interest 3024 3024 2614.54 2190.75 1752.12 1298.15 828.28 341.97 For 4% P.V(1+i)^n 1.48 148024.43 P.V(1+i)^n 2.19 219112.31 P.V(1+i)^n 3.24 324339.75 tax rate 35% Repayment 0 11698.88 12108.34 12532.13 12970.75 13424.73 13894.59 14380.91 Balance 86400 74701.12 62592.79 50060.66 37089.91 23665.18 9770.58 -4610.32 10000 50150.88 60150.88 3 4 5 6 7 0.751 0.683 0.621 0.564 0.513 0.467 0.424 0.386