AFM131 Lecture 11: AFM131 Lecture 10: AFM131: Marketing - Managing the Marketing Mix
Get access
Related Documents
Related Questions
Can you please help solve the formula portions and show theformulas used?
PROBLEM DATA
The University Club recently issued $1,500,000 of 10-year, 9%bonds at an effective interest rate of 10%. Bond interest ispayable annually.
REQUIREMENTS
1. You have been asked to calculate the issuance price of thebonds and prepare amortization schedules for any discount orpremium. The worksheet BONDS has been provided to assist you. Notethat the worksheet contains a scratch pad at the bottom that hasbeen preprogrammed to automatically compute and display therelevant cash flows needed for bond pricing.
2. The bond pricing formula will utilize the NPV (Net PresentValue) function on your spreadsheet program. The NPV function willautomatically compute the net present value of annual future cashflows discounted at a specific interest rate. The interest rateshould be expressed as a cell address.
BONDS | |||||
Bond Pricing andAmortization | |||||
Data Section | |||||
Face value of bond | $1,500,000 | ||||
Years to maturity * | 10 | ||||
Stated interest rate | 9.0% | ||||
Effective interest rate | 10.0% | ||||
* Worksheet is designed for use with bonds having amaturity of | |||||
12 years or less and paying interestannually. | |||||
Answer Section | |||||
Bond issue price | FORMULA1 | ||||
Amortization Schedule -Straight Line Method | |||||
Cash | Interest | (Disc.) | Bond | ||
Year | Paid | Amortization | Expense | Premium | Carrying Value |
0 | FORMULA2 | FORMULA3 | |||
1 | FORMULA4 | FORMULA5 | FORMULA6 | FORMULA7 | FORMULA8 |
2 | FORMULA9 | FORMULA10 | FORMULA11 | FORMULA12 | FORMULA13 |
3 | 0 | 0 | 0 | 0 | 0 |
4 | 0 | 0 | 0 | 0 | 0 |
5 | 0 | 0 | 0 | 0 | 0 |
6 | 0 | 0 | 0 | 0 | 0 |
7 | 0 | 0 | 0 | 0 | 0 |
8 | 0 | 0 | 0 | 0 | 0 |
9 | 0 | 0 | 0 | 0 | 0 |
10 | 0 | 0 | 0 | 0 | 0 |
11 | 0 | 0 | 0 | 0 | 0 |
12 | 0 | 0 | 0 | 0 | 0 |
Amortization Schedule -Effective Interest Method | |||||
Interest | Cash | (Disc.) | Bond | ||
Year | Expense | Paid | Amortization | Premium | Carrying Value |
0 | FORMULA14 | FORMULA15 | |||
1 | FORMULA16 | FORMULA17 | FORMULA18 | FORMULA19 | FORMULA20 |
2 | FORMULA21 | FORMULA22 | FORMULA23 | FORMULA24 | FORMULA25 |
3 | 0 | 0 | 0 | 0 | 0 |
4 | 0 | 0 | 0 | 0 | 0 |
5 | 0 | 0 | 0 | 0 | 0 |
6 | 0 | 0 | 0 | 0 | 0 |
7 | 0 | 0 | 0 | 0 | 0 |
8 | 0 | 0 | 0 | 0 | 0 |
9 | 0 | 0 | 0 | 0 | 0 |
10 | 0 | 0 | 0 | 0 | 0 |
11 | 0 | 0 | 0 | 0 | 0 |
12 | 0 | 0 | 0 | 0 | 0 |
Scratch Pad | |||||
Display of relevant cash flows | |||||
Annual | Bond | ||||
Year | Interest | Maturity | |||
1 | 135000 | 0 | |||
2 | 135000 | 0 | |||
3 | 135000 | 0 | |||
4 | 135000 | 0 | |||
5 | 135000 | 0 | |||
6 | 135000 | 0 | |||
7 | 135000 | 0 | |||
8 | 135000 | 0 | |||
9 | 135000 | 0 | |||
10 | 135000 | 1500000 | |||
11 | 0 | 0 | |||
12 | 0 | 0 | |||