A bond pays a fixed income periodically, and a terminal amount upon completion.
I can calculate its true rate of return numerically (eg using goal seek on a spreadsheet), but is there a formula for this (taking into account timings of payments)?
No. Well, er, yes, but none that can be solved algebraically.
Essentially the formula is that the sum of the net present values (taking "present" to mean the time the original investment was made, i.e. when the bond was bought) of all the periodic payments and of the terminal payment must add up to the original cost of the bond.
The NPV of each payment is easily computed, it is the cash value of the payment at the time it is made divided by the (N/12)th power of one plus the rate of return, where N is the number of months after the "present" that the payment is made. For example, if the rate of return were 6%, then £100 paid 6 months after present would be worth £100/(1.06^(6/12)) which is £97.13.
Since your problem is that you're trying to work out the 1.06 from all the other data, the main formula will involve a sum of different powers of the quantity you'd like to solve it for. That is in general impossibly hard to solve for the quantity.
But it's easy to work backwards. Just guess a value for the rate of return, and work out the sum, and see whether it works out too big or too small compared to the original cost. Then adjust your guess up or down as required, and keep trying until the sum is spot on (or near enough). That, in a nutshell, is what "goal seek" actually does behind the scenes.
If there were a simple formula, you wouldn't need to use goal seek.
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.