yield to maturity
Can anyone help with finding or combining formulas to calculate the "yield to maturity" at purchase date for a security investment that pays principal and interest on a monthly basis?
Par/original face = 10,000,000
Factor = 0.98919653000
Current face = 9,891,965.30
Principal = 9,842,505.47
Purchased accrued interest = 28,714.18
Coupon = 5.75%
price = 99.5
PSA = 222
Weighted Average Life = 4.22 yrs
Other info if needed:
Settlement date = 1/20/07
Next pmt date = 2/15/07 (for accrual period of 1/1/07 - 1/31/07 OR a 14 day delay)
I was going to leave this one alone as I'm not sure I'm the best person to answer this, but here is what I can tell you. I know there are a lot of neat financial functions now available in excel, especially with the analysis toolpak add-in. I'm guessing there is something there that can do what you need. However, I soured on excel "time-value of money" functions a long time ago because they were notoriously inaccurate whenever partial periods were involved. If I had to solve your problem, I would build a full-blown cash flow table and discount each element individually and precisely. I would feed the discounted value functions with a manually input rate of return (yield to maturity). I would then use a simple vba procedure to toggle the input value up or down to achieve a Net Present Value (sum of all discounted cash flows) of zero. This is how any built-in functions work anyway.
Note: That last "toggle" part can be done using the "goalseek" feature if you don't want to use vba.
Last edited by MickeyGreen; 11-06-2007 at 01:29 PM.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1