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

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

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

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

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

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

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

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