Forum Discussion
Question on Calculations
Greetings,
I have a question. I am trying to figure out a how to see something in excel. Below is the scenario:
A person has 300,000 in year 1. they lose 30% of that money and they also pull 4% from that money.
year 2: they make 26% and pull 4.2% from that amount.
year 3: they make 14% and pull 4.4% from that amount.
year 4 they lose -3% and pull 4.6% from that amount.
What is the correct formula to use in order to see what they will have at the end of year 4?
Also, what is the correct formula to be able to see what the amount would be if the starting amount were 195,000 vs 300,000.
Thank you and I can provide more clarity if it is needed.
I came up with two different ways to do this, ending at the end of year 4 a little bit apart. Your words are open to interpretation however, so there may be still more ways to do this.
The simplest way is to take both percentages (loss or gain percent PLUS the "pull" percent) entered as negatives or positives, apply the sum of those percentages to the amount starting the year and add the result to the start of the year. (Note: adding a negative number is the same as subtracting a positive of the same absolute value; so by adding, I'm dealing with the negative or positive percentages consistently.)
That results in these numbers
The other way was to take the two percentages and apply them as distinct steps, still beginning with the same year one start, but taking (for example) the loss of 30% from 300,000 and then the pull of 4% from the result of the first calculation. And so on for the subsequent years. That produces these numbers.
To change the starting figure to 195,000 you'd just change the numbers in yellow in the attached spreadsheet.
By the way, your last line of "instruction" you used a double negative, I think inadvertently.
year 4 they lose -3% and pull 4.6% from that amount.
I chose to take that to mean "they lose 3%"; were I to take it literally, a negative loss would be a gain. IF in fact that's what you meant, just change the -3% to 3%. But you need to be careful in math with negatives and positives when you're multiplying and dividing, adding and subtracting.
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).
It depends on interpretation.
1. Is the percentage return and percentage deposit/withdrawal both based on the beginning balance of each period?
2. Or is percentage return based on beginning balance plus/minus deposit/withdrawal?
3. Or is percentage deposit/withdrawal based on beginning balance plus/minus return?
The follow is based on assumption #1.
Formulas:
C3: =ROUND($F2*B3,2)
E3: =ROUND($F2*D3,2)
F3: =ROUND(F2+C3+E3,2)
Copy C3 into C4:C6
Copy E3:F3 into E4:F6
For $195,000, simply enter that amount into F2.
PS.... If we did not round, we could calculate the result for 195,000 with the formula:
=195000*F6/F2
And that might be "good enough" even with rounding, depending on your definition of "good enough".
Hi Joe,
for clarification the premise is January 1, 2008 a deposit is made of 300k, and a withdrawal done the same day of 4%. Then the full calendar year would result in a lose of 30%.
Each corresponding year, 4% is withdrawn on the 1st day of the calendar year.you are also correct, I meant the account loses 3% in value.
would that change the overall formulas being used?
phairiston wrote: ``[initially] a deposit is made of 300k, and a withdrawal done the same day of 4%``
That seems unlikely. More likely: the starting balance is 300,000 (i.e. previous-year ending balance).
In any case, you want withdrawals at the beginning of the year; and the year-end gain/loss is based on the net balance after the withdrawal.
Formulas:
C3: =ROUND(G2*B3,2)
D3: =ROUND(G2+C3,2)
F3: =ROUND(D3*E3,2)
G3: =ROUND(D3+F3,2)
See the attached Excel file.
