Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-02-2012, 05:53 PM
ubns ubns is offline Calculation for my assignment important please Windows 7 32bit Calculation for my assignment important please Office 2010 32bit
Competent Performer
Calculation for my assignment important please
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default Calculation for my assignment important please

Hi,



I have got my assignment.

I have to calculate the retirement savings balance with the following assumption:

Client - Test, Age - 33

1) Salary - $100,000
2) Inflation - 3%;

3) SG - Contribution from employer (pa) - 9% of salary;
Tax on this 9% is 15%. so in this case, 9% is $9,000, tax will be $1,350.

5) Salary Sacrifice - contribution from employee -3% of salary;
Tax on this is same i.e. 15% of 3%. so in this case 3% is $3,000, tax will be $450.

6) Client is working till age 56.

I have to calculate the balance of retirement savings @ age 56.

We do not have to consider his cost of living. Just focus on retirement savings.

I know I can use FV formula, however will take into consideration inflation each year. As each year the value of income will increase by 3% which will increase the SG (9% of client income; contribution by employer) and Salary Sacrifice (3% of client income; contribution by employee).

Please advice, its really important.
Reply With Quote
  #2  
Old 05-07-2012, 04:00 AM
macropod's Avatar
macropod macropod is offline Calculation for my assignment important please Windows 7 64bit Calculation for my assignment important please Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi umesh,

You shouldn't really ask for help with assignments - the whole idea is that you should do the learning, not have someone else provide the answers.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-07-2012, 04:22 AM
ubns ubns is offline Calculation for my assignment important please Windows 7 32bit Calculation for my assignment important please Office 2010 32bit
Competent Performer
Calculation for my assignment important please
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Red face Fair enough

I have done most of the assignment question, however was struck at $25,000 limit thing, thats why I asked that.

Assignment is more on strategy rather than excel calculation. we can use financial planning software (which we have), however I thought it should learn that too.
Reply With Quote
  #4  
Old 05-07-2012, 05:08 AM
macropod's Avatar
macropod macropod is offline Calculation for my assignment important please Windows 7 64bit Calculation for my assignment important please Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi umesh,

Whilst you've identified inflation, you seem to have omitted income from the invested superannuation. This will have a significant effect on the retirement savings. If you want to calculate the retirement savings in today's values, simply ignore the effects of inflation on the income and calculate the retirement savings on the basis of the difference between the inflation rate and investment rate of return.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-07-2012, 05:20 AM
ubns ubns is offline Calculation for my assignment important please Windows 7 32bit Calculation for my assignment important please Office 2010 32bit
Competent Performer
Calculation for my assignment important please
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default Excel inflation plus investment income

hi Paul,

Please refer to the attached sheet.

there are four scenarios; all of them have inflation of 3%, however there is differnt earning rate (this is net earning rate, after taxes), but not the real return rate (which factors in the inflation).

I have also used the conditional formatting and entered the formula (min) as per your suggestion. That was great help..

Do you think it would be possible to enter - retirement age in input sheet cell "b24" and make certain rows hidden or invisible in sheets "scenario1,scenario2,3 and 4. I do not want in the entire workbook since there can be many more sheet I will enter.

What do you think?
Attached Files
File Type: xlsm Assignment 2testimac.xlsm (50.1 KB, 9 views)
Reply With Quote
  #6  
Old 05-07-2012, 09:19 PM
macropod's Avatar
macropod macropod is offline Calculation for my assignment important please Windows 7 64bit Calculation for my assignment important please Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi umesh,

It is difficult to decipher from your workbook what all the data relationships are, as various amounts appear to be replicated, but without formulae to demonstrate a dependence. On top of that, various amounts are introduced (eg mortgage, inheritance, retirement income) without any indication of how they relate to anything else.

That said, you shouldn't need any of the scenario worksheets. You should be able to build all of the logic into a small number of formulae on a single worksheet.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 05-07-2012, 09:47 PM
ubns ubns is offline Calculation for my assignment important please Windows 7 32bit Calculation for my assignment important please Office 2010 32bit
Competent Performer
Calculation for my assignment important please
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default

thanks Paul i have downloaded it.


Paul, when you say "I dont need so many scenarios", do you mean that, different scenarios will be presented through charts?
Reply With Quote
  #8  
Old 05-07-2012, 10:35 PM
macropod's Avatar
macropod macropod is offline Calculation for my assignment important please Windows 7 64bit Calculation for my assignment important please Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi umesh,

What I mean is that, given a starting and ending age, you can calculate whatever scenario you're interested in via a single formula. See attached. The 'retirement funds' value expressed in current-dollar terms. If you want to see the actual dollars, simply set the assumed inflation rate to 0.
Attached Files
File Type: xlsx Demo.xlsx (8.4 KB, 7 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 05-07-2012, 11:27 PM
ubns ubns is offline Calculation for my assignment important please Windows 7 32bit Calculation for my assignment important please Office 2010 32bit
Competent Performer
Calculation for my assignment important please
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default thats awesomly smart

any suggestions for calculating how long annuities payments will last for?
Reply With Quote
  #10  
Old 05-07-2012, 11:36 PM
macropod's Avatar
macropod macropod is offline Calculation for my assignment important please Windows 7 64bit Calculation for my assignment important please Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

I'm not an actuary, but you could do something along the lines of the attached.
Attached Files
File Type: xlsx Demo.xlsx (8.5 KB, 8 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 05-09-2012, 12:09 AM
ubns ubns is offline Calculation for my assignment important please Windows 7 32bit Calculation for my assignment important please Office 2010 32bit
Competent Performer
Calculation for my assignment important please
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default

Hi Paul,

Thanks heaps.

I was looking at the formula and was wondering how can we factor indexation of 3% in the income. I mean the income will increase by 3% per annum so will the contribution and salary sacrifice will increase per annum by 3%.

I tried working out, but could not work on this simple and elegant one line formula you have sent me.
Reply With Quote
  #12  
Old 05-09-2012, 12:25 AM
ubns ubns is offline Calculation for my assignment important please Windows 7 32bit Calculation for my assignment important please Office 2010 32bit
Competent Performer
Calculation for my assignment important please
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default

how can we calculate retirement savings will last for in excel - is there any formula?
Reply With Quote
  #13  
Old 05-09-2012, 12:30 AM
macropod's Avatar
macropod macropod is offline Calculation for my assignment important please Windows 7 64bit Calculation for my assignment important please Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The calculations already do that. Remember, everything in expressed in today's values. So, if you've got 3% inflation, and a 7% rate of return, all that's really happening is that an effective 4% rate of return is occurring.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #14  
Old 05-09-2012, 12:39 AM
macropod's Avatar
macropod macropod is offline Calculation for my assignment important please Windows 7 64bit Calculation for my assignment important please Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

That depends on how fast you draw them down. I've already given you a formula that calculates the draw-down rate that will maintain a constant income (in today's values) till age 90 - or whatever other age you care to nominate. Excel has other functions (eg PMT) that allow you to assume a given draw-down rate and see how long it'll take to exhaust the funds. It's time you started studying Excel's financial functions.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #15  
Old 05-09-2012, 12:40 AM
ubns ubns is offline Calculation for my assignment important please Windows 7 32bit Calculation for my assignment important please Office 2010 32bit
Competent Performer
Calculation for my assignment important please
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default

yes Paul, I think there is some issue.

Actually I entered exactly the following figures in our financial planning software and it is giving the result of $602,732.

Current Age33Retirement Age55Inflation Rate3%Investment Rate6.75%Starting Balance $ 75,000.00 Annual Income $ 100,000.00 Super Guarantee9%Salary Sacrifice10%Contributions Tax15%Retirement Funds$764,582.97

I have used exactly the same assumptions?

So I am sure it is doing indexation but I am not sure why the difference is? Any idea or thoughts from your side?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
HELP!! Replaced new document over 3000 word assignment! Clair Word 1 11-10-2011 11:34 AM
Setting an Assignment Field from VBA CGM3 Project 1 10-05-2010 05:51 AM
REALLY IMPORTANT: corrupt word file !!! HELP !!! Libertin Word 0 04-19-2010 09:00 AM
Help needed with assignment ccmystery Outlook 0 04-09-2010 05:59 AM
tafe assignment alharris Word 0 11-16-2009 12:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:30 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft