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?