yield function
 

I wish to calculate yields using monthly compounding. Excel spreadsheet has
a 'yield' function that allows 1, 2, or 4 compound periods per year (I need
12). I have a custom function written by a friend that calculates yield,
but it too, does not calculate monthly compounding. In fact, it is only
accurate semi-annually and is inaccurate outside of that.

My question is: can I get monthly compounding for the 'yield' or 'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank you,
Mike Allen

yield function
 

I have not used the Yield Function in Excel, but with regular Time Value of
Money Calculations you can just increase the number of periods as years * 12
(months) and decrease the interest rate by Interest / 12 (months). This
should change to 12 compounding periods per year.

HTH

"mike allen" wrote:

I wish to calculate yields using monthly compounding. Excel spreadsheet has
a 'yield' function that allows 1, 2, or 4 compound periods per year (I need
12). I have a custom function written by a friend that calculates yield,
but it too, does not calculate monthly compounding. In fact, it is only
accurate semi-annually and is inaccurate outside of that.

My question is: can I get monthly compounding for the 'yield' or 'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank you,
Mike Allen

yield function
 

Have you tried using 12?

--
Don Guillett
SalesAid Software

"mike allen" wrote in message
...
I wish to calculate yields using monthly compounding. Excel spreadsheet
has
a 'yield' function that allows 1, 2, or 4 compound periods per year (I
need
12). I have a custom function written by a friend that calculates yield,
but it too, does not calculate monthly compounding. In fact, it is only
accurate semi-annually and is inaccurate outside of that.

My question is: can I get monthly compounding for the 'yield' or 'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will
calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank you,
Mike Allen

yield function
 

Or try using RATE where

pv = current price of bond

fv = 100 (maturity value)

pmt = coupon payment.



--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Have you tried using 12?

--
Don Guillett
SalesAid Software

"mike allen" wrote in message
...
I wish to calculate yields using monthly compounding. Excel spreadsheet
has
a 'yield' function that allows 1, 2, or 4 compound periods per year (I
need
12). I have a custom function written by a friend that calculates
yield,
but it too, does not calculate monthly compounding. In fact, it is only
accurate semi-annually and is inaccurate outside of that.

My question is: can I get monthly compounding for the 'yield' or
'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will
calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank
you,
Mike Allen

yield function
 

12 results in "#NUM!" (only 1,2,4 work).
but, i did get 'RATE' to work. do you know if it is usable in code ('YIELD'
and 'PRICE' are not)? i'll look into it. thanks, mike allen

"Don Guillett" wrote in message
...
Or try using RATE where

pv = current price of bond

fv = 100 (maturity value)

pmt = coupon payment.



--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Have you tried using 12?

--
Don Guillett
SalesAid Software

"mike allen" wrote in message
...
I wish to calculate yields using monthly compounding. Excel
spreadsheet
has
a 'yield' function that allows 1, 2, or 4 compound periods per year (I
need
12). I have a custom function written by a friend that calculates
yield,
but it too, does not calculate monthly compounding. In fact, it is
only
accurate semi-annually and is inaccurate outside of that.

My question is: can I get monthly compounding for the 'yield' or
'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will
calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank
you,
Mike Allen

yield function
 

I can use 'RATE' on simple examples (even in code! unlike 'yield'), but i'm
not sure about issues such as accrued interest due to non-even dates:
12/23/04 settlement, 4/3/09 maturity, 7%coupon, monthly payments...
any thoughts? thanks, mike allen

"Don Guillett" wrote in message
...
Or try using RATE where

pv = current price of bond

fv = 100 (maturity value)

pmt = coupon payment.



--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Have you tried using 12?

--
Don Guillett
SalesAid Software

"mike allen" wrote in message
...
I wish to calculate yields using monthly compounding. Excel
spreadsheet
has
a 'yield' function that allows 1, 2, or 4 compound periods per year (I
need
12). I have a custom function written by a friend that calculates
yield,
but it too, does not calculate monthly compounding. In fact, it is
only
accurate semi-annually and is inaccurate outside of that.

My question is: can I get monthly compounding for the 'yield' or
'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will
calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank
you,
Mike Allen

yield function
 

To use PRICE and YIELD in VBA code you must have the ATP installed and in the
VBE, set a reference to it at Tools/References.

On Thu, 23 Dec 2004 14:37:40 -0600, "mike allen"
wrote:

12 results in "#NUM!" (only 1,2,4 work).
but, i did get 'RATE' to work. do you know if it is usable in code ('YIELD'
and 'PRICE' are not)? i'll look into it. thanks, mike allen

"Don Guillett" wrote in message
...
Or try using RATE where

pv = current price of bond

fv = 100 (maturity value)

pmt = coupon payment.



--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Have you tried using 12?

--
Don Guillett
SalesAid Software

"mike allen" wrote in message
...
I wish to calculate yields using monthly compounding. Excel
spreadsheet
has
a 'yield' function that allows 1, 2, or 4 compound periods per year (I
need
12). I have a custom function written by a friend that calculates
yield,
but it too, does not calculate monthly compounding. In fact, it is
only
accurate semi-annually and is inaccurate outside of that.

My question is: can I get monthly compounding for the 'yield' or
'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will
calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank
you,
Mike Allen

yield function
 

great. i'm getting closer to my goal. i thought all this time i could not
use 'yield' and 'price' in code. i still need to figure 12 compound periods
per year on bonds in code. 'rate' function somewhat accomplishes this, but
not on bonds w/ accrued interest (i don't think).
Can I get the actual code for YIELD and PRICE? if so, maybe i can alter it
to accept compounding periods other than 1, 2, and 4. I don't guess there
is a setting, add-in, preference, etc. i can set to allow 12 compounds/yr?
thank you very much, mike allen

"Myrna Larson" wrote in message
...
To use PRICE and YIELD in VBA code you must have the ATP installed and in
the
VBE, set a reference to it at Tools/References.

On Thu, 23 Dec 2004 14:37:40 -0600, "mike allen"
wrote:

12 results in "#NUM!" (only 1,2,4 work).
but, i did get 'RATE' to work. do you know if it is usable in code
('YIELD'
and 'PRICE' are not)? i'll look into it. thanks, mike allen

"Don Guillett" wrote in message
...
Or try using RATE where

pv = current price of bond

fv = 100 (maturity value)

pmt = coupon payment.



--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Have you tried using 12?

--
Don Guillett
SalesAid Software

"mike allen" wrote in message
...
I wish to calculate yields using monthly compounding. Excel
spreadsheet
has
a 'yield' function that allows 1, 2, or 4 compound periods per year
(I
need
12). I have a custom function written by a friend that calculates
yield,
but it too, does not calculate monthly compounding. In fact, it is
only
accurate semi-annually and is inaccurate outside of that.

My question is: can I get monthly compounding for the 'yield' or
'price'
function on excel (not necessarily in code), like an add-in?
Or better yet, is there code available (preferably free) that will
calculate
yield and price w/ more than 1, 2, and 4 compounding periods? Thank
you,
Mike Allen

yield function
 

PS: You can't get at MS's code from the ATP, but you mention code from a
friend. Can you modify that? Perhaps you can find and fix the bug while you're
at it.

OTOH, here's some information from a David Garlock's "Federal Income Taxation
of Debt Instruments" on yield calculations. It requires listing the amounts on
a worksheet. I have paraphrased his example below:

The situation is a $1 million bond with 5 annual payments of 100,000, 300,000,
500,000, 500,000 and 300,000. There is an initial one-month delay in the
receipt of the payments, so they will be received at the end of months 13, 25,
37, 49, and 61. The solution Garlock gives is this:

In cell D1, put an initial guess for the MONTHLY yield to maturity, (say
1.5%). Put the 5 positive cash flows in cells D3:D7. In E3:E7, put a formula
for the present value of the amount in the adjacent cell, discounting at the
rate in D1 for the appropriate number of months. For example, the first
formula would be =-PV(D$1,13,0,D3).

[In the second formula, replace the 13 with 25, etc. With the layout as
suggested, you could change the formula to
=-PV(D$1,(ROW()-2)*12+1,0,D3)
and just copy it down through E7.]

In cell E8, the formula is =SUM(E3:E7). This will give the value 939,425. Now
use Goal Seek, setting cell E8 to the value 1,000,000 by changing cell D1. The
result -- the monthly yield -- is 1.3347289%. Convert that to annual yield by
multiplying by 12.

Whether this information will help with a VBA solution depends on whether you
can use Goal Seek with VBA arrays rather than worksheet ranges. AFAIK, you
can't.

I also tried putting the amounts and dates of the original purchase and coupon
payments in adjacent columns, then used the XIRR function. That gave me 17.26%
(vs 16.03% with Garlock's method).

Maybe one of the financial gurus can explain the reason for the difference.

BTW, I have MS's document providing not the actual code (which would probably
be written in C, not VBA), but the formulas which are solved. For the rate
functions, they show a formula involving rate, number of periods, payment, PV,
FV and 'type' which, if the values for all arguments are correct, will
evaluate to 0.

If one of these variables is unknown and the others known, this formula can be
used with iteration to solve for the unknown parameter: they start with a
guess for that value, and keep changing it until the result of the formula is
0.

yield function
 

I compared the speed of the VBA routine with the ATP YIELD function. The VBA
code is 2.33 times FASTER.

But, as I said yesterday, it does have the limitation of fewer options for the
Basis argument. Looking at the formulas in Help, this seems to be relevant in
the calculation of the number of days from settlement to first coupon (the
value DSC).