Question: Excel Assignment
Please note, Excel spreadsheets that are not able to automatically calculate bond prices and cash flows for any change in interest rates, maturity, or cash in-flows will not satisfy the requirements for this assignment! Estimates change in business and Excel files should be set up or linked to automatically adjust to changes.
Bond Pricing: You are to determine the price of each of the bonds at issuance. Prepare a table showing the amount of interest, principle, and total price of each of the bonds below. Again, your spreadsheet should update for any change in face amount, interest rates, maturity, or payment frequency (I will input data into your spreadsheet to test these items.). Below are the bonds:
|
Issuance Date
|
9/30/2016
|
11/15/2016
|
3/15/2016
|
|
Face Amount of Bond
|
$1,000,000
|
$750,000
|
$1,250,000
|
|
Maturity Date
|
9/30/2036
|
11/15/2021
|
6/15/2026
|
|
Stated Rate
|
8%
|
12%
|
12%
|
|
Market Rate
|
10%
|
7%
|
15%
|
|
Payment Frequency
|
Semiannual
|
Annual
|
Quarterly
|
In addition, assume that the company has issued the above bonds. Prepare the journal entries at the date of issuance and the first interest payment date (Hint: The interest payment date will be based upon the payment frequency.). Journal entries should be prepared for each of the bonds. Remember that the numbers on the journal should be linked so that they update too.
Cash Flow Analysis: Using Excel Functions, determine the discounted cash flow of the following purchase of a new plant facility. Given a required rate of return of 8 percent and that the plant costs $2M, determine whether the company should invest in the plant. Make sure to again link all the inputs in excel in case projected costs or inflows change.
|
Year
|
0
|
1
|
2
|
3
|
4
|
5
|
|
Initial Cost & Maintenance
|
($2,000,000)
|
($6,500)
|
($6,500)
|
($10,500)
|
($10,500)
|
($12,000)
|
|
Projected Revenue
|
|
150,000
|
500,000
|
650,000
|
720,000
|
900,000
|
|
Depreciation
|
|
-25,000
|
-51,200
|
-51,200
|
-51,200
|
-51,200
|
|
Total Annual Cash Flow
|
($2,000,000)
|
$118,500
|
$442,300
|
$588,300
|
$658,300
|
$836,800
|
|
Cumulative Cash Flow
|
($2,000,000)
|
($1,881,500)
|
($1,439,200)
|
($850,900)
|
($192,600)
|
$644,200
|