Go Back  British Expats > Living & Moving Abroad > Australia
Reload this Page >

Whole Of Life Finances SpreadSheet

Whole Of Life Finances SpreadSheet

Old Jan 31st 2005, 1:50 am
  #16  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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.
It is assumed that in retirement you would live off a self funded pension - $1,000,000 was initially selected because runing the pension as a sinking fund (spending some capital and earnings until you croak at say 80) would probably mean a retirement income of $50,000 per year valued at today's money. ~$50,000 retirement income was selected because $50,000 was seected as an example of your current expenses; would be nice to live in retirement much as you do today.

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.
Quinkana is offline  
Old Jan 31st 2005, 1:55 am
  #17  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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 .
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.
Quinkana is offline  
Old Jan 31st 2005, 1:55 am
  #18  
Rocket Scientist
 
MrsDagboy's Avatar
 
Joined: Aug 2003
Location: Dreamland AKA Brisbane which is a different country to the UK
Posts: 6,911
MrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond repute
Default 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.
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?
MrsDagboy is offline  
Old Jan 31st 2005, 2:02 am
  #19  
Rocket Scientist
 
MrsDagboy's Avatar
 
Joined: Aug 2003
Location: Dreamland AKA Brisbane which is a different country to the UK
Posts: 6,911
MrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond repute
Default 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" ....
MrsDagboy is offline  
Old Jan 31st 2005, 2:04 am
  #20  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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 your house mortgage free - put 0 (zero) as the mortgage principal.

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.
Quinkana is offline  
Old Jan 31st 2005, 2:08 am
  #21  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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" ....
With luck and care you have a good chance of living to 80 - 30 years in retirement. Maintaining the value of your investments and the income you derive from them becomes rapidly more uncertain the longer the funds need to last.
Quinkana is offline  
Old Jan 31st 2005, 2:19 am
  #22  
ABCDiamond
Guest
 
Posts: n/a
Default 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 .
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
 
Old Jan 31st 2005, 3:11 am
  #23  
Rocket Scientist
 
MrsDagboy's Avatar
 
Joined: Aug 2003
Location: Dreamland AKA Brisbane which is a different country to the UK
Posts: 6,911
MrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond reputeMrsDagboy has a reputation beyond repute
Default 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
Im happy to clean under other peoples, but I think I'll leave under ours dirty, thanx anyway ABC .

Megs, thanx I'll try to work it out.

Last edited by MrsDagboy; Jan 31st 2005 at 3:26 am.
MrsDagboy is offline  
Old Feb 13th 2005, 10:14 am
  #24  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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:
Attached Files
File Type: zip
Whole Of Life Finances.zip (19.1 KB, 47 views)
Quinkana is offline  
Old Feb 14th 2005, 11:09 am
  #25  
A former regular of BE
 
worzel's Avatar
 
Joined: Oct 2004
Location: Carramar, Perth
Posts: 6,241
worzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond repute
Default 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:
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?
worzel is offline  
Old Feb 14th 2005, 8:31 pm
  #26  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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?
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.
Quinkana is offline  
Old Feb 15th 2005, 9:16 am
  #27  
A former regular of BE
 
worzel's Avatar
 
Joined: Oct 2004
Location: Carramar, Perth
Posts: 6,241
worzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond reputeworzel has a reputation beyond repute
Default 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.

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.
worzel is offline  
Old Feb 15th 2005, 10:07 am
  #28  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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.
All an actuary will do is tell you how long the average Joe will live based on the statistics of large samples - eg 50% of those making 65 will croak at 80. The rest is arithmatic.

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.
Quinkana is offline  
Old Feb 16th 2005, 3:44 am
  #29  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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.
Attached Files
File Type: zip
Whole Of Life Finances 3.zip (23.5 KB, 33 views)
Quinkana is offline  
Old Mar 5th 2005, 12:22 am
  #30  
Banned
Thread Starter
 
Joined: Jul 2003
Posts: 1,048
Quinkana will become famous soon enoughQuinkana will become famous soon enough
Default 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.
Attached Files
File Type: zip
Whole Of Life Finances 4.zip (23.9 KB, 50 views)
Quinkana is offline  

Contact Us - Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service -

Copyright © 2024 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.