Re: Calculating compound interest
Re: Calculating compound interest
Source: http://www.tech−archive.net/Archive/Excel/microsoft.public.excel.worksheet.functions/2006−08/msg02651.html
• From: "PatJennings"
• Date: Fri, 11 Aug 2006 14:13:27 −0400 Thank you Bernie, I finally understood your calculation. Pat "Bernie Deitrick" wrote in message news:OVgB6YJvGHA.1288@xxxxxxxxxxxxxxxxxxxxxxx Pat, Normally, you need to have two different formulas, one for the first row (where it is the first value), and one for the second row and down, after your table has started. I just wrote it so that one formula would work. TRY setting up the formulas as I described − they do exactly what you want. These were the titles you listed, that I used in setting up my table: A1 Low Cost B1 Fixed Cost C1 Difference D1 Interest E1 Account Balance
HTH, Bernie MS Excel MVP
"PatJennings" wrote in message news:eS9LqQJvGHA.1772@xxxxxxxxxxxxxxxxxxxxxxx Bernie, Your assumption in your first sentence is correct. However, I'm confused as to the contents of columns D and E: the crux of you solution. Here are my column headings: A: Variable rate premium (escalating) B: Fixed rate premium (constant) C: Difference between the Fixed and Variable premium Re: Calculating compound interest 1
Re: Calculating compound interest D: Number? times the interest rate E: Number? plus the values in C and D It looks like D is calculating a number from a value in E1, which I believe is column heading. E appears to be saying if there is a value in E1 add it to the values of C2 and D2, etc. I'm lost. I can't figure out what is the title of columns D and E. I appreciate your assistance, and look forward to your helping me understand your solution. Sincerely, Pat "Bernie Deitrick" wrote in message news:eEhkN99uGHA.5044@xxxxxxxxxxxxxxxxxxxxxxx Pat, I have assumed any cost of the variable rate poilicy above the fixed cost policy comes out of the savings account pricnicpal, interest, or both. Put your headings in cells A1:E1, then is A2:E2 put these values/formulas: A2 100 B2 500 C2 =B2−A2 D2 =IF(ISNUMBER(E1),E1,0)*0.04 E2 =IF(ISNUMBER(E1),E1,0)+C2+D2 Then copy B2:E2 down as far as you need, and put in your escalating premiums into column A. Note that the 0.04 could be a cell reference, if you expect that the interest rates will change. Perhaps: =IF(ISNUMBER(E1),E1,0)*F2 and then enter the interest rate assumptions in column F, as percentages... HTH, Bernie MS Excel MVP
"PatJennings" wrote in Re: Calculating compound interest 2
Re: Calculating compound interest message news:%23JFpj9juGHA.4876@xxxxxxxxxxxxxxxxxxxxxxx Actually, it is more complicated than that which I stated in the question. Let me try to give a better explanation. The problem involves the age−old dilemma of buying low cost insurance with an escalating premium cost rather than a fixed−price, higher cost premium and investing the difference between the two. At some point, the initially low−cost insurance premium becomes more costly than the fixed−price premium. At that point, the "earnings" or balance in the "invested account" would be used to offset the higher cost of premium. Also, since there is no longer a difference to invest, no additional money can be added to the account. For example, assume for argument's sake that the cost of the low premium is $100 and the cost for the fixed price premium is $500. In the first year, there would be $400 to invest. For the second year the cost would be $110 and $500; third year $120 and $500. Each year the difference in premiums would be added to the investing account and, for simplicity, earn interest at, let's say 4% per year. At some point in time the originally priced $100 will increase and become greater than the $500. At that point, the investing account would have to supply the difference. Interest would continue to be earned on the account balance. My columns are: Low Cost, Fixed Cost, Difference, Interest, and Account Balance, which is the sum of the preceding two columns I would like to provide for the eventualities stated in the first paragraph. Re: Calculating compound interest 3
Re: Calculating compound interest Hope this makes it clearer. Thank you for any help you may offer. Pat "Bernie Deitrick" wrote in message news:uTUgXBiuGHA.1288@xxxxxxxxxxxxxxxxxxxxxxx Pat, Your question is a bit unclear. What does your data table look like? Does it have interest earned in the second column, or total balances? Bernie MS Excel MVP
"PatJennings" wrote in message news:eWxcNlauGHA.2448@xxxxxxxxxxxxxxxxxxxxxxx I have a column of years and a column of numbers representing annual amounts placed in a savings account for the year. I would like to calculate the balance of interest earned added to the balance of the account and then calculate Re: Calculating compound interest 4
Re: Calculating compound interest the interest earned on the accumulating amounts each year. The results would be the account balance displayed in an adjoining column. So far, I have not found a worksheet function for that. Could someone point me in the right direction? Perhaps there should be several columns of data? Thanks
. Re: Calculating compound interest 5