Yield Function with Monthly Frequency

[jamesglewisf]

We have one stinking spreadsheet that is still in Lotus 1-2-3. It is
used to calculate the yield on our bonds, cmos, etc.

The reason we still use it is that 1-2-3's yield function lets you have
a frequency of 12 or monthly for the coupon payments. Most of our bonds
have monthly coupon payments.

Unfortunately, MS Excel only offers three frequencies. > Frequency is the number of coupon payments per year. For annual

payments, frequency = 1; for semiannual, frequency = 2; for
quarterly, frequency = 4.Any ideas? Has anyone ever seen either a workaround or some company

offering an addin with an improved yield calculation? I would so like
to dump 1-2-3.

 

[jamesglewisf]

Sorry about that. I didn't realize I was posting to a Usenet group i
this forum. My bad

 

[Fred Smith]

Why not just use the Rate function?

The Yield function is just a specialized version of Rate where pv = current
price of bond and fv = 100 (maturity value) and pmt = coupon payment.

Depending on whether you want to use monthly, semi-annual (which bond
traders still cling to) or annual compounding (which the rest of the world
identifies with), you may have to convert your rate using the Effect
function.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

We have one stinking spreadsheet that is still in Lotus 1-2-3. It is
used to calculate the yield on our bonds, cmos, etc.

The reason we still use it is that 1-2-3's yield function lets you have
a frequency of 12 or monthly for the coupon payments. Most of our bonds
have monthly coupon payments.

Unfortunately, MS Excel only offers three frequencies. > Frequency is the

number of coupon payments per year. For annual