I just got a new iMac, and MS Off ice to go with it. Previously I have only had Word.Anyway, I made a first, feeble, attempt at doing compoound interest using Excel, with Financial Calculator plugin. I want to do a CD, but at the bottom of the fill-in list (including dates, interest rate) it asks for coupon. With a CD, should I be entering what the CD is worth at the starting date of the calculation as "coupon"?The MS help, isn't.
Anyway, I made a first, feeble, attempt at doing compoound interest using Excel, with Financial Calculator plugin. I want to do a CD, but at the bottom of the fill-in list (including dates, interest rate) it asks for coupon. With a CD, should I be entering what the CD is worth at the starting date of the calculation as "coupon"?A coupon is a regular payment of interest from a bond. A $100 bond which gives you a $2 coupon every 6 months has a coupon rate of 4% per year.Most CDs do not make regular payments of interest, but instead give you the interest as a lump sum on the maturity date. Such investments have a 0% coupon.
I knew that..., too frustrated to think. I entered 0 as coupon and .8% as interest, and it still won't calclate.Think I'm going to see if there's a remaindered, outdated, hw to use excel book at Barnes and Noble. I see why they offer courses. Thanks for the help, but I need a tutor over my shoulder. I'm sticking to poetry.
I'm a bit hesitant to suggest to a TMF "old timer" and very helpful poster but have you checked the Spreadsheet board at http://boards.fool.com/Messages.asp?mid=18043838&bid=100002 Lots of Excel nerds there that have been very helpful.
Thanks Bob,I'll play aroound a little more on my own, first, but I'm not afraid to ask for help. I did realize I said coupon before when I meant par, which makes more sense, but the calculator still isn't coming up with a number.
Personally, I'd start with a $1000 CD paying 10% for one year and see if the numbers I got made sense. Then 2 years, then compounded quarterly, etc.As always, even if you get it to start calculating, test the numbers before you trust them. (Do you need one of those chip upgrades or do you have rounding errors?)
foo, My apologies in advance, but I think you are mis-using the term "0% coupon" in refering to a CD and confusing Loki as to how he might calculate his "coupon". A separate point. CD's come in a lot of flavors. Some do post periodic interest and, if you withdraw before maturity, you can capture most of the accrued interest.(And, sometimes, they even have "hardship" clauses where you can capture all of it.)Loki, Excel isn't magic. It's just an easy way to manipulate more data than would be reasonable to do by hand, but, if you can't do it by hand --however many man-years it might take--, you can't do it in Excel either. You don't need an Excel manual. You need to step back and think about what you are telling the computer to do for you. It will do anything you want, rapidly, tirelessly, errorlessly, PROVIDED, you supply it with an algorithm. (i.e., a finite list of instructions that can be proven to terminate). Forget the plugins. Simply write your formula, seed it, and then drag it down the column, and Voila! the answer you expect is the last row.In your case, divide ending value by starting value, annualize (easily done with the compound root function found on any scientfic calculator)and that is your inferred compounded rate of return. If you want to normalize your CD's "coupon" to be consist with regular bonds, just run approxiations if you don't want to write a formula, which is something that Excel excels at - displaying a wide range of results generated from stepwise varariables. Charlie
My apologies in advance, but I think you are mis-using the term "0% coupon" in refering to a CD and confusing Loki as to how he might calculate his "coupon".My understanding was that coupon was an input to the calculator being used, not an output. Otherwise Loki would not be asking what value to enter for coupon. Since he meant "par" instead of "coupon" anyways, the point is moot.A separate point. CD's come in a lot of flavors. Some do post periodic interest and, if you withdraw before maturity, you can capture most of the accrued interest.(And, sometimes, they even have "hardship" clauses where you can capture all of it.)Yes, which is why I wrote "most CDs", instead of "all CDs".
foo, You're right. Loki was using a program that asked for an input he didn't immediately have. My point was that he should have used Excel to create the input he needed. And in doing so, he would have seen how lame the plugin was and what a self-sufficient and easy to use tool Excel can be. Charlie
I supported Excel for many years. In most cases, the functions provided were very specific and narrow. Your best bet is to look up the appropriate formual and calculate it in the worksheet. (You usually have to do this just to make sure its doing what you think its doing<sigh>.)
Thanks all,I did finally get the plug-in for compound interest to put out a number: incorrect, but a number. I'll try Charlie's do it yourself idea, but I frankly don't know why I care. We got Office on the cheap with the new iMac, and my wife needs Power Point (which I loathe as an example of reducing the complexity of thinking that comes from writing sentences and paragraphs and essays to slogans) for some new work she's doing, so I figured since we got Excel thrown in I'd try to do something with it. Don't really need it.By the way, I love the iMac. I know most of you aren't Mac users, but if you're thinking about a new PC (Windows), you might want to wait until Dell or somebody steals the design. It takes up much less space and has an adjustable flat screen, which I find wonderful, especially for those of us with aging eyes and bifocals.
Loki,I have the previous model iMac running OS 10 and the latest Microsoft Office suite. I find EXCEL indispensible for financial planning. I enjoy building and testing spreadsheets. Once you get into it, I suspect you'll find it a satisfying challenge and dream up all sorts of spreadsheets to cover various aspects of your life from tracking your physical weight and BMI to your financial weight and NAV. I created a table that predicts growth of income from various sources, growth of total income, and growth of portfolio value over the next 40 years, given a number of parameters I provide like expected return, expected CPI, rate of draw, and so forth. It's intersting and instructive to vary the parameters and observe the changes in expected values. Another table tracks our asset allocation and shows what needs to be sold or bought to maintain the balance we want. I think the combination of Quicken and EXCEL is unbeatible.Wish I had your new iMac with its wide display -- perfect for spreadsheets.Incidently, at the time I left the high-tech world in 1992, the techies used Sun or Silicon Graphic workstations and Macs (Symbolics LISP machines had largely gone away by then). Only secretaries and clerical personnel used PCsdb
Best Of |
Favorites & Replies |
Start a New Board |
My Fool |
BATS data provided in real-time. NYSE, NASDAQ and NYSEMKT data delayed 15 minutes.
Real-Time prices provided by BATS. Market data provided by Interactive Data.
Company fundamental data provided by Morningstar. Earnings Estimates, Analyst Ra