Question

On February 1, 2016, Baker Company issued 9% bonds, dated February 1, with a face amount of $ 80 million. The bonds mature on January 31, 2020 ( 4 years). The market yield for bonds of similar risk and maturity was 10%. Interest is paid semiannually on July 31 and January 31. The Fiscal years of both firms end December 31

Crimley Motor Products

Bond Issue: $80,000,000

Bond Issue years: 4 years

Bond Issue interest rate: 9%

Market Annual Yield: 10%

Using excel functions prepare amortization schedules that the effective interest expense on the bond

Amortization Schedule
	Cash	Effective
	Payment	Interest	Increase in	Outstanding
	4.5%	5%	Balance	Balance
				77,414,714.90
1	3,600,000
2
3
4
5
6
7
8*	-	-	-
*rounded

Using my calculator I can get the below information and the schedule done, but I need to do with the excel functions

i/y=10/2 = 0.05

N=4*2 = 8

pmt = 3,600,000

FV = 80,000,000

PV= 77,414,714.90

Answer 1

present value of Bond issue	Using present value function in ms excel	pv(rate,nper,pmt,fv,type) rate = 10/2 =5% nper =4*2 =8 pmt = 3600000 fv =0 type =0	PV(5%,8,3600000,80000000,0)	($77,414,714.90)
discount on bonds	80000000-77414714.9	2585285.1
Amortization Schedule
Period	Interest paid = face value*coupon rate	Interest expense = carrying value*Market Yield	discount amortized = interest expense-cash paid	balance in discount on bond issue	face value	carrying value = face value-balance in discount on bonds
0				2585285.1	80000000	77414714.9
1	3600000	3870735.75	270735.745	2314549.355	80000000	77685450.65
2	3600000	3884272.53	284272.5323	2030276.823	80000000	77969723.18
3	3600000	3898486.16	298486.1589	1731790.664	80000000	78268209.34
4	3600000	3913410.47	313410.4668	1418380.197	80000000	78581619.8
5	3600000	3929080.99	329080.9901	1089299.207	80000000	78910700.79
6	3600000	3945535.04	345535.0397	743764.1673	80000000	79256235.83
7	3600000	3962811.79	362811.7916	380952.3756	80000000	79619047.62
8	3600000	3980952.38	380952.3812	-0.00557076	80000000	80000000.01