# Excel bond price, YTM, portfolio beta solution

Consider the information in the following table:

a. What is the beta of this portfolio?

b. What (specifically) would you do to bring this portfolio back to a

target beta of 1.10?

And also the following

A seven-year bond with an 8 percent coupon rate has a yield to maturity

of 9.15 percent. What is the current bond price?

https://brainmass.com/business/bond-valuation/excel-bond-price-ytm-portfolio-beta-solution-411254

#### Solution Preview

Consider the information in the following table:

a. What is the beta of this portfolio?

To calculate the portfolio beta it is necessary to determine the weight each individual stock or security within the portfolio factors into the total. It is possible to determine the beta for the portfolio by arriving at a weighted average beta for each stock within the portfolio. For the sake of simplicity, let's say 50% of my portfolio assets are in Dell Computer. Dell's Beta is 1.5 (example only). The other 50% is in 3M stock which carries a Beta = to .5. A simple average will tell me that my portfolios Beta is:

(.5*1.5)+(.5*.5) = Portfolio Beta.

(.75) + (.25) = 1.0

Now, in the example, we have many more securities, but the same rule applies. The simplest way to calculate is to plug the values into excel, I will attach but have demonstrated the logic above using a simple example. See the attached spreadsheet for the final result.

b. What (specifically) would you do to bring this portfolio back to a

target beta of 1.10?

In this case, the beta is 1.08. There are a couple of options when managing portfolios using "asset allocation" and there are multiple strategies depending on what the goals are for the investor. Let's examine a couple scenarios that could be used to realign the portfolio to 1.10. You can effect portfolio beta in several ways and also in multiple combinations. The theory is to balance the account according to the beta to acheive the desired level of return over the life of the investment portfolio. Greater risk = Greater Return however through diversification, one is able to mitigate several types of risk that are inherent when you only own one stock. For example, if Dell went out of business, I lose 1/2 my portfolio, but if it's only a 5% weight, I only lose 5% of my total investment. In addition, it can also mitigate economic or business segment risk. I am not truly diversified if I have 100% of my stocks in the technology sector. A basket of securities well matched to the composition of the broader economy is a well ...

#### Solution Summary

Solution has two subjects. One solution addresses calculating the weighted average beta for an investment portfolio The solution also explores calculting the price of a bond given the yeild to maturity by hand/financial calculator and the super easy method, using excel. Solutions are highly instructional and give step by step instructions to complete both problems. In addition, there is an attached excel file to show the mechanics of the calculations via the formulas used which better explains how to replicate the same for use in a different problem along with formula attached on worksheet 3 in addition to the expression within the solution.