Author Topic: Financial independence - Calculator ( Covering TFSA / Equity and RA)  (Read 19970 times)

erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
Re: TFSA vs RA vs Satrix - Calculator
« Reply #30 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.

Nios

  • Full Member
  • ***
  • Posts: 124
  • Karma: +3/-1
    • View Profile
Re: TFSA vs RA vs Satrix - Calculator
« Reply #31 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?


erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
Re: TFSA vs RA vs Satrix - Calculator
« Reply #32 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)
« Last Edit: November 29, 2015, 09:10:56 pm by erwintwr »

Nios

  • Full Member
  • ***
  • Posts: 124
  • Karma: +3/-1
    • View Profile
Re: TFSA vs RA vs Satrix - Calculator
« Reply #33 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.

erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
Re: TFSA vs RA vs Satrix - Calculator
« Reply #34 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.



erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
Re: TFSA vs RA vs Satrix - Calculator
« Reply #35 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 :





Outputs:






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 :)






« Last Edit: November 30, 2015, 07:23:28 pm by erwintwr »

erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
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
« Last Edit: December 03, 2015, 07:35:10 pm by erwintwr »

erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
« Reply #37 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.

Samurai

  • Jr. Member
  • **
  • Posts: 99
  • Karma: +1/-0
    • View Profile
Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
« Reply #38 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?

erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
« Reply #39 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





erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
« Reply #40 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)

Gerlu

  • I've just arrived
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
« Reply #41 on: May 04, 2017, 12:40:52 pm »
Such a fantastic tool!  Any update on this since v4.1? Holding thumbs :D

erwintwr

  • Jr. Member
  • **
  • Posts: 88
  • Karma: +6/-0
    • View Profile
Re: Financial independence - Calculator ( Covering TFSA / Equity and RA)
« Reply #42 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.