Short: Calculation for money makers Author: Dirk Neubauer Uploader: dirk.neubauer@guestrow.netsurf.de Type: biz/misc Version: 0.4 - TurboCalc-Sheet for a simple calculation - - of pure profit out of investments - Introduction ------------ Sometime I wanted to compare some kinds of investments (fonds of stocks, securities, estates, deposits etc.). The reason was to find out the pure profit made. The only thing I want to know is the investment which yields the most money. That was the reason for creating this sheet. Till now you can use it for fonds of stocks, estates and securities distributing the profit. For fonds thesauring maybe some small changes are needed, this will probably be done in the next releases. Also I want to do some changes for deposits month by month, but it's a little bit difficult to do. The sheet supports yet random calls following the first one. If you've only straight bonds, you don't need such a sheet, because of fixed interest rates. The values calculated by this sheet are not correct to the last cent, but usable. I don't need more accuracy. The calculated "profits" are more accurate the longer the runtime is. If you want more features for this sheet, then you should contact me. My address: email: dirk.neubauer@guestrow.netsurf.de snail-mail: Dirk Neubauer Bgm.-Dahse-Straße 14A 18273 Guestrow Germany Phone: +0049-03843-843362 Requirements: - spreadsheet program (TurboCalc, FinalCalc, Excel, Lotus 1-2-3 etc.) The sheet was created on: Amiga1200T/040 40MHz OS3.1 32MB-RAM Picasso2+/Picasso96, FlickerMagicT with use of TurboCalc 5.02 It was also tested on: Amiga500 7MHz OCS OS3.0 1MB Chip 8MBFast 1.6GB-HD + TurboCalc V3.5 Amiga1200 030/882 50 MHz OS3.1 18MB-RAM + TC4.0/5.0 Amiga1200 PPC603e160+68040/5 48MB Fast BVision CGX_V3 SCALOS 1.2a + TC3.5 Amiga4000T CSPPC604e200+68060/50 2MB Chip 144 MB Fast PicassoIV + TC 3.5/4.0 Shapeshifter/MAC 1MB-ROM, System7.51, 16MB RAM, 68030/50, Excel 5.0 + (SYLK format) PC/WinNT4, PentiumII 300 MHz, Excel97/98 + (SYLK format) This text was written by use of CygnusEd V4.20 and GoldEd Studio 5.0.6 Warranties ---------- There is NO warranty for the correct function of this sheet. It is not tested on other as the described systems (above). The export formats are not tested very good because of rare possibilities. There's no responsibility or liability for damages on hard- and software or data by use or misuse of this sheet. And there's no warranty for correct working of this sheet in every case. Usage of the sheet ------------------ There are various drawers. One for every supported language. You must look for your favourite one. In the ATO.readme you can look for all supported languages. Please load the version you need: FondsCalc.TCD for use with TurboCalc 3.5+ (maybe earlier, too) FondsCalc.slk (SYLK-Format for use with other spreadsheets) The import function of the sheet by SYLK are tested successfully with Excel5 (MAC) and Excel97 (WinNT). In Excel you should note, that the switch "Use alternative formulas" under options is activated. Otherwise instead of formulas the values were imported, which also exists at the SYLK file. I don't have experience in use of Lotus 1-2-3. There are some restrictions by use of the SYLK format, which will be described below. The names of the columns are easy to understand, I hope. Nevertheless users of TC4.0/5.0 have a small advantage, they can use cell notes, if the statusbar is activated (global settings). Then you get for every cell, that contains values, little notes about the reason and creation (manual entry or calculation) of this value. (Every fonds has its own sheet. This way it is easier to manage, I think.) Small notes: The default font is 'cgtimes'. This font is good for WYSIWYG and printing. Besides, it is delivered with original workbench. The headline is fixed to use this font. If you want to change the font, you only have to change the default font (in TurboCalc!). Besides, I must say I use a screen mode of 800x600x256 for TurboCalc. There are modifications for colours for background and foreground on some cells. I've used all possible colours, and so you must change the used palette, if you have less than 32 cols. Otherwise there could be text in colour 'white'. This case appeared during testing on A500/OCS. By use of the SYLK sheet no settings for colours or fonts were imported. The iteration settings for calculations in TurboCalc were set to 2 and saved (Sheet/Settings/Calculation...). This became necessary, because after taking the tax into account, the interest was not re-calculated. If you use a different spreadsheet software, you should set it up accordingly. Because "iterative calculation" is only supported since V4.0 by TurboCalc, users of TurboCalc V3.5 have to choose "Command/Recalculate" after changing "Actual value" (or any other) for correct calculation at every cell. In cell A1 is the title. For XX you can insert needed names. These are the kind of fonds, the name of it, the company and bank, too. example: Performance of the value for the fonds Weka-Nord (Corporation Nord-Invest) on Vereins- und Westbank. In cell A5 you should insert number of shares by your call. There are possibilities for sharing parts, and therefore no integer value in this cell. Shares from following calls you can insert into cells A8 and A11. If you don't own such shares, please insert 0 there. If three cells are less than you need for calling shares, please contact me. At the SYLK format no settings for the structure of numerical values were imported. In cell B5 you should insert the value of a share by calling (in Euro). In this value the spread for the call is integrated, of course. In cell B6 the value in your currency appears. The cells B8/B9 and B11/B12 are planned for following shares. If you don't own such shares, please insert 0 there. For all details to currency in this sheet: After the value the abbreviated currency is showing. You should change it, if you use another currency as the default (EUR, DM), because it's only normal text. In this case you only have to change the text in cells C5/C6. All other texts are updated automatically. In cell M20 you should insert the exchange factor for your currency. This value describes how much of your currency are 1 Euro. The default value is 1.95583 for German marks (DM). In cell D5 (yellow cell) you should regularly insert the actual value for a share (in Euro) for getting a real calculation. Of course, that means the value for cash out shares. This cell is the only one, which you have to hold in current status by yourself. The entries in all other cells are only to set by start. In cell D6 the value appears in your currency. In cells F5/F8/F11 the differences between the value by today and by call is appearing (in Euro). A negative value describes a loss (especially in the weeks and months after call as direct result of the spread). In cells F6/F9/F12 the same values for your currency is appearing. In cell H5 the date for the call is needed. The cells H8/H11 are planned for dates of the calls for following shares. In cell I5 appears the current date. In cell A16 the number of shares is specified, which are called with the cash out. It contains only "won" profit, so it's an 100% tribute, of course. In cell J5 the difference for the performance (in Euro) for the time of one year is given. If the call isn't one year ago, there could be relatively big gains or losses. That depends on the speculation times for which no real predictions are possible. Securities and estates should show useful values after a few months already. After first cash out there should be normal values for fonds of stocks, too. In cell J6 are the same values for your currency. In cell I20 you should insert the appropriate costs of your depot (in %). example: 0.00125 means 0.125% (1 means 100%) At SYLK format no settings for structure of numerical values were imported, and so in this cell appears the decimal instead of the percent value, p.e. 0.00125. In cell I21 you should insert the counts of paying these costs during one year. (usual values are 4 to payment per quarter). In cell J20 you should insert the appropriate costs for the managing (in%). example: 0.00125 means 0.125% (1 means 100%) In cell J21 you should insert the counts of paying these costs during one year. (usual values are 4 to payment per quarter). If there are no such costs needed for your investment, or these already calculate into the performance, then you should insert 0% in the appropriate cell. In cell L20 the tax rate is needed. In Germany the tax for cash out is about 30.5%. There is a small amount of cash out, which is free of tax (999 DM). Please contact me for other values or calculation models for your country. In cell L21 the amount of cash out is needed, which is free of tax. In Germany there are 999 DM. In cell B22 the total amount of money for the call (in Euro) is given. In cell B23 is the same value for your currency. In cell D22 the current amount of the investment (in Euro) appears. That is the value you get by put today. In cell D23 is the same value for your currency. In cell F22 the current amount of the investment after taxation (in Euro) appears. That is the value you get by put today, and have to give a part to your government. In cell F23 is the same value for your currency. In cell L5 the true profit (in %) is given. There are already all appropriate costs included. In cell M5 appears the yearly "interest rate". Because a fonds of stocks doesn't cash out an interest rate, I "invented" this value to compare it with normal straight bonds. This value describes the theoretical interest rate for a straight bond with the same tribute as the appropriate fonds. This value is highly accurate and a very good thing for comparing. History: -------- v0.1 - first public version v0.2 - improvements for compatibility to MS-Excel - slovenian translation v0.3 - Improvement in the calculation v0.4 - Adding cell notes as small "online help" - Rework for SYLK-Export - Improvement on clarity (texts for currencies) Thanks to: ---------- - Michael Friedrich for TurboCalc - Ossowskis Schatztruhe for highly usable Amiga-Soft - my father for introduction to money bussiness - my brother for testing this sheet on his A500 - Horst Schumann for testing this sheet on his system and proofreading this readme file - Ronald Schmiers for testing this sheet on his system and his numerous suggestions, tips and infos. - Michael Becker for testing this sheet on his system - Dale Frameli for his suggestions - ATO members for translations - all users, which mailed me - the financial arm of the German government :-> Future ideas ------------ - Manual in guide or html format ? - Improvements in the layout (cols, fonts etc.? - Add-on for fonds thesauring (sure!) - Add-on for costs of depots with fixed values ? - Add-on for calls month by month (VL) REALLY SURE! - Improved calculation for taxes - Fixing of bugs ?!? - Localisation for many languages and specific adaptations for these countries (perhaps)? - Calculation with inflation ? - Your suggestions.... Copyrights ---------- - TurboCalc is copyright by Michael Friedrich 1993-98 - FinalCalc is copyright by Softwood - Excel is copyright by MicroSoft - Lotus 1-2-3 is copyright by Lotus - CygnusEd is copyright by CygnusSoft-Software - Amiga is copyright by AmigaInc. This sheet is mailware, that means, it's nice for me to get a message from people using this sheet (email, postcard etc.). It would be nice to know, who's using this sheet. Besides on this way I get suggestions, criticism etc. very quickly. June '99