Does somebody have a formula to calculate the exact real return on a bond.
With time to go, not just in years, but with exact dates, and including
purchasing-costs,...
Thanks in advance.
Gilbert

"Gilbert DE CEULAER" wrote
Does somebody have a formula to calculate the exact real return on a
bond. With time to go, not just in years, but with exact dates, and
including purchasing-costs,...

Below is a csv file that sort of addresses this question. It uses Excel
"Goal Seek," not a formula. Basically, it calculates an amortization
schedule. The main idea is that the "yield" ("real return" in your
terminology?) is the rate that makes the total amortization equal the
premium paid at the time of purchase. After doing the indicated Goal Seek
(see cell A10), the yield is in B9.

But first enter these values.
B1 = face value of the bond
B2 = maturity date (format as a date)
B3 = purchase date (format as a date)
B4 = accrued interest paid at purchase.
B5 = Basis at purchase = principal + commission + fees
B12 downward = dates of interest payments (format as a date)
C12 downward = corresponding interest amounts
D13:E13 downward = extend or shorten vertically as needed.
(Note that row 12 is a little different from the later rows)

This approach probably does not conform with any official accounting
standard. It's just an exercise; there's no claim of "correctness."

Actual bonds don't have a strange payment schedule like the example below.
Since the point of the original question (apparently) was to deal with a
completely general payment schedule, the example shown is strange on
purpose.

--------------------- cut here ------------------
Face value,25000,,,
Maturity date,39736,,,
Purchase date,38391,,,
Accrued interest @ purch,447.08,,,
Basis at Purchase (incl. purch. cost),26291,,,
Premium,=B5-B1,,,
Total amortization,=SUM(D$11:D$31),,,
Difference,=B7-B6,,,
Calculated Yield,0,,,
Use Goal Seek to set B8=0 by changing B9,,,,
,Date,Pmt,Amortiz'n,Net basis
,38457,693.75,=C12-B$4-B$9*B$5*(B12-B$3)/365.25,=B$5-D12
,38647,703.75,=C13-B$9*E12*(B13-B12)/365.25,=E12-D13
,38808,683.75,=C14-B$9*E13*(B14-B13)/365.25,=E13-D14
,39005,703.75,=C15-B$9*E14*(B15-B14)/365.25,=E14-D15
,39176,683.75,=C16-B$9*E15*(B16-B15)/365.25,=E15-D16
,39370,703.75,=C17-B$9*E16*(B17-B16)/365.25,=E16-D17
,39568,683.75,=C18-B$9*E17*(B18-B17)/365.25,=E17-D18
,39736,693.75,=C19-B$9*E18*(B19-B18)/365.25,=E18-D19