+ Reply to Thread
Results 1 to 6 of 6

Using Age to Add Number After Specific Period

  1. #1
    Registered User
    Join Date
    06-28-2007
    Posts
    63

    Using Age to Add Number After Specific Period

    I am working on a retirement planning spreadsheet. One of the parts of this is to determine how long someone can withdraw fund at a certain percent of their income. For example, the user inputs there age, current savings, ivestment return rate, and desired retirement age. The sheet then calculates how much money the person will have at retirement given these factors.

    The user also inputs what percentage of current income they will need in retirement. If the user needs 80% of income, then the sheet withdraws 80% of income each year from the assets at retirement while adjusting for inflation.

    I need to determine how to add social security money to this retirement withdraw process. The problem is, people can first access social security at age 62. So if someone retires at age 50, the calculator should not reduce the size of retirement withdrawls until the person reaches age 62. How do I set this up?????

    The retirement data is displayed like:
    Years of Retirement.....Retirement Savings.....Annual Withdrawl
    ..........1.......................4,166,989...................204,500.......
    ..........2.......................4,239,987...................210,000.......

    So the annual withdrawl is increasing with inflation, and retirement savings is compounding at a specific rate. I want to reduce the amount of the annual withdrawl by social security payment, starting at age 62.
    Link removed by Mod

  2. #2
    Registered User
    Join Date
    04-13-2007
    Posts
    8
    mwgr,

    Assuming you have a formula for your Annual Withdraw column, you would add this on to the end of it:

    ... - IF((AGE + "Years of Retirement") >= 62, SS Payment, 0)

    This subtracts the SS Payment IF the age is 62 or older, otherwise it subtracts zero.
    This requires you to replace AGE with the cell where the user enters their current age.
    "Years of Retirement" refers to the column you included.

    Brade

  3. #3
    Registered User
    Join Date
    06-28-2007
    Posts
    63
    Thanks for the reply.

    I didn't make the sheet I am modifying, and it is set up in a different way.

    To determine the retirement asset withdrawl rate, the sheet determines the users desired retriement income, then subtracts social security from that number and finally diviveds the results by the total retirement savings. This computes the withdrawl rate.

    In the previous sheet I showed an example of the data. The 3rd column is annual withdrawl. This is calculated by multiplying the withdrawl rate by the total amount of retirement savings. The subsequent number just increases the withdrawl rate by the inflation rate each year. This is calculated by multiplying the previous cell annual withdrawl by the inflation rate and adding the previous cell annual withdrawl.

    So, back to the original question. Is there a way to modify the withdrawl rate calclation so that it only subtracts social security after a specific number of years??? Thanks.

  4. #4
    Registered User
    Join Date
    06-28-2007
    Posts
    63
    After the previous post, I determined how to make the calculation work.

    Now I have a different question. I have a formula that I want to copy to many cells in the column below. When I copy the forumla to the cell directly below, all of the refrenced cell numbers increase by one. Is there a way to fix specific cell numbers that are being refrenced so that the cells refrenced stay the same?? Thanks.

  5. #5
    Registered User
    Join Date
    06-11-2007
    Location
    Nonthaburee Province, Thailand
    MS-Off Ver
    MS Office 2003 to MS Office 2010
    Posts
    82
    You should using $ sign to fix the cell reference on calculation.

    But please give me which cell that you want to fixed.

    for more information on $ sign, please see at http://www.cpearson.com/excel/relative.aspx
    N. Yauvasuta
    Power User Excel.

  6. #6
    Registered User
    Join Date
    06-28-2007
    Posts
    63
    Thanks for answer!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1