Question

D8 X fox 3.7% AB E F G H Union Local School District has bonds outstanding with a coupon rate of 3.7 percent paid semiannually and 16 years to maturity. The yield to maturity on these bonds is 3.9 percent and the bonds have a par value of $5,000. What is the dollar price of the bond? 1/1/2000 1/1/2016 3.70%! Settlement date Maturity date Coupon rate Coupons per year Redemption value (% of par) Yield to maturity Par value 100 3.90% 5,000 Complete the following analysis. Do not hard code values in your calculations. Leave the "Basis" input blank in the function. You must use the built-in Excel function to answer this question. Dollar price

How to use excel function to solve this problem?

Answer 1

In order to find dollar value using excel, we need to use PRICE Function in MS Excel.

Description of this function as per MS Excel: Returns the price per $100 face value of a security that pays periodic interest. (In this example it is semi-annual period.)

Syntax of this function as per MS Excel: =PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

Values as given in the question are:

Argument description	Data
Settlement date	01-01-2000
Maturity date	01-01-2016
Percent semiannual coupon	3.70%
Percent yield	3.90%
Redemption value % of Par	100
Frequency is semiannual	2
Basis	None
Par Value	$5,000

Updated values and the PRICE function value as below:

=PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])

A B Argument description Data Settlement date 01-Jan-00 Maturity date Percent semiannual coupon 01-Jan-16 3.70% Percent yield 3.90% Redemption value 100 Frequency is semiannual 2 Basis 0 Price per $100 PRICE(B2,B3,B4, B5, B6, B7, B8)

Explanation of the function:

Settlement is settlement date of the security. Usually the date the coupon is purchased.
Maturity is maturity date of the security. On this date investor will receive final coupon and the original principal amount.
Rate is nothing but security's coupon rate, expressed annually. It is 3.7% in this question.
Yld is Yield To Maturity - i.e. Current yield on the security expressed annually. Which is 3.9 % in this question.
Redemption is the Redemption value per $100 face value or par value of the security.
Frequency is the coupon payment frequency. This is usually 1,2 or 4. Annual, Semi-Annual or Quarterly. In this problem it will be 2 since it is semi-annual.
Basis is the day count basis. Since we have been asked to keep it blank, value 0 is updated which is the default value when blank updated. The day count for this will be 30/360 US.

Price output = 97.63602 approx 97.64$

This is the sum of present value of all the future cash flows for this security.

Just to reiterate, the value 97.64 $ is the value per $100 par value.

In this question redemption value is 100% of par value which is $5,000.

Price for given par value of $5,000

= ( 97.63602 X 5000 ) / 100

= $ 4881.801