Show Bond Interest

rmweiss12

About 30% of my portfolio is in bonds. They typically pay interest twice a year.
If I enter these interest payments with a INT transaction type, they will go to cash.
But I don't see them separately in the Holdings worksheet - which is what I would like to have.
I can enter them as DIVTA and then they show up together with the other dividend payments in the Dividends column.
I really would like to see Bond Interest separately from the dividends in the Holdings worksheet so I can see the combined returns of Cap Gain and Interest paid.
Is there an easy way to accomplish this?

VidasMatelis

By design, you should be using DivTA to record bond interest, so you can assign interest amount to symbol that this amount was paid for. But by default, PS does not separate dividends paid on equities or bonds.
I would do the following:
In srcSymbol worksheet use "Symbol Group1" (or 2 or 3) column to populate with values "Equity", "Cash" or "Bond".
For Holding table add Slicer for "Symbol Group1". When you will select this slicer value "Bond", you will see just "Bond" holdings and dividend showed will be just for your bond symbols.
If you do now how and if your Excel version support adding new calculations, you can add new calculation:

Bond Interest:=CALCULATE([Dividends], Symbol[Symbol Group1] = "Bond")

rmweiss12

OK; I will try that. It's been a while back that I created Slicers in Excel. I am now running 64 Excel on Microsoft 365 MSO.

Another issue for bonds is the monthly changing cost basis (not sure why that is).
But I only enter Cost Basis once when I buy the bond.
in the psConfig file under the GeneratedQuotes section, Is there a way to add Cost Basis to Format so it reads: "Symbol/MinData,MaxDate,Price,CostBasis"?
This would update CostBasis monthly together with Price.

VidasMatelis

Hi,

Cost basis should be static - that is an amount you paid for bonds.
I am not good with bond tracking as I do not buy them, but my understanding would be that at the time of bond purchase, you would be paying for bond (that would be cost basis) and accrued interest (earned but not paid out interest at the time of buying).
So you purchased a bond for 1000$, but 50$ of that would have been accrued interest.
I would then enter transactions like this:
Bond Buy 950$
Bond DivTA -50$ (that is a negative dividend amount)

Cost basis should stay static over time - it represents the amount you paid for your bonds.

rmweiss12

I believe that if I hold the bond through maturity, the cost basis would be the price paid for the bond.
I will check with my broker why bond cost basis changes every month. Maybe there is a tax and/or bond premium issue (pay more than par value of the bond) which causes a variable cost basis.

rmweiss12

I looked into the variable cost basis for Bonds. The cost basis changes indeed as the bond amortizes over time. If the bond was purchased at a premium (over par value), the cost basis will decrease over time until maturity at which time it reaches the price paid. Cost basis will increase over time if the bond was purchased at a discount (below par value). Unfortunately, in PS I can not input a variable cost basis. Unless you see an easy way of doing that. One would probably have to create a new script that creates a file of the changing cost basis (like dividend or price). It certainly would make PS more accurate for Bond tracking. Could you comment please?

VidasMatelis

You cannot track bonds that way - it is not supported by PS. "Proper" bond tracking is a bit more complicated.
But I believe you can get exactly the same results by properly marking for your bond transactions - what is cost basis, what is the actual sale price and what is interest (in PS as dividends).