Whole Of Life Finances SpreadSheet
#16
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
Originally Posted by JulesandChris
Chances are that any real income would cease when you retire anyway, so the most obvious plan would involve splitting your mortage over the number of years you have left before retirement anyway?
As I don't have a mortgage I am placing figures in on a best guess if I did join the home owners guild. What is the mortgage principle? Why do you set mortgage years to 1? I'm obviously new to this.
As I don't have a mortgage I am placing figures in on a best guess if I did join the home owners guild. What is the mortgage principle? Why do you set mortgage years to 1? I'm obviously new to this.
The mortgage principle is the outstanding balance of what you owe to the borrower. If you have no current mortgage, take the price of a house you can afford and deduct the deposit you would make to give you the outstanding balance / mortgage principal.
The mortgage years are re-set to 1 when the Solve button is clicked to avoid mathematical errors - it is a technical work around. Solver then changes the mortgage years by whole number amounts until the tax, and consequentially the required income, is minimized.
Hopefully someone with spreadsheet skills and willingness will think of or suggest a better solution.
#17
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
Originally Posted by MrsDagboy
Ok I give up, too hard for me, I think we'll just keep Dagboy working for another few years & stick some money under the mattress .
#18
Rocket Scientist
Joined: Aug 2003
Location: Dreamland AKA Brisbane which is a different country to the UK
Posts: 6,911
Re: Whole Of Life Finances SpreadSheet
Originally Posted by Quinkana
The mortgage principle is the outstanding balance of what you owe to the borrower. If you have no current mortgage, take the price of a house you can afford and deduct the deposit you would make to give you the outstanding balance / mortgage principal.
#19
Rocket Scientist
Joined: Aug 2003
Location: Dreamland AKA Brisbane which is a different country to the UK
Posts: 6,911
Re: Whole Of Life Finances SpreadSheet
Originally Posted by Quinkana
The model does show how hard it is to achieve even what most would think of as a very modest financial outcome at retirement. The consequences of falling off the "golden path" are hard too.
True, I can only work out what I think is best, Im hoping that maybe he can retire at 50 (ie 10 years time), kids will be left school & Im hoping our super & investments (including our own home which we will probably sell sometime in the 10-15 years time range) will be enough to be able to afford somewhere nice to live & a comfortable retirement.
But to quote someone famous "The best laid plans of mice & men go astray" ....
#20
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
Originally Posted by MrsDagboy
Thats fine if you dont own a house & dont have a mortgage, but what if you do own a house mortgage free? or 2? Or 3? Do you then put in 0 values?
Own more houses - it rapidly becomes very difficult to model - but maybe there is a bright spark out there with the interest and ability to incorporate it in the model.
I suggest sticking to the simple point of view - that investment in additional houses will just maintain the value of your equity, so put in Current Investable Worth the amount of your equity in those other houses. If the houses do better than maintain your equity, you will be happy.
#21
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
Originally Posted by MrsDagboy
True, I can only work out what I think is best, Im hoping that maybe he can retire at 50 (ie 10 years time), kids will be left school & Im hoping our super & investments (including our own home which we will probably sell sometime in the 10-15 years time range) will be enough to be able to afford somewhere nice to live & a comfortable retirement.
But to quote someone famous "The best laid plans of mice & men go astray" ....
But to quote someone famous "The best laid plans of mice & men go astray" ....
#22
Guest
Posts: n/a
Re: Whole Of Life Finances SpreadSheet
Originally Posted by MrsDagboy
Ok I give up, too hard for me, I think we'll just keep Dagboy working for another few years & stick some money under the mattress .
#23
Rocket Scientist
Joined: Aug 2003
Location: Dreamland AKA Brisbane which is a different country to the UK
Posts: 6,911
Re: Whole Of Life Finances SpreadSheet
Originally Posted by ABCDiamond
I just looked at it too, and decided it would be easier to "make" the $1M required then work out why, especially if I start a cleaning business, and ensure we clean up "under" the mattresses
Megs, thanx I'll try to work it out.
Last edited by MrsDagboy; Jan 31st 2005 at 3:26 am.
#24
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
A spreadsheet for couples, same caveats as before. Allows for different ages, spliting of debts, income and shared outcome - house without mortgage and capital for retirement investment:
#25
Re: Whole Of Life Finances SpreadSheet
Originally Posted by Quinkana
A spreadsheet for couples, same caveats as before. Allows for different ages, spliting of debts, income and shared outcome - house without mortgage and capital for retirement investment:
Does the current value of pensions come into it?
How do I factor in to it the pension I will require when I retire?
#26
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
Originally Posted by worzel
I like what this is trying to do. I understand the living expenses bit and grossing up for tax but not sure about the investible worth bit. Is the current worth cash in savings, endowments, equity in the house etc?
Does the current value of pensions come into it?
How do I factor in to it the pension I will require when I retire?
Does the current value of pensions come into it?
How do I factor in to it the pension I will require when I retire?
So the house you live in does not produce an income and should not be included in Investable Wealth. Owning it does save a portion of the equivalent rent and probably will increase in value but only if you downsize at some time.
Current value of pensions should be included in Investable Wealth. The value at retirement of a government old age pension is not specifically included in Investable Wealth but could be by calculating the Net Present Value of a OA pension as the time of retirement. You would deduct the NPV of the OA pension from the required Investable Worth at retirement.
However, this starts to become complicated - not the least because retirement is a long way off for many and the rules are likely to change a lot. Best to try to keep things simple but as realistic as possible: accuracy not precision.
I hope someone will take up the cudgel and improve the thing.
A bit-better-than-rough idea of what inputs and outputs are required to produce the required outcome is the idea behind the spreadsheet.
#27
Re: Whole Of Life Finances SpreadSheet
Originally Posted by Quinkana
I see investable worth as whatever money / capital you currently have or aim to have at retirement which is / could be invested to produce an income.
So the house you live in does not produce an income and should not be included in Investable Wealth. Owning it does save a portion of the equivalent rent and probably will increase in value but only if you downsize at some time.
Current value of pensions should be included in Investable Wealth. The value at retirement of a government old age pension is not specifically included in Investable Wealth but could be by calculating the Net Present Value of a OA pension as the time of retirement. You would deduct the NPV of the OA pension from the required Investable Worth at retirement.
However, this starts to become complicated - not the least because retirement is a long way off for many and the rules are likely to change a lot. Best to try to keep things simple but as realistic as possible: accuracy not precision.
I hope someone will take up the cudgel and improve the thing.
A bit-better-than-rough idea of what inputs and outputs are required to produce the required outcome is the idea behind the spreadsheet.
So the house you live in does not produce an income and should not be included in Investable Wealth. Owning it does save a portion of the equivalent rent and probably will increase in value but only if you downsize at some time.
Current value of pensions should be included in Investable Wealth. The value at retirement of a government old age pension is not specifically included in Investable Wealth but could be by calculating the Net Present Value of a OA pension as the time of retirement. You would deduct the NPV of the OA pension from the required Investable Worth at retirement.
However, this starts to become complicated - not the least because retirement is a long way off for many and the rules are likely to change a lot. Best to try to keep things simple but as realistic as possible: accuracy not precision.
I hope someone will take up the cudgel and improve the thing.
A bit-better-than-rough idea of what inputs and outputs are required to produce the required outcome is the idea behind the spreadsheet.
Hmmm. I follow the principal but I don't know how to estimate a required worth at retirement. You'd probably need to be an actuary to do that. But like you say, If I forget about the house value, at least I know how much to aim for as a salary to cover day to day costs. So if I assume that I have assets (pensions or otherwise) from the UK at a sensible level already then if I aim to get a job at the grossed up costs-covering figure + Super then I shouldn't be too far off.
#28
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
Originally Posted by worzel
Hmmm. I follow the principal but I don't know how to estimate a required worth at retirement. You'd probably need to be an actuary to do that. But like you say, If I forget about the house value, at least I know how much to aim for as a salary to cover day to day costs. So if I assume that I have assets (pensions or otherwise) from the UK at a sensible level already then if I aim to get a job at the grossed up costs-covering figure + Super then I shouldn't be too far off.
You can be more precise actuarially but not a lot more accurate in the instance of an individual.
Simple example: current expenses = $50k, to last 80 - 65 = 15 years; 15 * $50k = $750k; in round numbers $1M. Why $1M? Because you are a worry-wort and plan to take it out slow at first at $50k / yr and quicker later. What about rate of return, inflation? The rate of return on conservatively invested pensions and annuities generally just covers inflation.
Another: $50k / yr to last 80 - 50 = 30 years; 30 * $50k = $1.5M +/- $0.5M.
Just judge how close to the wind you are happy sailing - and take the bottom or top of the range depending if you are happy-go-lucky or not.
Current "Superannuation Guarantee" of 9% of salary package won't result in significant retirement investable wealth. Savings of around 20% are needed starting from a young age, more when older.
#29
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
OK, included a suggested minimum investable wealth at retirement calculated by multiplying current non-mortgage expenses by the minimum PVF (pension value factors - current tax office numbers). Added % savings output.
Also gave cells defined names to make it easier for people to modify the spreadsheet without affecting the code behind the "Solve" button.
Also gave cells defined names to make it easier for people to modify the spreadsheet without affecting the code behind the "Solve" button.
#30
Banned
Thread Starter
Joined: Jul 2003
Posts: 1,048
Re: Whole Of Life Finances SpreadSheet
Added "Investment Return Above Inflation" field. Use 3% as a guide to your overall investment performance. This should be a long run "whole of portfolio" rate of return. Some investments do spectacularly well and some do spectacularly badly and some (more then norm) do "sort of alright".
The bottom line is that a couple of 30 year olds with living expenses of $25,000 each, taking on a $500,000 mortgage, with no assets and wanting $1,000,000 in today's money at the retirement age of 65 years needs a minimum of about $135,000 in combined annual income over the 35 years to their retirement. $1,000,000 will give each about $25,000 annual income in retirement.
Click on the attached zip file, save to and open from your disk so you can change the numbers. Virus scan recommended.
The bottom line is that a couple of 30 year olds with living expenses of $25,000 each, taking on a $500,000 mortgage, with no assets and wanting $1,000,000 in today's money at the retirement age of 65 years needs a minimum of about $135,000 in combined annual income over the 35 years to their retirement. $1,000,000 will give each about $25,000 annual income in retirement.
Click on the attached zip file, save to and open from your disk so you can change the numbers. Virus scan recommended.