Question

A bond is purchased for $5500. It is kept for 4 years, and interest is received at the end of each year. Immediately following the owner’s receipt of the fourth interest payment, the owner sells the bond at $200 less of its par value.The bond rate of interest is 6 percent, and the owner’s money yields a 10 percent interest rate. a) Determine the bond’s face value. b) Plot the cash flow diagram for the investment. Please solve it in Excel and not in Mathematical equations. Also mention the formula and steps.

Answer 1

We need to find the face value of the bond given the coupon, buying price, expected sales price and expected return.

Coupon = 6%

Buying Price = $5,500

Sales Price = Par Value - $200

Yield = 10%

A cash flow diagram would be s given below:

We need to solve this problem using goal seek and assuming a par value of the bond.

Let us assume the par value of bond is $6000.

Par Value = $6,000 ..... Assumption

Coupon = 6% *Par Value = $360

Sales Price = Par Value - $200 = $5,800

Using these let us plot the cash flows in excel as given below and calculate the yield using formula given below:

Now, using goal seek we need to adjust the Par Value in such a way that the Yield becomes 10%. This can be done as follows:

Step1: Go to Data Tab >> What-if Analysis >> Goal Seek

Step2: Set Cell: C11 (Yield) >> To Value = 10% >> By Changing Cell: C2 (Par Value) >> Click OK

Using this Goal Seek, would change the cell C2 (Par Value) to $6,445 which is the face value of the bond .

Hence, Face Value = $6,445

The Final Cash Flow Diagram is also given below: