yield to maturity

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.