Forum Discussion
Question on Calculations
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.