Question on Calculations

phairiston 

It is not difficult to use single formulas to calculate the values you are interested in.  But, because of the capabilities of spreadsheet software, you can do these calculations for different starting amounts (any starting amount!) and different percentages using a single set of formulas; once those formulas are set up correctly, you only need to change the initial amount and percentages as desired.

I presume that when you wrote "they lose -3%" that you meant "they lose 3%". But in this spreadsheet, gains (and contributions, if there would be any) are consistently represented by positive percentages, and losses and withdrawals by negative percentages.

I formatted the columns with monetary amounts (B, D, and F) as Accounting values, showing values with zero decimal places (i.e., rounded to a dollar). I formatted the columns with percentages (C and E) as Percentage values, showing values with one decimal place. Cells that contain formulas I have highlighted in gray (that coloration is of course not required).

The formula in D2 (shown in the formula bar in the picture, since cell D2 is selected) takes the initial amount for the first year (in B2), and applies the gain/loss for that year (in C2) to get that intermediate result. The formula in F2 takes the result of that gain/loss calculation (in D2) and applies the (contribution or) withdrawal (in E2) to get the end-of-year result. The formula in B3 just makes a copy of the end-of-year value (in F2).

While it may appear that you need to enter twelve formulas to get those calculations done for four years, it actually requires only three formulas, plus some dragging down (or copying of cells and pasting) to repeat the formulas; Excel automatically adjusts the cell references in the formulas when you do so.

So calculating a value for 195,000 (or any other initial amount) requires a change to only one cell! (That result is 159,523.)

I am attaching this spreadsheet (in Excel terminology, it's a worksheet, and the file is called a workbook).