Published by
Guru Raven

Subject Finance Topic Other

Academic Level : Bachelor

Paper details;

This exercise will require both a written and Excel spreadsheet component. It is critical to

demonstrate the appropriate Excel skills. There are 10 mini projects that you are required to

complete. Carefully read additional requirements for each mini project. Using APA format, you

must also complete a 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

• Submit a Word file for the written component, addressing all the key points along with

appropriate and relevant examples. APA format is required.

• Submit a spreadsheet Excel file for the spreadsheet component, demonstrating the use

of formulas, cell referencing, and calculation with appropriate set up.

This exercise #1 is due by the end of Module 2.

Scenario

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 a 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. There are ten mini-projects for you to

complete. 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.

Mini-Projects

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 million today and he will invest it for them.

Excel:

You’ll need to demonstrate the present value of the $30 million today versus the future value of

the $25 million in 5 years to make your argument.

Written:

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

2. A client of Mr. Richards wants to purchase a large commercial building. The building

costs $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, copy the three bonds and then change the Rate to +/- .005 or 50 BPS. Setup a

summary 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.

????????? ???????? = ??234567 − ??934567

2 ∗ ??? ∗ 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 and then copy and change values as described for each of

the additional five scenarios. Create a summary table showing the years and rates and the

compound interest earned. Also, create 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 analyses 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.

5

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

monthly income by multiplying the rate by the account balance for each different rate.

Create a summary table to 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?