Question

Skills & Knowledge: Calculate the selling price of bonds using Excel and create a bond amortization schedule using Excel Task: Use Excel and the data provided below to: 1. Calculate the price of the bond and 2.Create an amortization schedule Use the amortization schedule format reflected in Chapter 14 of the required textbook. st" 1 in eto tting e il' wad nam asf ond Once you save and name the file correctly email the assignment to me (agaze@fau.edu) by the due date. Data: On January 1, 2018, Raymond Corporation sold 4% bonds having a maturity value of $10,000,000. The market yield for bonds of similar risk and maturity is 4.5%. The bonds are dated January 1, 2018, mature on January 1, 2021, and pay interest on June 30 and December 31 of each year. Criteria for Success: You must incorporate as many formulas as necessary, so that the amortization schedule automatically updates when the market rate of interest is changed. Your Excel spreadsheet should work whether your bond results in a discount or premium. I should be able to manipulate certain data, for example if I change the present value rates or market rate of interest rate, the formulas should still work correctly when determining the present value of the bond. You must use an Excel formula to calculate the price of the bond (i.e. present value)

Create an amortization schedule via excel, using the following data listed above.

Answer 1

	Rate	Semi annual Market Yield=(4.5/2)%	2.25%
	Nper	Number of Aemi annual period (Jan1,18 to Jan1,21)	6
	Pmt	Semi annual Coupon Payment (10 million*4%)/2	$200,000
	FV	Payment at maturity	$10,000,000
	PV	Bond Issue Price	$9,861,138	(Using PV function of excel with Rate=2.25%, Nper=6, Pmt=-200000, Fv=-10000000)
	FV-PV	Bond Discount (10000000-9861138)	$138,862
			AMORTIZATION SCHEDULE
				(4/2)=2% *10 million	Market rate(4.5/2)=2.25% *Previous Book value
			A	B	C	D=C-B	E	F	G=F-E
			Date	Interest payment	Interest Expense	Amortization of Discount	Debit Balance in Bond Discount Account	Credit Balance in Bond Payable Account	Book value of the Bond
			Jan1,2018				$138,862	$10,000,000	$9,861,138
			June30,2018	$200,000	$221,875.61	$21,875.61	$116,986.31	$10,000,000	$9,883,014
			Dec31,2018	$200,000	$222,367.81	$22,367.81	$94,618.51	$10,000,000	$9,905,381
			June30,2019	$200,000	$222,871.08	$22,871.08	$71,747.42	$10,000,000	$9,928,253
			Dec31,2019	$200,000	$223,385.68	$23,385.68	$48,361.74	$10,000,000	$9,951,638
			June30,2020	$200,000	$223,911.86	$23,911.86	$24,449.88	$10,000,000	$9,975,550
			Dec31,2020	$200,000	$224,449.88	$24,449.88	($0.00)	$10,000,000	$10,000,000
										File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do * v fac =PV(E4,55,-€6,-E7) L 4 Rate 5 Nper 6 Pmt 7 FV 8 PV 9 Semi annual Market Yield=(4.5/2)% 2.25% Number of Aemi annual period (Jan1,18 to Jan1,21) Semi annual Coupon Payment (10 million*4%)/2 $200,000 Payment at maturity $10,000,000 Bond Issue Price $9,861,138 (Using PV function of excel with Rate=2.25%, Nper=6, Pmt=-200000, FV=-10000000)