Question

Excel Online Structured Activity: Bond valuation You are considering a 25-year, $1,000 par value bond. Its coupon rate is 10%, and interest is paid semiannually. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below. Open spreadsheet If you require an "effective" annual interest rate (not a nominal rate) of 10.01%, how much should you be willing to pay for the bond? Do not round intermediate steps. Round your answer to the nearest cent.

Answer 1

Years to maturity	25
Par value of bond	1000
Coupon rate	10%
Frequency interest	2
Effective annual rate	10.01%
		Formula
Nominal annual rate	9.77%	((EAR+1)^(1/m)-1)*m
Periodic rate	0.048857	Nominal rate/Frequency
Bond Price
Number of periods	50	Years of maturity*Frequency
Interest rate per period	0.048857
Coupon	50	Coupon rate*Par value
Par value	1000
Price of bond	$1,021.25

Workings

AutoSave On H D . Book1 - Excel Sign in - 0 X Comments File Help Tell me what you want to do Share A OX Insert. Delete - Format - 7 Paste Le Home Insert Draw Page Layout Formulas Data Review View Calibri - 11 AA === . le General BIŲ - EA E 3 - $ -% Font Alignment Number - X ft Years to maturity s I Conditional Format as Formatting Table Styles Cell Styles Sort & Filter Find & Select Clipboard Cells Editing S2 W Years to maturity Par value of bond Coupon rate Frequency interest Effective annual rate 25 1000 0.1 0.1001 Formula =((T6+1)^(1/T5)-1)*T5 ((EAR+1)^(1/m)-1)*m =T8/2 Nominal rate/Frequency Nominal annual rate Periodic rate Bond Price Number of periods =T5*T2 Years of maturity* Frequency Interest rate per period =T9 Coupon =T4*T3/T5 Coupon rate*Par value Par value 1000 Price of bond =-PV(T13,T12,T14,T15)