Question

Face amount
Interest payment			Blue cells can be typed in, yellow cells must be formulas or functions.
Market interest rate per period
Number of periods
Issue price			All cells in the table should be formulas or functions

Date	Cash Paid                for Interest	Interest Expense	Increase in Carrying Value	Carrying Value           (End of Period)
1/1/17	-------	-------	-------
6/30/17
(18 more logs below)

Problem Data:

Bond Discount Problem: On January 1, 2017, Waterfall Lodge & Amusement Park issued $6,000,000 of 6% bonds, due in 10 years. The market interest rate for bonds of similar risk and maturity is 7%. Interest is paid semiannually on June 30 and December 31 each year.

Bond Premium Problem: Assume that the market interest rate is 5.5% instead of 7%. Assume all other given amounts are the same as stated in the bond discount problem.

Required:

1.   Enter the given problem data in the Bond Discount worksheet. Then use the Present Value function in Excel to calculate the issue price of the bonds.

2.   Use cell references and Excel formulas to construct an amortization schedule for the entire life of the bonds. Note everything below the data should be a cell reference or formula containing a cell reference. (example “=B4+B5” is ok, =7+4 is not)

(2) (3) (5) (4) Increase in Carrying Value Date Cash Paid Interest Expense Carrying Value x Market Rate Face Amount X Stated Rate (3)-(2) Carrying Value Prior Carrying Value + (4) $ 93,205 93,433 93,670 1/1/18 6/30/18 12/31/18 $3,500 3,500 $3,728 3,737 $228 237 6/30/27 12/31/27 3,500 3,500 3,962 3,981 462 481 99,057 99,519 100,000

Answer 1

Note:Only part 1 can be uploaded as the maximum limit is 65,000 characters
Part 1:When Bond is issued at discount
Table values are based on:
Face Amount	$6,000,000
Interest Payment	$6,000,000*6%*6/12 =$180,000
Market Interest rate per period	3.50%
Cash Flow	Table Value(PV of 3.5% for 20 period)	Amount	Present Value
PV of Interest	14.2124	$1,80,000	$25,58,232
PV of Principal	0.502566	$60,00,000	$30,15,396
PV of Bonds Payable(Issue Price)			$55,73,628
Discount on Bonds =$6,000,000 - $5,573,628 =$426,372
Date	Cash Paid($6,000,000*3%)	Interest expenses(Bond carrying amount*3.5%)	Increase in carrying value	Bond carrying amount
Col I	Col II	Col III	Col IV(Col III - Col II)	Col V
01-Jan-17				$                          55,73,628
30-Jun-17	$                                              1,80,000	$                                                         1,95,077	$                                            15,077	$                          55,88,705
31-Dec-17	$                                              1,80,000	$                                                         1,95,605	$                                            15,605	$                          56,04,310
30-Jun-18	$                                              1,80,000	$                                                         1,96,151	$                                            16,151	$                          56,20,460
31-Dec-18	$                                              1,80,000	$                                                         1,96,716	$                                            16,716	$                          56,37,177
30-Jun-19	$                                              1,80,000	$                                                         1,97,301	$                                            17,301	$                          56,54,478
31-Dec-19	$                                              1,80,000	$                                                         1,97,907	$                                            17,907	$                          56,72,385
30-Jun-20	$                                              1,80,000	$                                                         1,98,533	$                                            18,533	$                          56,90,918
31-Dec-20	$                                              1,80,000	$                                                         1,99,182	$                                            19,182	$                          57,10,100
30-Jun-21	$                                              1,80,000	$                                                         1,99,854	$                                            19,854	$                          57,29,954
31-Dec-21	$                                              1,80,000	$                                                         2,00,548	$                                            20,548	$                          57,50,502
30-Jun-22	$                                              1,80,000	$                                                         2,01,268	$                                            21,268	$                          57,71,770
31-Dec-22	$                                              1,80,000	$                                                         2,02,012	$                                            22,012	$                          57,93,781
30-Jun-23	$                                              1,80,000	$                                                         2,02,782	$                                            22,782	$                          58,16,564
31-Dec-23	$                                              1,80,000	$                                                         2,03,580	$                                            23,580	$                          58,40,144
30-Jun-24	$                                              1,80,000	$                                                         2,04,405	$                                            24,405	$                          58,64,549
31-Dec-24	$                                              1,80,000	$                                                         2,05,259	$                                            25,259	$                          58,89,808
30-Jun-25	$                                              1,80,000	$                                                         2,06,143	$                                            26,143	$                          59,15,951
31-Dec-25	$                                              1,80,000	$                                                         2,07,058	$                                            27,058	$                          59,43,009
30-Jun-26	$                                              1,80,000	$                                                         2,08,005	$                                            28,005	$                          59,71,015
31-Dec-26	$                                              1,80,000	$                                                         2,08,986	$                                            28,986	$                          60,00,000