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.