Question

Unfortunately this is the third time I'm reposting this because of an insufficient answer. I need specific excel formulas for each answer. For example: PV(.085,5000,8,1000,0) or specific cell reference: =b4*b2 per each cell answer requirement.

So specific formulas for each cell that requires an answer as well as the answer. So far I've only received formulas for the "Bond price Quotation" cells.  Thank you....

Excel File Edit View Insert Format Tools Data Window Hel 书26% Tue 11:15 PM Q :E 385excel project.xlsx fou A 100% ▼ i search in Sheet A Home Layout Tables Charts SmartArt Formulas Data Review Edit FillVerdana 10AA Wrap Text ▼ General Insert Delete Format Themes Aa B20 Here is information about five bonds. In the spaces provide, for each bond, compute: 1) the amount of the stated coupon payment cash fow; 2) the amoum of the bond proceeds that would be received upon the issuance of each bond; and, 3) the resulting bond price quotation. Bond Φ Bond e Bond Bond Bond 4 Face Value s Stated Coupon Interest Rate 6 Market Yicld Rate 7 Term in Years 21,750,000 s 18,000,000 27,500,000 s 30,000,000 7.50% 7.65% 25 6.25% 6.34% 15 10,000 0.00% 5.80% 8.30% 9.20% 8.95% 20 Interest Paid NA. 10 Stated Coupon Payment 12 Bond Proceeds at Issuance 13 14 Bond Price Quotation 15 16 17 Points Earned (2 points per box) 18 19 20 21 23 24 25 26 27 CoverPro-Forma Statements Computations Car Loan Bond Valuation 珇困 Normal View Sum 0 15

Answer 1

A	B	C	D	E	F	G	H	I	J	K
2
3		Bond proceed at issuance will be the present value of future payments.
4		Bond Price will be the proceed at the issuance as % of Face Value.
6
7			Bond (1)	Bond (2)	Bond (3)	Bond (4)	Bond (5)
8		Face Value	$21,750,000	$18,000,000	$27,500,000	$30,000,000	$10,000
9		Stated Coupon rate	7.50%	6.25%	8.30%	9.20%	0.00%
10		Market Yield Rate	7.65%	3.34%	8.25%	8.95%	5.80%
11		Terms in Years	25	25	5	20	10
12		Interest Paid	Semi-annually	Annually	Quarterly	Semi-annually	N/A
13		Compounding factor	2	1	4	2
14
15		Stated coupon payment	$815,625.00	$1,125,000.00	$570,625.00	$1,380,000.00	0	<-- 0
16
17		Bond Proceed at Issuance	$21,388,811	$26,784,882	$27,555,871	$30,692,529	$5,690	<-- =H8*(1/((1+H10)^H11))
18
19		Bond Price quotation	98.34%	148.80%	100.20%	102.31%	56.90%	<-- =H17/H8
20

Formula sheet

A	B	C	D	E	F	G	H	I	J	K
2
3		Bond proceed at issuance will be the present value of future payments.
4		Bond Price will be the proceed at the issuance as % of Face Value.
6
7			Bond (1)	Bond (2)	Bond (3)	Bond (4)	Bond (5)
8		Face Value	21750000	18000000	27500000	30000000	10000
9		Stated Coupon rate	0.075	0.0625	0.083	0.092	0
10		Market Yield Rate	0.0765	0.0334	0.0825	0.0895	0.058
11		Terms in Years	25	25	5	20	10
12		Interest Paid	Semi-annually	Annually	Quarterly	Semi-annually	N/A
13		Compounding factor	2	1	4	2
14
15		Stated coupon payment	=D8*D9/D13	=E8*E9/E13	=F8*F9/F13	=G8*G9/G13	0	=getformula(H15)
16
17		Bond Proceed at Issuance	=-PV(D10/D13,D11*D13,D15,D8)	=-PV(E10/E13,E11*E13,E15,E8)	=-PV(F10/F13,F11*F13,F15,F8)	=-PV(G10/G13,G11*G13,G15,G8)	=H8*(1/((1+H10)^H11))	=getformula(H17)
18
19		Bond Price quotation	=D17/D8	=E17/E8	=F17/F8	=G17/G8	=H17/H8	=getformula(H19)
20