Question

Problem 1:	You are considering investing in a 10-year bond issued by NewEnergy Inc. This bond has $1000 face value, 4% coupon rate.
The bond pays coupons semi-annually and is currently selling at $920. The bond can be called at a $1,040 in 3 years.

1.c. (6 points)	What is the yield to maturity if you purchase the bond at the current price of $920? (Use RATE function)
N=
FV=
PMT=
PV=
PMT Type=
Periodic Discount Rate=
Yield to Maturity =

Answer 1

N = no of periods, FV = Future value of the bond, PMT = periodic payment, PV = Present Value, PMT Type = Type of payment semi annual end of period, Periodic discount rate = Discount rate for frequency of coupons paid (if it is not annual), YTM = annual discount rate

In the example, the bond matures in 10 years but since the payment is made semi-annually we will take N = 2*10 = 20.

Future value is the value of the bond when it matures and it is always equal to the face value unless specified.

The periodic payment = (annual coupon rate x face value)/frequency in a year.

The present value is already given as $920

Periodic discount = YTM for semi annual coupon payment

N = 20

FV = $1000

PMT = (4% x 1000)/2 = $40/2 = $20

PV = -$920 (PV is negative as it indicates cash outflow for purchasing the bond)

PMT Type = Semi annual end of period

The rate formula when used in excel 2016 gives the following arguments for input to give you YTM  

= RATE (nper, pmt, pv, [fv], [type], [guess])

Here nper = N, pmt = PMT, pv = PV (ensure to input a negative number as mentioned above), fv = FV, [type] = 0 (0=end of period and 1=beginning of period)

Since there is no mention we take by default as end of period.

So as per rate function our formula would be (use numbers directly or provide cell reference)

= RATE(20,20,-920,1000,0)

This will give output as 2.51% as the answer

This answer is the periodic discount rate as we have taken semi annual coupon calculations and hence the rate received is the semi-annual rate

YTM = frequency of coupon * periodic discount rate

= 2 x 2.514% = 5.028%