The JSE and finance forum for South Africa

General Category => Shares => Topic started by: erwintwr on May 25, 2015, 01:49:41 pm

Title: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: erwintwr on May 25, 2015, 01:49:41 pm
Hi guys

Since Financial independence have been bugging me a while, i decided to put some time into the following calculator.

Calculator Link (https://www.dropbox.com/s/cr8ktznmligiic8/Financial%20independence%20calculator%20v4.2.xlsx?dl=0)
(hosted on dropbox, please select download (Three dots top right)



It basically calculates over 4 evenly distributed periods what your after tax return will be for the different type of investments (and combinations there of)
Basic operation :
**Note1  If draw down pre 55  failed, salary will be shown as R0.00

Inputs:
(http://i.imgur.com/VmD7Bm1.jpg)

(http://i.imgur.com/qD86N8P.jpg)

(http://i.imgur.com/VXg33Oa.jpg)

Something interesting that the calculator now roughly allows is to determine if pre tax or post tax RA contributions is the best (remember to adjust your after tax savings percentage accordingly)


Capital gain tax is applied to Equity investments with exclusions applied.

If any obvious mistakes is picked up, please let me know. Otherwise - i hope this helps someone :)

The usual yada yada T&C's applies - cant take responsibility if an excel mistake causes you to eat beans at the age of 60 :P).

Have fun

Neo_x

*edit*
Update to version 4.2(4/05/2017)
* Added the latest tax tables (note that tax tables for future years is automatically adjusted based on a 10 year average.)
* increased TFSA to 33,000  (note that there is an option to automatically increase the TFSA)



Update to version 4.1(8/03/2016)
* Added the latest tax tables (note that tax tables for future years is automatically adjusted based on a 10 year average.)
* improved the CGT formulae to allow adapting to future tax changes (latest tax change moved the previous 33% up to 40%)

*pending - still to Add the RA contribution limit ( there is an annual amount after which the tax benefit doesn't apply on a RA)

Update to version 4.0(6/12/2015)
* Added functionality to add retirement annuity deduction as per payslip. (caution  - this is kept constant throughout the calculator and adjusted as per your pay increases. Any other contributions towards an RA will be after tax, and tax returns will be re-invested).
Although some checks is in place, please double check that your RA deduction is not greater than the allowed SARS Tax benefit.
/* this is quite a major update - if anything strange is noted on the results, please provide feedback

* Summary screen was updated -with a total Portfolio value, and some readability/formatting.



Update to version 3.8(03/12/2015)
* Improved summary page after some recommendations (added portfolio values at the FI year, so that you can have a visual of the values)

Update to version 3.7(19/11/2015)
* improved a formula to be more accurate for monthly interest(This was workbook wide, thus affects all totals)

Update to version 3.6 (7/7/2015)
* fixed a bug that slipped in when moving some of the reference tables around (affected TFSA contributions)
* adjusted tax table inflation averages a little.


Update to version 3.5
* Added a once off bonus at FI - configurable. This is to allow a lumpsum draw to happen at FI, mostly  for big expenses (paying off house/car, etc)
* Fixed tax tables between FI age and 55- changed to  CGT tax(was previously normal tax)
* Improved readability on summary screen


Update to version 3.4
*Adjusted internal calculations ,based on paying current loans (eg home /car) - expenses that wont remain into financial independence for long - configurable(change to zero if you plan to keep paying off your home/car  up to 95 :P)
*improved the results screen ,showing more details as to what is happening, including your salary at FI.

only challenges that still remains :
* Drawing a salary after FI is calculated, mostly, based on drawing your tax every month. this wont be the case, as you will draw a once off SARS amount after tax claims are due.

* Homeloan and car expenses is removed after the payment period is complete - not sure how to tackle the scenario where you want to continue wasting money into the latest and fastest clown-mobiles every few years. for now, please configure the buffer to allow for any expenses that you will keep/add after FI.


Update to version 3.3
*discovered and fixed a major miscalculation with regards to savings (was previously matching salary increases, while it should be based on salary minus inflation adjusted expenses.
Bonus part is that this increases savings, and thus will result in a quicker FI.
*added more accurate descriptions on the summary page
*added a check to show a salary shortfall (eg your increases is below inflation, which then results in running into a salary that cant carry your expenses.

Update to version 3.2
*Tax tables was "ahead" with a year - Corrected
*Improved the Salary prediction required at Financial Independence - should now be much more accurate(current expenses is inflation adjusted with buffer added, then reverse tax is applied to determine the before tax salary)
*Added a Spending buffer which will be added to your salary after FI (use full if you want to go on that yearly travel trip :p



Update to version 3.1
* Expanded draw down function - now select the most rewarding draw path (selecting between Equities/ TFSA and also splitting a third of the RA at 55 into equities or keeping it in tact)
* Added a option to block TFSA limits from increasing every  year(configurable under constants)
* fixed some calculations for extreme cases(eg paying RA and TFSA off in one month)

Update to version 3.0
Major functionality update - basically it is now a South African Financial Independence calculator. Most Inputs is customizable, including Age, Inflation, and current portfolio values. System will output 3 periods between your current age and 55 years, allowing you to see capital growth.

Update to version 2.0
* major changes in tax tables - applied 10% increase on tax tables per year (current average since 2005) - this is configurable under inputs...
* added 20 and 30 year results sets as well
* 10 year result is based on tax for a ten year "draw" (year 15 tax is applied)
* 20 year result is based on tax for a twenty year "draw" (year 30 tax is applied)
* 30 year result is based on tax for a thirty year draw (year 45 tax is applied)


Update to version 1.7
* Fixed tax tables related to tax return(now properly selects the correct bracket)
* Applied the before 65 rebate of 13 to all calculations

Updated to version 1.6
* Fixed a mistake on the first third RA tax (Thank you KalMaverick!)

Updated to version 1.5
* Added Ability to limit RA contribution to values other than 15%
* Applied latest SARS tax tables
* Tax on RA 2/3's and Equities was adjusted to be more leniant (assuming 10% draw down)
* Tax bracket changes due to salary increase should also be handled better now
*

Updated to version 1.4
* Fixed RA SARS return when only small portion of Salary is paid

Updated to version 1.3
fixed major mistake on "TFSA then RA" worksheet, plus corrected RA lump sum tax as per latest SARS document

Updated to version 1.2
Fixed for small salaries (eg not capable of reaching RA of 15%)
fixed for big salaries (tax return of greater than 30k was messing up TFSA allocation)
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Hamster on May 26, 2015, 07:15:26 am
Nice
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Patrick on May 26, 2015, 08:49:26 am
Phew that's quite a serious spreadsheet. I'm trying to get my head around the numbers. I can't figure out why the TFSA then RA beats the RA then TFSA when you're in the 41% tax bracket, with performance being the same. Is it perhaps that even though you've got more cash available going the RA route, this isn't invested, while the post investment tax savings in the TFSA is, making it look like the TFSA has you better off financially?

I'm trying to think of a way your spreadsheet could illustrate this. Could ou either add a Total tax paid column, or perhaps invest the tax savings into norml investments when contributing to the RA.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on May 26, 2015, 09:12:21 am
Phew that's quite a serious spreadsheet. I'm trying to get my head around the numbers. I can't figure out why the TFSA then RA beats the RA then TFSA when you're in the 41% tax bracket, with performance being the same. Is it perhaps that even though you've got more cash available going the RA route, this isn't invested, while the post investment tax savings in the TFSA is, making it look like the TFSA has you better off financially?

I'm trying to think of a way your spreadsheet could illustrate this. Could ou either add a Total tax paid column, or perhaps invest the tax savings into norml investments when contributing to the RA.

have to agree with you - its quite an eye opener if you punch in different numbers ( i just updated the OP with a new version)

On lower salary brackets "TFSA >RA>Equities", is winning, especially if you only invest a small % of your salary.
once you go a bit higher and invest more than a third, "TFSA >Equities" starts to win.
my personal feeling (as you have posted a few times), is to max TFSA and RA's then go into equities. It gives you a safeguard , which is also quite important.

*Update version 1.3*
Think i discovered the gremlin, well part of it at least. found a broken formula on the TFSA>RA and RA>TFSA worksheets. OP is updated
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: [email protected] on May 26, 2015, 07:48:25 pm
Very impressive.  I've often though of doing the same, but i never got around to it.  So well done.

My first thought was how you treated the eventual taxation of a RA, and i didn't see it your spreadsheet.  But then saw your differential Gain rates, so i'd guess it was there.  Any chance you can confirm the logic  around your gain rates, and how you considered the eventual taxation of a RA. 

Regards
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on May 26, 2015, 08:04:33 pm
Very impressive.  I've often though of doing the same, but i never got around to it.  So well done.

My first thought was how you treated the eventual taxation of a RA, and i didn't see it your spreadsheet.  But then saw your differential Gain rates, so i'd guess it was there.  Any chance you can confirm the logic  around your gain rates, and how you considered the eventual taxation of a RA. 

Regards

thank you thank you :) - loved the challenge ;)

wrt to the taxation -

*available cash is pushed into each vehicle until the cap is reached (30k for TFSA and 15% for RA), after which the remainder is pushed into the Equity section. thus each gain is calculated separately, and thus i could apply tax to each
*RA tax was simplified as per the SARS page : First third is taxed ( <300,000 (18%) , < 600,000(54k +27%) and >600,000(135k+36%)) with different levels each. The other two thirds i taxed at your nominal tax rate as configured.
*all things equal - Equities is also taxed on capital gains based on the nominal tax rate provided.


i know taxation will be a little bit different at retirement age, but my vision for this calculator was to provide a ten year overview.


Hope this answers your question?
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on May 27, 2015, 04:56:32 pm
new version uploaded

updated some tax calculations - thus affecting results quite drastically

*edit* - tax fixes made a big difference - screenshots no longer accurate :)
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Patrick on May 27, 2015, 06:29:18 pm
Note those numbers make sense, great work. You're right though, etf's are likely to outperform RA's as RA's can only hold 75% equities, and will most likely have far higher fees too.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on May 27, 2015, 08:36:35 pm
Note those numbers make sense, great work. You're right though, etf's are likely to outperform RA's as RA's can only hold 75% equities, and will most likely have far higher fees too.

but then there is a case like this.. RA wins even if it performs 5 to 7% lower

(http://imgbin.org/images/thumbs/ext23987.png) (http://imgbin.org/index.php?page=image&id=23987)
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 03, 2015, 06:15:00 am
OP is updated. Fixed another tax bug :/
SARS ....just eish.

RA first is looking better though.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 07, 2015, 03:02:52 pm
Update to version 2.0
* major changes in tax tables - applied 10% increase on tax tables per year (current average since 2005) - this is configurable under inputs...
* added 20 and 30 year results sets as well
* 10 year result is based on tax for a ten year "draw" (year 15 tax is applied)
* 20 year result is based on tax for a twenty year "draw" (year 30 tax is applied)
* 30 year result is based on tax for a thirty year draw (year 45 tax is applied)
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 10, 2015, 11:59:00 am
Update to version 3.0
Major functionality update - basically it is now a South African Financial Independence calculator. Most Inputs is customizable, including Age, Inflation, and current portfolio values. System will output 3 periods between your current age and 55 years, allowing you to see capital growth.


Can someone maybe recommend some financial /retirement draw down calculators out there? i would like to run simulations to compare values.

For now it looks quite accurate  - Any comments is welcome.


Basic operation :
**Note1  If draw down pre 55  failed, salary will be shown as R0.00
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Nivek on June 10, 2015, 12:43:51 pm
Excellent work  :TU: I'm trying to run the following figures:
Yearly pay (Before tax)   R 1 000 000,00
Pay increase per year(%)   6
Gain rate RA (%)   10
Gain rate TFSA (%)   12
Gain rate Equity (%)   12
"Invest perc of income
(after tax) (%)"   50
Limit RA contrib cap to (%)   15
Current Age (years)   40
Current RA portfolio   R 0,00
Current TFSA portfolio   R 30 000,00
Current Equity Portfolio   R 5 000 000,00

Why does it give me the results after 3.8, 7.5, 11.3 and 15 years?

If it's accurate, it looks like in this scenario you'd be FI somewhere between 3.8 and 7.5 years.

One thing which I think may be a mistake, using the figures above it ranks max equity higher than max TFSA then equity even though the gain rates are the same.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 10, 2015, 12:58:51 pm
Excellent work  :TU: I'm trying to run the following figures:
Yearly pay (Before tax)   R 1 000 000,00
Pay increase per year(%)   6
Gain rate RA (%)   10
Gain rate TFSA (%)   12
Gain rate Equity (%)   12
"Invest perc of income
(after tax) (%)"   50
Limit RA contrib cap to (%)   15
Current Age (years)   40
Current RA portfolio   R 0,00
Current TFSA portfolio   R 30 000,00
Current Equity Portfolio   R 5 000 000,00

Why does it give me the results after 3.8, 7.5, 11.3 and 15 years?

If it's accurate, it looks like in this scenario you'd be FI somewhere between 3.8 and 7.5 years.

One thing which I think may be a mistake, using the figures above it ranks max equity higher than max TFSA then equity even though the gain rates are the same.

*sulks at salary - i will be there in a few years i hope*
i have divided the difference between current age and 55 in 4 parts, thus resulting in (55-40)/4 = 3.8 years per result. you are welcome to modify the constants(analyze after year)  to 4, 5, 6 and 7 to see a more accurate result for those years. (year 6 seems to be the extreme limit with 1K surplus)


not sure if i follow your last statement? Inputting your values shows that RA/TFSA/Equity is higher? (112k surplus vs 67k maxing equity only after 7.5 years)
excel will highlight the bigger value green :)



Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Nivek on June 10, 2015, 02:45:30 pm
*sulks at salary - i will be there in a few years i hope*

i have divided the difference between current age and 55 in 4 parts, thus resulting in (55-40)/4 = 3.8 years per result. you are welcome to modify the constants(analyze after year)  to 4, 5, 6 and 7 to see a more accurate result for those years. (year 6 seems to be the extreme limit with 1K surplus)

not sure if i follow your last statement? Inputting your values shows that RA/TFSA/Equity is higher? (112k surplus vs 67k maxing equity only after 7.5 years)
excel will highlight the bigger value green :)
I rounded down a little  >:D

Here's the query:

Results(FI After 15 years)            
Yearly Salary   "Max TFSA then Equity
(no RA)"   Max Equity only   Max TFSA then RA then Equity   Max RA then TFSA then Equity
RA (15%)   R 0,00   R 0,00   R 359 365,37   R 483 959,65
TFSA   R 152 760,08   R 12 603,98   R 153 099,74   R 147 385,83
Equity   R 5 952 262,70   R 6 211 969,35   R 5 380 981,96   R 5 280 140,85
Required   R 1 217 552,98   R 1 217 552,98   R 1 217 552,98   R 1 217 552,98
Total surplus (after tax)   R 4 498 042,92   R 4 597 112,03   R 4 206 445,16   R 4 185 992,94
"Total yearly surplus
(Inflation adjusted)"   R 1 746 296,55   R 1 784 758,62   R 1 633 088,17   R 1 625 147,90

As you can see, the total surplus is highest on equity only. I would have thought it would be highest on RA, TFSA then equity but it's lowest there.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 10, 2015, 03:20:17 pm
*sulks at salary - i will be there in a few years i hope*

i have divided the difference between current age and 55 in 4 parts, thus resulting in (55-40)/4 = 3.8 years per result. you are welcome to modify the constants(analyze after year)  to 4, 5, 6 and 7 to see a more accurate result for those years. (year 6 seems to be the extreme limit with 1K surplus)

not sure if i follow your last statement? Inputting your values shows that RA/TFSA/Equity is higher? (112k surplus vs 67k maxing equity only after 7.5 years)
excel will highlight the bigger value green :)
I rounded down a little  >:D

Here's the query:

Results(FI After 15 years)            
Yearly Salary   "Max TFSA then Equity
(no RA)"   Max Equity only   Max TFSA then RA then Equity   Max RA then TFSA then Equity
RA (15%)   R 0,00   R 0,00   R 359 365,37   R 483 959,65
TFSA   R 152 760,08   R 12 603,98   R 153 099,74   R 147 385,83
Equity   R 5 952 262,70   R 6 211 969,35   R 5 380 981,96   R 5 280 140,85
Required   R 1 217 552,98   R 1 217 552,98   R 1 217 552,98   R 1 217 552,98
Total surplus (after tax)   R 4 498 042,92   R 4 597 112,03   R 4 206 445,16   R 4 185 992,94
"Total yearly surplus
(Inflation adjusted)"   R 1 746 296,55   R 1 784 758,62   R 1 633 088,17   R 1 625 147,90

As you can see, the total surplus is highest on equity only. I would have thought it would be highest on RA, TFSA then equity but it's lowest there.

*edit* - are you still running on your original inputs(1mil 50% invested with 5mil in equities)? - My results looks much less than yours :/

not a major difference between them, but in 15 years you will be 55 - thus calculation is quite easy.

@55 years

Equity only will give 47.5mil plus the 0.1mil from the TFSA - possible to draw 3mil, and only taxed on 800k of that(-90k) = get 3Mil out

max TFSA then RA then equity will give 39.6mil Equity + 2.3Mil TFSA + 9.7Mil RA = 51.7 Mil. Possible to draw 3.1Mil, but you will be taxed double(-188k) = 2.9Mil out.

Row 56 on the worksheets gives the salaries ( and J52 the tax that is paid on that salary)

remember that equities is taxed on a third of the capital gains 3mil draw = 2.5mil capital gain = 805k tax - and by that stage 805k is in the 26% bracket with an even larger rebate.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 12, 2015, 10:13:27 pm
Update to version 3.1
* Expanded draw down function - now select the most rewarding draw path (selecting between Equities/ TFSA and also splitting a third of the RA at 55 into equities or keeping it in tact)
* Added a option to block TFSA limits from increasing every  year(configurable under constants)
* fixed some calculations fro extreme cases(eg paying RA and TFSA off in one month)
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 15, 2015, 08:12:22 pm
Update to version 3.2
*Tax tables was "ahead" with a year - Corrected
*Improved the Salary prediction required at Financial Independence - should now be much more accurate(current expenses is inflation adjusted with buffer added, then reverse tax is applied to determine the before tax salary)
*Added a Spending buffer which will be added to your salary after FI (use full if you want to go on that yearly travel trip :p
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 16, 2015, 10:54:53 pm
Update to version 3.3
*discovered and fixed a major miscalculation with regards to savings (was previously matching salary increases, while it should be based on salary minus inflation adjusted expenses.
Bonus part is that this increases savings, and thus will result in a quicker FI.
*added more accurate descriptions on the summary page
*added a check to show a salary shortfall (eg your increases is below inflation, which then results in running into a salary that cant carry your expenses.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 20, 2015, 04:32:38 pm
Update to version 3.4
*Adjusted internal calculations ,based on paying current loans (eg home /car) - expenses that wont remain into financial independence for long - configurable(change to zero if you plan to keep paying off your home/car up to 95 :P)
*improved the results screen ,showing more details as to what is happening, including your salary at FI.

only challenges that still remains :
* Drawing a salary after FI is calculated, mostly, based on drawing your tax every month. this wont be the case, as you will draw a once off SARS amount after tax claims are due. (thus additional savings will happen especially if you pay large tax amounts, since your tax owed will grow during the year in your portfolio)

* Homeloan and car expenses is removed after the payment period is complete - not sure how to tackle the scenario where you want to continue wasting money into the latest and fastest clown-mobiles every few years. for now, please configure the buffer to allow for any expenses that you will keep/add after FI.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Patrick on June 22, 2015, 08:31:24 am
Excellent, it says I'm good to go. What the hell am I still doing in the office then?  :D
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 22, 2015, 08:41:54 am
Excellent, it says I'm good to go. What the hell am I still doing in the office then?  :D
:wall:
that mystery eludes me - :P - maybe you are sponsoring the next nkandla?

if memory serves you are saving up for a sail boat trip around the world?
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Patrick on June 22, 2015, 09:30:07 am
if memory serves you are saving up for a sail boat trip around the world?
Yes, I definitely prefer reason 2! I'll either need to work a few more years, or get a smaller boat...

(http://www.yachtsaleswest.com/ysw/wp-content/uploads/2015/04/fountaine-pajot_mahe-36-evolution_02.jpg)
Not mine, yet...
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 22, 2015, 10:09:36 am
if memory serves you are saving up for a sail boat trip around the world?
Yes, I definitely prefer reason 2! I'll either need to work a few more years, or get a smaller boat...

(http://www.yachtsaleswest.com/ysw/wp-content/uploads/2015/04/fountaine-pajot_mahe-36-evolution_02.jpg)
Not mine, yet...

yup - definitely a dream come true that!

Makes me wonder if i should add a once off bonus at FI as well :/. What other scenarios will require you to have a big lump expense on retirement?
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on June 28, 2015, 10:33:48 pm
Update to version 3.5
* Added a once off bonus at FI - configurable. This is to allow a lumpsum draw to happen at FI, mostly for big expenses (paying off house/car, etc)
* Fixed tax tables between FI age and 55- changed to CGT tax(was previously normal tax)
* Improved readability on summary screen
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on July 07, 2015, 11:03:00 am
Update to version 3.6 (7/7/2015)
* fixed a bug that slipped in when moving some of the reference tables around (affected TFSA contributions)
* adjusted tax table inflation averages a little.


Can someone maybe recommend a source or two of "real" inflation? i know it is a highly contested topic, but i will rather have the calculator default to a more safe value if needed.
the current 6% as reported does not seem to be enough

in any event, you can edit the inflation on the inputs/constants (make it a % or two higher), as per your own feeling. planning for a higher inflation will help with generating a sufficient investment buffer/surplus as well.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Nivek on July 07, 2015, 01:39:30 pm
Big mac index?
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: [email protected] on July 07, 2015, 08:19:20 pm

Can someone maybe recommend a source or two of "real" inflation? i know it is a highly contested topic, but i will rather have the calculator default to a more safe value if needed.
the current 6% as reported does not seem to be enough


CPI history is on the reserve bank website
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on November 19, 2015, 04:05:46 pm
Update to version 3.7(19/11/2015)
* improved a formula to be more accurate for monthly interest(This was workbook wide, thus affects all totals)
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Nios on November 26, 2015, 07:54:15 pm
What a great piece of work erwintr. Thanks very much for sharing.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on November 26, 2015, 09:27:55 pm
What a great piece of work erwintr. Thanks very much for sharing.

only a pleasure :)


i know it can be a bit overwhelming, but read through the comments where needed for clarifications. Any questions is welcome though.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Nios on November 29, 2015, 06:48:46 pm
Overwhelming is an understatement  ???  :D

I'm trying to figure a few things out, if you don't mind me asking?
-Where do you see the values of each vehicle after the various FI periods invested?
-When I input 100% of salary, it messes up the calculations somewhere and doesn't output the data in the 4 Results(FI test after X Years) as per attached. You know why?

Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on November 29, 2015, 08:46:32 pm
Overwhelming is an understatement  ???  :D

I'm trying to figure a few things out, if you don't mind me asking?
-Where do you see the values of each vehicle after the various FI periods invested?
-When I input 100% of salary, it messes up the calculations somewhere and doesn't output the data in the 4 Results(FI test after X Years) as per attached. You know why?


never  a bad question :).
seems like you uncovered a possible bug added recently, i will try and correct it when time allows. Trying your values, the Div/0 error comes in when you enter the homeloan and car values. For now make it R100 per month for a year -  it should be basically ignored for most of the calculations.


Quote
Where do you see the values of each vehicle after the various FI periods invested?
If you want to see the value of the investment at certain periods, you will need to go to the worksheets - unfortunately the summary page shows the salary that can be drawn from that investment ( a further step down the line)

in all sheets this is located at a row labelled current

F:29,G:29,H:29 for the first period (after 5 years in your example - seems like i left it highlighted yellow just below , so should be easy to locate)
F:78,G:78,H:78 for the second period (10 years)
F:127,G:127,H:127 for the third period (14 years)
F:176,G:176,H:176 for the last period  (19 years)


Quote
When I input 100% of salary, it messes up the calculations
a note on this - the calculator takes the reverse of the 100% to determine your current living expenses ( expenses + savings = salary). Thus savings will/should always be less than 100% ( unless you got a big heritage somewhere :P )

sample output follows :
this takes savings as 90% ( thus expenses per month is +-R5193 initially)
(http://i.imgur.com/DRpaAss.jpg)
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: Nios on November 29, 2015, 09:58:07 pm
Have I said how cool this spread sheet is again? Been wanting something like this for ages, and too dumb with excel to be able to do it myself  :-[

Quote
never  a bad question :).
seems like you uncovered a possible bug added recently, i will try and correct it when time allows. Trying your values, the Div/0 error comes in when you enter the homeloan and car values. For now make it R100 per month for a year -  it should be basically ignored for most of the calculations.
Sorry to be tearing it apart :). Ok, I changed the homeloan payment to 1 cent for the time being :)

Quote
If you want to see the value of the investment at certain periods, you will need to go to the worksheets - unfortunately the summary page shows the salary that can be drawn from that investment ( a further step down the line)

in all sheets this is located at a row labelled current

F:29,G:29,H:29 for the first period (after 5 years in your example - seems like i left it highlighted yellow just below , so should be easy to locate)
F:78,G:78,H:78 for the second period (10 years)
F:127,G:127,H:127 for the third period (14 years)
F:176,G:176,H:176 for the last period  (19 years)

Thanks. Adding the value of the investment on the summary page would be way cool  ;D
Is there somewhere in the sheet to see what the monthly contributions to RA, TFSA(R2500) and Equity contributions are monthly/annually till FI is reached based on the inputs? This just to verify how close this is to reality.

Quote
a note on this - the calculator takes the reverse of the 100% to determine your current living expenses ( expenses + savings = salary). Thus savings will/should always be less than 100% ( unless you got a big heritage somewhere :P )

sample output follows :
this takes savings as 90% ( thus expenses per month is +-R5193 initially)

Hmm, ok I understand that & why you did it that way, and that it's a way to reverse engineer using those  numbers because who in reality can save 100% of their salary. But would you somehow be able make it work without the "reverse engineering"only when you input 100%, for someone that wants to be able to commit 100% or through other means can do so(lets pretend they can live without touching their earned salary during this growth phase  8)) but with an expectation that after FI is reached they want that income inputted in "Monthly Spending buffer after FI" without it affecting the calculation? Hope that makes sense? You see the idea is the 900,000k "Once off spending buffer at FI" is to settle a rental property for instance that will then provide another portion of income, hence the lower "Monthly Spending buffer after FI" figure :) which will complement this income to make up for the "real" requirement once FI is reached.
Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on November 29, 2015, 11:33:08 pm

Quote
Have I said how cool this spread sheet is again? Been wanting something like this for ages, and too dumb with excel to be able to do it myself  :-[
Lol - happy it is helping you :) - many ideas went into it - hoping to keep it as close to real life scenario's as possible


Quote
Thanks. Adding the value of the investment on the summary page would be way cool  ;D
Is there somewhere in the sheet to see what the monthly contributions to RA, TFSA(R2500) and Equity contributions are monthly/annually till FI is reached based on the inputs? This just to verify how close this is to reality.

hmmmm  - this goes much deeper into the inner workings of the sheet. this does not stay constant, especially considering the different investment strategies.

(my whole idea behind the calculator was due to RA's having lower returns than equities- even on  monthly basis. Thus  pushing more cash into a 20% equity return, does it have a better financial gain than sending cash into your RA first?
anyhow - if you really like looking at the numbers, go to each sheet, and look at Row 283 onwards. This gives the value of the investments and how much is added to each ( columns F , I  and K) ,and the resulting value after that month's interest (columns G J and L).

Dont think that can be summarized (expenses inflation vs salary increases is not supposed to stay the same which is why i seperated it on the inputs).
eg
year one

expenses 5000
savings 15000
salary 20000

year two
expenses 5000 + inflation (6.1%) = 5305
savings  = salary minus expenses = 16695 = 11.3% higher
salary 20000 (+10% increase) = 22000


Quote
Hmm, ok I understand that & why you did it that way, and that it's a way to reverse engineer using those  numbers because who in reality can save 100% of their salary. But would you somehow be able make it work without the "reverse engineering"only when you input 100%, for someone that wants to be able to commit 100% or through other means can do so(lets pretend they can live without touching their earned salary during this growth phase  8)) but with an expectation that after FI is reached they want that income inputted in "Monthly Spending buffer after FI" without it affecting the calculation? Hope that makes sense? You see the idea is the 900,000k "Once off spending buffer at FI" is to settle a rental property for instance that will then provide another portion of income, hence the lower "Monthly Spending buffer after FI" figure :) which will complement this income to make up for the "real" requirement once FI is reached.


ah hmmmm. yes investment properties is a completely different ball game. unfortunately, my initial thought is that in the current state the calculator cant cater for this - too many variables ( rental income in x years will be ??. Rental expenses in x years... etc etc.)  - Tax to be paid due to rental income in x years = ??.

dont think its feasible to reverse engineer current input values(my personal feeling from rental properties ( Thank you Patrick :P), is to avoid them like the plague. Rather choose a REIT fund. but yes that's just me.

Thus my best recommendation then for now is to leave the 900k once of buffer in equities ( eg change buffer to 0). The calculator will then use the cash available in equities to show you salaries possible from that amount.


Title: Re: TFSA vs RA vs Satrix - Calculator
Post by: erwintwr on November 30, 2015, 07:18:54 pm
Quote
Hmm, ok I understand that & why you did it that way, and that it's a way to reverse engineer using those  numbers because who in reality can save 100% of their salary. But would you somehow be able make it work without the "reverse engineering"only when you input 100%, for someone that wants to be able to commit 100% or through other means can do so(lets pretend they can live without touching their earned salary during this growth phase  8)) but with an expectation that after FI is reached they want that income inputted in "Monthly Spending buffer after FI" without it affecting the calculation? Hope that makes sense? You see the idea is the 900,000k "Once off spending buffer at FI" is to settle a rental property for instance that will then provide another portion of income, hence the lower "Monthly Spending buffer after FI" figure :) which will complement this income to make up for the "real" requirement once FI is reached.


Please excuse my ramblings above - Spend a long a day flying yesterday, thus a few brainfarts later resulted in the above :P

taking your numbers through the calculator again (avoiding the 100%) :

Inputs :

(http://i.imgur.com/hXUlFII.jpg)



Outputs:



(http://i.imgur.com/TXFsHTg.jpg)


Thus you should be able to safely achieve FI after 3 years ( or even less due to rental income). at 55 you will have R5000 + R2594 available to spend + rental income.
Keep in mind that the calculator assumes your only expense is R5000 and nothing more (Inflation adjusted obviously)

i would however recommend that you push inflation to a more comfortable value (maybe 8%?), but even then rental income should provide a nice buffer.

If you need to refer to any other numbers please feel free to ask/recommend :)






Title: Re: Financial indepence - Calculator ( Covering TFSA / Equity and Retirement Anuity)
Post by: erwintwr on December 03, 2015, 05:43:51 pm
Update to version 3.8(03/12/2015)
* Improved summary page after some recommendations (added portfolio values at the FI year, so that you can have a visual of the values)
* Added a field to allow changing to the new RA tax benefit limit - soon to be implemented in 2016
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: erwintwr on December 06, 2015, 12:49:30 pm
Update to version 4.0(6/12/2015)
* Added functionality to add retirement annuity deduction as per payslip. (caution  - this is kept constant throughout the calculator and adjusted as per your pay increases. Any other contributions towards an RA will be after tax, and tax returns will be re-invested).
Although some checks is in place, please double check that your RA deduction is not greater than the allowed SARS Tax benefit.
/* this is quite a major update - if anything strange is noted on the results, please provide feedback

* Summary screen was updated -with a total Portfolio value, and some readability/formatting.
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: Samurai on December 06, 2015, 02:15:32 pm
I do admire the effort you placed into creating such a spreadsheet/calculator. Do you have a separate calc for your equity portfolio and its growth prospects including dividend reinvestments?
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: erwintwr on December 06, 2015, 02:46:27 pm
I do admire the effort you placed into creating such a spreadsheet/calculator. Do you have a separate calc for your equity portfolio and its growth prospects including dividend reinvestments?


hmmm wont call this a calculator as such ( although i have tried implementing a scoring system based on different "rules"  (expand the top 20 rows of the sheet to see them).

Have only been running it for the past year, thus the one question still nagging in my mind is how will i do re balancing should the need arise.
Shares highlighted green is the ones i currently have in my portfolio. I try to update the fact sheet data every few months, but this one is about  three weeks old so should be more than ok.

all share performance shown includes dividend reinvestment.

Hope it helps?

https://www.dropbox.com/s/frq4dxlj00nrfeu/20151119%20share%20long%20term%20comparison%20ranking%20v3.xlsx?dl=0




Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: erwintwr on March 08, 2016, 04:31:51 pm
Update to version 4.1(8/03/2016)
* Added the latest tax tables (note that tax tables for future years is automatically adjusted based on a 10 year average.)
* improved the CGT formulae to allow adapting to future tax changes (latest tax change moved the previous 33% up to 40%)

*pending - still to Add the RA contribution limit ( there is an annual amount after which the tax benefit doesn't apply on a RA)
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: Gerlu on May 04, 2017, 12:40:52 pm
Such a fantastic tool!  Any update on this since v4.1? Holding thumbs :D
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: erwintwr on May 04, 2017, 01:35:24 pm
Such a fantastic tool!  Any update on this since v4.1? Holding thumbs :D

now i have a nice headache trying to figure out all my formulae of 3 years ago  - guess i was really bored then :D


updated tax tables as much as possible, but had to skip one of the "brackets" due to a new bracket introduced. shouldn't be too much of a problem though.
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: mike.h on August 31, 2018, 09:02:21 pm
This is very cool, Erwin! Thanks a lot for providing it!

Playing around with it a bit to try and understand everything. Is it a bug that scenario 4 is sitting on 33,000 for the TFSA while scenario 1 and 3 are sitting on 66,000?

https://imgur.com/FODkoZN
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: erwintwr on September 01, 2018, 10:11:36 am
This is very cool, Erwin! Thanks a lot for providing it!

Playing around with it a bit to try and understand everything. Is it a bug that scenario 4 is sitting on 33,000 for the TFSA while scenario 1 and 3 are sitting on 66,000?

https://imgur.com/FODkoZN

its a pleasure :) - glad it is still helping out there
you have a few really odd entries there lol.


i don't believe it is a bug, i checked through the monthly calculations and seems to match. but yes - if you manage to earn a 5 bar salary, without having anything invested then there is bigger problems afloat :P
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: mike.h on September 01, 2018, 01:58:34 pm
its a pleasure :) - glad it is still helping out there
you have a few really odd entries there lol.


i don't believe it is a bug, i checked through the monthly calculations and seems to match. but yes - if you manage to earn a 5 bar salary, without having anything invested then there is bigger problems afloat :P

lol yeah I don't have the "problem" of a 5 mil salary and no investments :D

Why do scenario 3 and 4 arrive at different values? With 5 mil salary, my intuition is that the 27.5% RA contribution and the R33,000 TFSA contribution will both "fit" into the salary available for investment. In both scenarios, I had thought the final number would be identical as both RA and TFSA get "saturated" by the massive salary (i.e. it doesn't matter if you go into TFSA first or RA first, as there is "plenty" of salary to go around and fill up both).

Actually, I think I might know the answer since you mentioned the monthly calculations (had a closer look at them). With scenario 3 (TFSA first) the TFSA contribution is done in the first month of each year, whereas with scenario 4 (RA first) it goes in a few months later.

Could that still be a slight error then? It seems the first month of contributions are taken into account at the end of the 1 year period (in the image). I.e. 1 year is actually considering 13 months, and therefore catches an extra 33,000 in the "RA first" scenario. In the Max TFSA then RA then equity sheet
G29=OFFSET($J$283,D3*12,0,1,1) - this should maybe be
G29=OFFSET($J$283,D3*12-1,0,1,1)

I am still wrapping my head around all this stuff. I see you have a cool optimization in that monthly section where the SARS return from RA contributions gets plugged directly back into the next year's RA – smart! I am learning a lot, thanks again! ;D
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: erwintwr on September 01, 2018, 08:25:49 pm
its a pleasure :) - glad it is still helping out there
you have a few really odd entries there lol.


i don't believe it is a bug, i checked through the monthly calculations and seems to match. but yes - if you manage to earn a 5 bar salary, without having anything invested then there is bigger problems afloat :P

lol yeah I don't have the "problem" of a 5 mil salary and no investments :D

Why do scenario 3 and 4 arrive at different values? With 5 mil salary, my intuition is that the 27.5% RA contribution and the R33,000 TFSA contribution will both "fit" into the salary available for investment. In both scenarios, I had thought the final number would be identical as both RA and TFSA get "saturated" by the massive salary (i.e. it doesn't matter if you go into TFSA first or RA first, as there is "plenty" of salary to go around and fill up both).

Actually, I think I might know the answer since you mentioned the monthly calculations (had a closer look at them). With scenario 3 (TFSA first) the TFSA contribution is done in the first month of each year, whereas with scenario 4 (RA first) it goes in a few months later.

Could that still be a slight error then? It seems the first month of contributions are taken into account at the end of the 1 year period (in the image). I.e. 1 year is actually considering 13 months, and therefore catches an extra 33,000 in the "RA first" scenario. In the Max TFSA then RA then equity sheet
G29=OFFSET($J$283,D3*12,0,1,1) - this should maybe be
G29=OFFSET($J$283,D3*12-1,0,1,1)

I am still wrapping my head around all this stuff. I see you have a cool optimization in that monthly section where the SARS return from RA contributions gets plugged directly back into the next year's RA – smart! I am learning a lot, thanks again! ;D


re-looking at all these formulas really made me wonder where did i find the time :O

in the specific test data you entered, the above corrections might be more accurate, but then lots of other dependencies is affected (for example the RA tax return).
looking at it now, it is a bug, but the work required to fix it seems daunting... i might sleep on it and feel intrigued to fix it tomorrow:P

for now, think it will be safe to stick with the calculated values, and "work" one month longer eg 1 year +1 month :)

thx for testing in such detail!



Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: mike.h on September 02, 2018, 01:13:58 am
re-looking at all these formulas really made me wonder where did i find the time :O

in the specific test data you entered, the above corrections might be more accurate, but then lots of other dependencies is affected (for example the RA tax return).
looking at it now, it is a bug, but the work required to fix it seems daunting... i might sleep on it and feel intrigued to fix it tomorrow:P

for now, think it will be safe to stick with the calculated values, and "work" one month longer eg 1 year +1 month :)

thx for testing in such detail!

A little while after I posted, I also noticed the tax return thing, so I'm glad we're on the same page there. It does look like it would be difficult to fix, and in a longer term scenario of ~25 years (which is what I'm most interested in) it will hardly matter.

After plugging in some conservative numbers, I'm feeling a lot more comfortable now about my prospects in retirement! ;D
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: erwintwr on September 02, 2018, 10:39:32 am
re-looking at all these formulas really made me wonder where did i find the time :O

in the specific test data you entered, the above corrections might be more accurate, but then lots of other dependencies is affected (for example the RA tax return).
looking at it now, it is a bug, but the work required to fix it seems daunting... i might sleep on it and feel intrigued to fix it tomorrow:P

for now, think it will be safe to stick with the calculated values, and "work" one month longer eg 1 year +1 month :)

thx for testing in such detail!

A little while after I posted, I also noticed the tax return thing, so I'm glad we're on the same page there. It does look like it would be difficult to fix, and in a longer term scenario of ~25 years (which is what I'm most interested in) it will hardly matter.

After plugging in some conservative numbers, I'm feeling a lot more comfortable now about my prospects in retirement! ;D

always glad to hear :)
now just to get equities etc to start growing a bit faster :P
Title: Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
Post by: francismichael on September 27, 2018, 09:58:31 am
Thanks, this is amazing ! I know on the Fat Wallet show they are waiting for someone to do this, but here it is. Fantastic work.

I have a rental property which I wish to payoff before retirement that can act as part of my income, would be great to include this. (Yes I know the stats re buy-to-let, but this is a convenience thing as well, to have a place in the city if I want to move out to farm at some stage)

Thanks again for the hard work and the updates!