代写Portfolio Selection Problem调试数据库编程

The bank places the following policy limitations on the portfolio manager’s actions:

· Government and agency bonds must total at least $4 million.

· No more than $8 million can be invested in any one bond.

· The average quality of the portfolio cannot exceed 1.4 on the bank’s quality scale (on this scale, a lower number means a higher-quality bond).

· The average number of years to maturity of the portfolio cannot exceed 5 years.

For a portfolio, portfolio-risk rating and portfolio maturity are defined as a weighted average, where the weight assigned to each bond type is proportional to the dollar investment in that bond. Assume you can invest any dollar amount in any of the bonds.

a) Assume that the objective of the portfolio manager is to maximize after-tax proceeds, e.g., a 3.0% after-tax yield on a $4 million investment translates into a $0.12 million in proceeds. Formulate a linear decision model in Excel and solve it. Describe the optimal solution, which bonds are bought, in what quantities, etc.

b) Assume it becomes possible to borrow up to $1 million at 4.5% interest (before taxes). Reformulate the problem and resolve.  Describe how the optimal solution has changed. (Assume the tax rate is 50%, so that the after-tax cost of borrowing $1 million at say 5% would be $50,000-$25,000=$25,000.) (Hint: add a new decision variable representing the amount borrowed.)

(Hint: It is often desirable to use a linear model, since the Solver is guaranteed to find the optimal solution for such a model.  The maximum allowed average quality constraint and the maximum allowed average years to maturity constraint are non-linear, but they can be transformed to linear constraints. This is done as follows: say you want to write a constraint like x+y/2x+5y ≤ 3 in linear form.  Then you simply transform. it to this: 2x+5y £ 3x+3y.  The constraint is now linear.)