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?