Question
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.
Solution Preview
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.
1 a. Proceeds from bonds = PV of principal + PV of interest Face Value of Bonds 1.800.000Proceeds 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...