Please complete the spreadsheet to find the selling price of

Please complete the spreadsheet to find the selling price of the bonds, prepare the amortization schedule, compute the sum of payment, interest, and principal columns, and compute financial statement amounts in cells J9, J13, J14, and J18

Assignment 1-Spring 2018 (1)xls [Compatibility Mode]- Excel . A FILE HOME INSERT PAGE LAYOUTFORMULAS DATA REVIEW VIEW Michael Kneale S Cut Wrap Text Conditional Format as Cell Insert Delete Format Formatting , Table, Styles···· Fill Clear Paste Sort &Find;& ge & Center . $ . % , Format Painter Filter Select Font Alignment Editing B1 Name 1 Name 3Due-January 25,2018 23145 0.0 Bond Price Calculator Financial Statement Amounts 7389 $100,000 Income Statement 7Face Value of Bonds 8Bond Term (Years) 9 Number or Coupon Payments (Per Year) 10 Ann 11 Annual Effective Interest Rate 12 Amount of Coupon Payments 13 Selling Price of Bonds 10 For Year Ended 12/3117 2 Interest Expense al Coupon Interest Rate 4.000% 4.800% Balance Sheet 5 2,000 December 31, 2017 Interest Payable 14 Amortization Schedule 16 17 Date Payment Interest BalanceBalance For Year Ended 12/31/17 18 1/1/2016 19 20 11/2017 21 7/1/2017 Change in Loan Cash Flow Statement Cash paid for interest 7/112016 1/1/2018 237/1/2018 24 1/1/2019 25 7/112019 27 7/112020 28 1/1/2021 29 7/1/2021 1/1/2022 SheetSht2 Sheet3 +89% O Type here to search 2-11 PM 1/24/2018 Ul 2

Solution

Income statement interest expense= 2248.92

Balance sheet

Interest payable= 2000

Long term liability= 93953.95

Cash flow statement, cash paid for interest = 2000

	Particulars	Cash flow	Discount factor	Discounted cash flow
	Interest payments-Annuity (2.4%,20 periods)	2,000.0	15.7374	31,474.87
	Principle payments -Present value (2.4%,20 periods)	1,00,000	0.6223	62,230.15
A	Bond price			93,705.03
	Face value			1,00,000
	Premium/(Discount)			-6,294.97
	Interest amount:
	Face value	1,00,000
	Coupon/stated Rate of interest	4.00%
	Frequency of payment(once in)	6 months
B	Interest amount	100000*0.04*6/12=		2000
	Present value calculation:
	yield to maturity/Effective rate	4.80%
	Effective interest per period(i)	0.048*6/12=		2.40%
	Number of periods:
Ref	Particulars	Amount
a	Number of interest payments in a year	2
b	Years to maturiy	10
c=a*b	Number of periods	20