BOND CALCULATIONS & GRAPHING

[Chuckles123]

Myrna,

Thanks for your reply.

I am a CPA, although I don't practice as one, as well. I am aware o
most, if not all, of the tax basis complexities that you referred to.

I am not looking for a template to calculate tax basis for a specifi
security; I have access to a Bloomberg terminal which does that ver
efficiently. I am looking for a more generic, although accurate
approach that would display amortization of premium and accrual of OI
graphically over time. I realize that some simplifying assumption
would have to be made: e.g., only taxable, or only tax-exempt (m
preference); if only tax-exempts, the yield-to-worst date (based upo
the provided purchase price and purchase date) and associate
redemption price; perhaps excluding zero coupon bonds; perhap
excluding all optional redemptions; definitely excluding step coupo
bonds. I am not looking for any adjustments for historical events -
like an issuer exercising or not exercising its redemption rights; thi
would be only forward-looking.

All bonds would accrue interest on a 30/360 basis and pay interes
semiannually.

Chuckles12

 

[Myrna Larson]

I am aware of most, if not all, of the tax basis complexities that you

referred to.

IMO the tax code is pretty absurd in this area. It's almost enough to deter a
person from buying bonds. No "ordinary mortal" could possibly do it without
help. Some (a lot of?) CPAs don't know how to calculate this correctly. Ours
didn't know anything about the "constant yield" method and/or how to do
amortized premium, in fact didn't even know we were supposed to be making that
adjustment

I am not looking for a template to calculate tax basis for a specific
security.... I am looking for a more generic, although accurate,
approach that would display amortization of premium and accrual of OID
graphically over time.

The code I wrote was not intended simply to calculate tax basis at time of
disposition. It calculates the adjustment to the interest reported on Form
1040, Schedule B, resulting from amortized premium or accrued interest, for
each coupon period.

As I said, it took 57 pages of VBA code to accomplish that. And I didn't
handle every possible type of bond, call/mandatory put situation, etc, either.

At any rate, I don't know of any templates, add-ins, or other software that
will do exactly what you are after.

Excel's YIELD function gives the result required by the IRS, so you should be
able to use YIELD and the value at the beginning of the period to calculate
the "earned" interest for a period. The difference between that amount and the
coupon payment (positive or negative) is the accreted interest or amortized
premium for the period. Given that information, you should be able to set up
the equivalent of an amortization table showing the value at each coupon date.

 

[Jay]

I am a CPA, although I don't practice as one, as well...

I am not looking for a template to calculate tax basis for a specific
security; I have access to a Bloomberg terminal which does that very
efficiently. I am looking for a more generic, although accurate,
approach that would display amortization of premium and accrual of OID
graphically over time. I realize that some simplifying assumptions
would have to be made: e.g., only taxable, or only tax-exempt (my
preference); if only tax-exempts, the yield-to-worst date (based upon
the provided purchase price and purchase date) and associated
redemption price; perhaps excluding zero coupon bonds; perhaps
excluding all optional redemptions; definitely excluding step coupon
bonds. I am not looking for any adjustments for historical events --
like an issuer exercising or not exercising its redemption rights; this
would be only forward-looking.

All bonds would accrue interest on a 30/360 basis and pay interest
semiannually.

There's an Excel template for bond premium amortization at
http://office.microsoft.com/templates/default.aspx
Click on "Personal Finance" first (it's under "Finance and Accounting"),
and then on "Bond Amortization." You can download the Excel template from
there.

Maybe the ideas behind the template would be useful to you. It doesn't have
a graph, but you could add one that uses the appropriate columns.

To use the spreadsheet, first, fill in the first four blanks. (Leave
"Effective rate" blank at this point.)

Then, use
Tools >> Goal seek
To calculate the "Effective rate."

Specifically, use "goal seek" to make the final "Carrying amount of bond"
value equal to the "Face value of the bond" by changing the "Effective
rate."

(Disclaimer: I'm not a CPA, just somebody who wanted to figure out the same
stuff.)

 

[Myrna Larson]

Interesting idea, but the IRS dictates how to calculate the interest rate.

 

[Myrna Larson]

Doesn't look to me like it's any more sophisticated than one a person could
construct themselves, but it could prove useful. Thanks for the link.

 

[Jay]

IMO the tax code is pretty absurd in this area. It's almost enough to

deter a person from buying bonds. No "ordinary mortal" could possibly
do it without help.

Or even with help. ;-)

Interesting idea, but the IRS dictates how to calculate the interest
rate.

According to IRS Pub 550, "Your yield is the discount rate that, when
used in figuring the present value of all remaining payments ...
produces an amount equal to your basis in the bond." Goal Seek seems to
be one way to implement this calculation in Excel. Following the steps
in Pub 550 literally, I tried Goal Seek and got the correct answer for
the example in the Pub.

The template at
http://office.microsoft.com/templates/default.aspx
doesn't follow the steps in Pub 550 literally, but seems to be
equivalent.

Now for sure Pub 550 is just an overview, and professionals know how to
deal with many, many more complications than appear there. But hopefully
it conveys the basics.

Doesn't look to me like it's any more sophisticated than one a person
could construct themselves, ....

That's a good thing, right?