Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
yield function
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current Yield vs. Yield to Maturity at low prices | Excel Worksheet Functions | |||
Problem with Yield() function | Excel Worksheet Functions | |||
what is PAR in the Yield Function | Excel Worksheet Functions | |||
In Excel, can a function yield a drop-down list? | Excel Worksheet Functions | |||
yield function | Excel Worksheet Functions |