Question

You will be using Excel as a tool to assist in solving this accounting problem. Students completing this assignment will demonstrate the ability to: Use arithmetic operators (addition, subtraction, multiplication, and division) in spreadsheets. Enter simple formulas into spreadsheets to calculate values Copy formulas from one cell to another. Use the sum function. Format cells using decimal points, currency, etc. Right Click; Click Format cells; Click Number; Click Currency or Date Use underline, bold, spacing (left, center, right) functions. Create appropriate headers and footers including date ALWAYS include a header with your name, assignment, and date Create borders-Right click; Click Format; Click Border Print selected problem area. Print landscape versus portrait You are to independently prepare an amortization schedule for the bond problem (below) using Excel. You MUST use formulas where possible. There is very little data that should be 'hard-keyed' into the spreadsheet. I Prepare a separate "data" section at the top of your worksheet Read the problem. Identify and list the following data relevant to preparing an amortization schedule: Face amount Discount or premium Carrying value (Price of bonds) Term (years) Number of Periods Stated rate of interest (annual %) Stated rate per period Market rate of interest (annual %) Market rate per period The ABC Company issued $800,000 face value, 69%, 10 year bonds on January 1, 2015, with bond interest payments each June 30 and December 31. The bonds sold for $691,287 when the market rate of interest was 8%. Prepare a bond amortization schedule using the effective interest methed to amortize the bond discount or premium. II To enter the data into the amortization schedule, copy the data from the "data" section. Enter formulas to compute: Cash interest paid Interest expense Discount amortization Unamortized discount Carrying Value Include headers in the amortization schedule to label each column When completing the problem enter formalas into all cells requiring you to perform mathematical functions. DO NOT

Answer 1

Amortization table
Date	Interest Payment($800,000*3%)	Interest expenses(Bond carrying amount*4%)	Discount amorrtization	Unamortized discount	Bond carrying amount
Col I	Col II	Col III	Col IV(Col III - Col II)	Col V(Col VI - Col IV)	Col VI
01-Jan-15				1,08,713	6,91,287
30-Jun-15	24,000	27,651	3,651	1,05,062	6,94,938
31-Dec-15	24,000	27,798	3,798	1,01,264	6,98,736
30-Jun-16	24,000	27,949	3,949	97,315	7,02,685
31-Dec-16	24,000	28,107	4,107	93,207	7,06,793
30-Jun-17	24,000	28,272	4,272	88,935	7,11,065
31-Dec-17	24,000	28,443	4,443	84,493	7,15,507
30-Jun-18	24,000	28,620	4,620	79,873	7,20,127
31-Dec-18	24,000	28,805	4,805	75,067	7,24,933
30-Jun-19	24,000	28,997	4,997	70,070	7,29,930
31-Dec-19	24,000	29,197	5,197	64,873	7,35,127
30-Jun-20	24,000	29,405	5,405	59,468	7,40,532
31-Dec-20	24,000	29,621	5,621	53,847	7,46,153
30-Jun-21	24,000	29,846	5,846	48,000	7,52,000
31-Dec-21	24,000	30,080	6,080	41,920	7,58,080
30-Jun-22	24,000	30,323	6,323	35,597	7,64,403
31-Dec-22	24,000	30,576	6,576	29,021	7,70,979
30-Jun-23	24,000	30,839	6,839	22,182	7,77,818
31-Dec-23	24,000	31,113	7,113	15,069	7,84,931
30-Jun-24	24,000	31,397	7,397	7,672	7,92,328
31-Dec-24	24,000	31,693	7,672	0	8,00,000