Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalc needed for successive years
You wouldnt need to programme anything.
Simply create a new workbook. In column A put weeks 1-52. Col B put your income from your pension Col C put a formula that looks at the amount in your savings and calculates your monthly interest payment (e.g. in col C =F1*0.05/52) where F1 is the cell that has your current savings balance, 0.05 = 5% annual interest and 52 is the number of weeks in a year). Col D put in your expenditure (as a minus figure). Col E put = SUM(A2:E2). At the bottom of Col E put a sum formula e.g. =sum(E2:E53) This would be your first sheet. Then copy this sheet, name it as next year... change the entry in the savings amount to equal a formula that says =2005!E53+2005!F1 Now copy this sheet and rename it 2007, 2008, 2009 etc etc changing the formula in teh savings cell (F1) to be the result of hte sum in E54 + F1 from sheet 2005. I've emailed you a start,... note it needs formatting etc. This solution would be oversimplified as it assumes that you don't earn any interest from any money you have left each month.. but as a rough guide would do. The only thing programming would help in is to reduce the mundane task of copying the sheet and changing the formula.... Hope this helps |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalc needed for successive years
I use WIN98 and Excel. I need to do the following sum on a
worksheet\spreadsheet YEAR ONE My pensions produce an income of A My Savings are B My savings delivering an income of C So my total income is (A+C) Expenditure is D So over the year the surplus\deficit is (A+C)-D I then say E=(A+C)-D If I am receiving more than I spend, then E will be a plus figure, if the other way round, a minus. Problem: I now want for year TWO to add E into my savings figure, either increasing it, if E is a plus, or decreasing it if E is a minus. This in turn increases or decreases the income from my savings. And so on for (say) twenty or thirty successive years. Can I do this on Excel? If so, how? Is this the right newsgroup? If not, which? TIA Mr Warwick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalc needed for successive years
Thanks, will reflect
Mr W "cheesey_toastie" wrote in message ups.com... You wouldnt need to programme anything. Simply create a new workbook. In column A put weeks 1-52. Col B put your income from your pension Col C put a formula that looks at the amount in your savings and calculates your monthly interest payment (e.g. in col C =F1*0.05/52) where F1 is the cell that has your current savings balance, 0.05 = 5% annual interest and 52 is the number of weeks in a year). Col D put in your expenditure (as a minus figure). Col E put = SUM(A2:E2). At the bottom of Col E put a sum formula e.g. =sum(E2:E53) This would be your first sheet. Then copy this sheet, name it as next year... change the entry in the savings amount to equal a formula that says =2005!E53+2005!F1 Now copy this sheet and rename it 2007, 2008, 2009 etc etc changing the formula in teh savings cell (F1) to be the result of hte sum in E54 + F1 from sheet 2005. I've emailed you a start,... note it needs formatting etc. This solution would be oversimplified as it assumes that you don't earn any interest from any money you have left each month.. but as a rough guide would do. The only thing programming would help in is to reduce the mundane task of copying the sheet and changing the formula.... Hope this helps |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalc needed for successive years
Thanks for your sample spreadsheet Savings.xls which I am starting to
understand. One question. At the bottom there are three tabs labelled in the original master spreadsheet Sheet 1, Sheet 2, Sheet 3. You have relabelled these 2005, 2006, 2007 and I understand why. This will do mef for three years. But I want the programme to predict *30* years ahead. How do I do this; do I complete another worksheet with formulas referring back to the first sheet, or what? Grateful advice. Mr W "Mr Warwick" wrote in message ... Thanks, will reflect Mr W "cheesey_toastie" wrote in message ups.com... You wouldnt need to programme anything. Simply create a new workbook. In column A put weeks 1-52. Col B put your income from your pension Col C put a formula that looks at the amount in your savings and calculates your monthly interest payment (e.g. in col C =F1*0.05/52) where F1 is the cell that has your current savings balance, 0.05 = 5% annual interest and 52 is the number of weeks in a year). Col D put in your expenditure (as a minus figure). Col E put = SUM(A2:E2). At the bottom of Col E put a sum formula e.g. =sum(E2:E53) This would be your first sheet. Then copy this sheet, name it as next year... change the entry in the savings amount to equal a formula that says =2005!E53+2005!F1 Now copy this sheet and rename it 2007, 2008, 2009 etc etc changing the formula in teh savings cell (F1) to be the result of hte sum in E54 + F1 from sheet 2005. I've emailed you a start,... note it needs formatting etc. This solution would be oversimplified as it assumes that you don't earn any interest from any money you have left each month.. but as a rough guide would do. The only thing programming would help in is to reduce the mundane task of copying the sheet and changing the formula.... Hope this helps |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalc needed for successive years
I would create a sheet (copying the last) 30 or however many times you
wish. So a sheet for 2008, 2009 ... 2029, 2030. If however you are just trying to get the final calculation then this isn't necessary I just assumed that at the end of each year you wanted a review of the year. Also your expenditure, interest rates, savings etc are all variables that may change from year to year and this could then be adjusted whilst keeping the history. Hope the helps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seniority Years Formula needed | Excel Discussion (Misc queries) | |||
How to chart monthly sales for successive years | Charts and Charting in Excel | |||
How do I subtract dates to get a number in years or years & month | Excel Discussion (Misc queries) | |||
Recalc needed for successive years | Excel Discussion (Misc queries) | |||
Overlay 4 years of data as a line on 4 years of columns for several x category labels | Charts and Charting in Excel |