Bond Calculations

The Issue
Your company is considering the issuance of bonds in the amount of $1,800,000. The bonds mature in fifteen years and have an annual coupon rate of interest of 8%, paid semi-annually. Market interest rates have been fluctuating in recent weeks, and the treasurer of your company would like to know the amount of money that can be expected from the bond issuance.

The Assignment
Using Excel, illustrate the following clearly labeling your work. To receive full credit for this assignment, you must you the appropriate formulas in Excel.   Use formulas wherever possible.

1. Determine the amount of proceeds that will be received when the bonds are issued, assuming market interest rates for similar bonds are:
a. 9%
b. 7%
Hint: Bonds are present value problems.

2. Use Excel to prepare an amortization schedule for these bonds under both scenarios using the effective interest rate.

3. Assume that the bond sells for $1,700,000. Using Excel, calculate the annual effective interest rate for the bond.   Round to four decimal points.

1 a. Proceeds from bonds = PV of principal + PV of interest Face Value of Bonds 1.800.000
Proceeds from bonds = PV of $1,800,000 + PV of interest X Coupon Rate 8%
Proceeds from bonds = $480.600,03 + $1.172.799,98 Annual Interest 144.000
Proceeds from bonds = $1.653.400,00 Divide by # of yrly pymts 2
Semi Annual Interest Amount 72.000 <<This is the cash paid in interest at each semi annual date
The present value of the principal is based on a single payment...

This is only a preview of the solution. Please use the purchase button to see the entire solution