Jun 27 2020 03:05 AM
How to use logical formula. one column contains % of withdrawal amt and one column contains Expenses .if i want to change expenses column the percentage column should accordingly.
Jun 27 2020 03:35 AM
Jun 27 2020 03:39 AM
Jun 27 2020 07:29 AM
I have attached the file, Figures in coloumn G should not go negative if i change the figures in coloumn F.
Jun 27 2020 11:42 PM
@BABA1978 The expense in column F seem to be predetermined (fixed amount plus periodic inflation adjustments). If you then want to avoid the amounts in column G to go negative, the SWP Monthly amount in columns C may not be lower than the amount in F. In generic term, the formula in C would be like: "=MAX(Fn,<SWP Monthly formula>)", where n is the row number.
To be more specific, on row 24 (= Month 21), the formula in C24 would be:
=MAX(F24,(B24*3.5/100)/12)
This will return 90825 in C24, resulting in 0 in G24 rather than -181.
Jun 28 2020 02:46 AM
Thanks for the reply. requires more help.
The percentage in C24 remains static. If we increase or decrease expense the percentage (in C24)should change accordingly and balance column should not go negative
Jun 28 2020 03:37 AM
Jun 28 2020 03:47 AM - edited Jun 28 2020 03:50 AM
@BABA1978 Perhaps you can start by explaining the logic for the changes in C. Currently, the uplifts are hardcoded in every cell and are far from static (starting with 3.5, then 9.9 in month 12, thereafter 3.6 but then in month 21 it goes down to 3.5 again. Then 9.5 in month 24 and then 5.9 from month 25 onwards etc. etc. Could it be that you simply forgot to change the factors for months 21 through 23?
In the expense column you have amounts with a note every 12th month that mentions an uplift for inflation by 5% (plus 60K for each 12th month).
If you want to automate anything, it's important that you clearly define the rules for it first. These can probably be structured in a table that allows you to pick-up the correct percentages, inflation adjustments and yearly extra expenses. Just guessing though.