finding a bond coupon rate

[Guest]

is there a function that would give me a bond coupon rate if the following are known.
Face/Par 25,000
paid 25905.46
yield is 9.63%
semiannual coupon payment
maturity 2 years.

 

[steve smallman]

hi,

unsure of exactly what you need, but from the detail
provided, suggest you look at NPV and IRR

Steve

 

[Norman Harker]

Hi eah04!

Try:
=(PMT(9.63%/2,4,-25905.46,25000)/25000)*2
Returns:

I know the price paid is 25905.46 and I know that on maturity I get
25000. I think that the quoted return is 9.63% nominal compounded 6
monthly.

=PMT(9.63%/2,4,-25905.46,25000)
Returns: 1458.00399373537
This is the 6 monthly payment required to secure the yield of 9.63%/2

So divide that by 25000 and I get the 6 monthly yield.
Multiply the answer by 2 and I get the coupon rate expressed as
nominal compounded 6 monthly.

But this approach will only work on the payment dates.

You could work it out for non payment dates but that is more
difficult. I'd be inclined to use an XIRR approach for that and use
GoalSeek to determine the payment required to secure the XIRR
(returned as annual effective) that is equivalent to 9.63% nominal
compounded 6 monthly.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.