Assignment:
Karman Company has two different bonds currently outstanding. Both bonds have a par value of $26,500 and a maturity of 19 years. The required return on both bonds is 10 percent, compounded semiannually.
Bond M makes no payments for the first 7 years, then pays $1,600 every six months over the subsequent 4 years, and finally pays $2,100 every six months over the last 8 years.
Bond N is a zero coupon bond and makes no coupon payments over the life of the bond. The bond's only cash flow is the payment of principal or par value at maturity.
Construct a spreadsheet that solves for the current price of both of these bonds.
The spreadsheet needs to be designed so that any one of the input variables can easily be changed by one cell entry. Further, if any variable entry is changed in value, the resulting price should automatically be calculated by the spreadsheet. For example, there should be a cell to enter the par value and when the numerical entry in that cell is changed a new bond price will automatically calculate and display.
Your spreadsheet must include:
- at least 2 different types of number format
- at least 2 different colors for cell highlighting
- all calculations or computations must be done by and within the spreadsheet
- all formulated cell calculations must use cell references (any input numbers must be first listed or entered in another cell)
- use at least 2 built-in formulas or functions (fx), e.g., =sum(D3:F3), =count(A3:A9), etc.