IRD Determination G3: Yield to Maturity Method for Bonds (worked example)

[Snoopy]

PM me an email address and I will send this Excel Spreadsheet:

Thanks for the offer Snow Leopard, but those 3 screen shots you gave allowed me to create the spreadsheet myself. Breaking down the spreadsheet into an underlying form, what we have here is a series of four sequential equations covering the four sequential investment time periods. The job of the spreadsheet is to select an unknown 'yield to maturity' figure to make the whole sequence of equations sum to zero. The four sequential equations from the spreadsheet being:

$989,683.38 (1 + Y/2) - $1,070,000 = 0
$980,141.15 (1 + Y/2) - $70,000 = $989,683.38
$971,315.18 (1 + Y/2) - $70,000 = $980,141.15
$1,012,500.00 (1 + 64Y/365) -$70,000 = $971,315.18

It strikes me that those intermediate principal figures always connect one investment period with a subsequent one. Their only purpose is a connective function . That means you don't need to know the exact figures they are. And the above equations can equally well be represented as below:

$A (1 + Y/2) - $1,070k = 0
$B (1 + Y/2) - $70k = $A
$C (1 + Y/2) - $70k = $B
$1,012.5k (1 + 64Y/365) -$70k = $C

Looking at the $A amount in the first equation, we can eliminate $A by substituting from the second equation.
($B (1 + Y/2) - $70k) (1 + Y/2) - $1,070k = 0

Likewise we can eliminate $B by using the third equation
(($C (1 + Y/2) - $70k) (1 + Y/2) - $70k) (1 + Y/2) - $1,070k = 0

Lastly we can eliminate $C by using the fourth equation
[{($1,012.5k (1 + 64Y/365) -$70k) (1 + Y/2) - $70k} (1 + Y/2) - $70k] (1 + Y/2) - $1,070k = 0

The above equation looks complicated. But taking out the dollar values in the calculation and looking at the above equation representing 5 'sequential events' (numbered sequentially 1,2,3,4,5, the fifth being the final repayment of the bond), the equation takes on the simpler form below.
[{(1)2}3](4) - (5) = 0

That might make it easier to see what is going on for some.


Observations

i/ The above 'combined equation' represents four periods of investment 'sequentially cascaded together' over four distinct time periods.
ii/ The return on the first period (innermost bracket (1) ) gets multiplied by the returns in the subsequent period (2), and so on.
iii/ The negative numbers in the above equation represent the money being 'pulled out' by our investor. Because this money is removed, it does not get 'multiplied up' to produce downstream earnings in subsequent periods, which is all as you might expect.
iv/ The 'total of the money put in' is less than the 'total of the money pulled out'. We can explain this by considering the original investment ($1,012.5k) as being multiplied by a multiplier containing a 'constant yield factor Y' (scaled when any constituent time period is shortened of course), with the retained earnings of such an investment subsequently 'multiplied forwards'. This yield factor Y is unknown and must be 'solved' to complete the equation.

Personally I find the above 'combined equation', that I have highlighted in bold, better communicates what is going on in this valuation process than the spreadsheet. But I guess other peoples' mileage may vary?

SNOOPY