Message Font: Serif | Sans-Serif
 
UnThreaded | Threaded | Whole Thread (23) | Ignore Thread Prev | Next
Author: joelcorley Big gold star, 5000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: of 50  
Subject: Re: Anyone home? Date: 6/14/2011 12:59 PM
Post New | Post Reply | Reply Later | Create Poll Report this Post | Recommend it!
Recommendations: 2
BenSC,

You wrote, Me, with the slight touch of OCD that I possess, decided to create my own spreadsheet of these securities. I took the initial results from Quantum and further screened these securities for companies that paid a regular dividend of at least 2% (why 2%? well, had to pick a number and that seems to me to be a decent cushion though I know one cannot count on REITs to pay at a consistent rate). I also stripped out any securities that are called or partially called or have anything "funky" going on with them. This has given me a list of 96 securities to continue refining. My next steps are to input in the current price, S&P rating, M* evaluations, and price variance for the security. I'm not quite halfway through that task. Once I get done with that spreadsheet I'll go from there.

I do something similar. At least I have a list based on a list from year-end. I recommend importing the sheet into GoogleDocs. A GoogleDocs sheet can get live (delayed) quotes for each security.

I have my spreadsheet broken down into 2 pages - one with raw QuantumOnline data and another for mining. The Quantum data page has the following columns based on their original reports, plus some search-and-replace massaging:

Symbol	
CUSIP
Security Description
StockExchange
IPO Date
Cpn Rate
Ann Amt
LiqPref
Call Price
Call Date
Matur Date
Moodys
S&P
Rating Date
15% Tax Rate
Conv
IPO Prospectus
Distribution Dates
First Dist Month
First Dist Day
Frequency
Suspended


The mining page has the following:

Symbol	
Security Description
Ann Amt
Cpn Rate
Price
LiqPref
Yield
Post-Tax Yield
Matur Date
YTM
Post-Tax YTM
Call Date
YTW
Post-Tax YTW
Moodys
S&P
15% Tax Rate
Suspended
Distribution Dates


Some of the items are just lookups from the data table. Others are GoogleFinance() lookups. Others are formula. The post-tax columns are really just estimates based on an assumption of a 25% marginal income tax rate. However, I find the post-tax estimate to be very useful in comparing returns for taxable accounts.

The entire thing is just a work-in-progress. I'd like to adjust my Yield, YTM and YTW calculations by the amount of accrued, but unpaid interest - I can know this based on the payment dates and the coupon. I'd also like to create a table of estimated default risks (as a ratio) based on the Moodys & S&P ratings. I could then discount Yield, YTM & YTW based on the estimated default risk so I can attempt to compare all of the fixed yield securities directly.

Anyway, thought that might get you thinking about how to analyze the securities available.

- Joel
Post New | Post Reply | Reply Later | Create Poll Report this Post | Recommend it!
Print the post  
UnThreaded | Threaded | Whole Thread (23) | Ignore Thread Prev | Next

Announcements

Pencils of Promise - Back to School Drive
"Pencils of Promise works with communities across the globe to build schools and create programs that provide education opportunities for children."
Post of the Day:
Value Hounds

Considerable Headwinds for Profire Energy?
What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Community Home
Speak Your Mind, Start Your Blog, Rate Your Stocks

Community Team Fools - who are those TMF's?
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and "#1 Media Company to Work For" (BusinessInsider 2011)! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.
Advertisement