Menu

Where is my calculation off?

Home Beginners Helping Beginners Where is my calculation off?

  • Avatar AZPT 
    Participant
    Status: Other Professional
    Posts: 76
    Joined: 02/02/2019

    Hi all,

    I have been reading many WCI posts and the WCI book, and currently trying to work through my “retirement needs”. Per one of the website posts, it states:

    “Estimating the amount for your retirement nest egg is even more complex.  Many studies and even entire books have been written on the subject.  But the basic process is that you estimate how much money you will need to spend each year in retirement, subtract the amount you expect from any guaranteed pensions or Social Security, and then apply a “safe withdrawal rate” such as 3 to 4.5% per year.  For example, you estimate you’ll need $100K per year in today’s dollars, you have no pensions, and you expect Social Security to contribute $30K per year, indexed to inflation.  Thus, you need your portfolio to contribute $70K per year, indexed to inflation.  You decide, after looking at the studies, that you’re comfortable with a 3.5% withdrawal rate.  $70,000/0.035= $2 Million.  So your goal might be “I want $2 Million (in today’s dollars) in retirement savings by July 1st 2030.””

     

    I have ran through a few different calculations in Excel, but something isn’t adding up. My calculations show that I actually need to save LESS for a 4.0% withdrawal rate compared to a 3.5% withdrawal rate, all else being equal. I simply divided the amount needed from my portfolio by the withdrawal rate, as the post instructed. See attachment.

    Where did I mess up??

     

    Attachments:
    You must be logged in to view attached files.
    #189054 Reply
    ENT Doc ENT Doc 
    Participant
    Status: Physician
    Posts: 3355
    Joined: 01/14/2017

    The math is correct. Think of the grand total as a margin of safety. The larger the total for the same $ withdrawal = more safety. Smaller = less safe. 4% is less safe than 3.5% withdrawal.

    #189062 Reply
    Avatar AZPT 
    Participant
    Status: Other Professional
    Posts: 76
    Joined: 02/02/2019

    Thanks for the reply. That’s hard to wrap my head around. I will do some more reading.

    #189074 Reply
    Avatar ID Doc 
    Participant
    Status: Physician
    Posts: 60
    Joined: 02/23/2017

    Your starting point is you’re asking your portfolio to produce $75,000 per year. You are varying the planned percent withdrawal to figure out how much you need to save to produce that. The smaller the percentage withdrawal, the larger the portfolio needs to be to produce $75,000.

    Let’s make it more obvious and and say you’re comparing 50% to 1%.

    If your withdrawal rate was 50%, your portfolio would need to be $150,000 to produce $75,000.

    If your withdrawal rate was 1%, your portfolio would need to be $7,500,000 to produce $75,000.

    If you’re planning on a 30-year retirement, it’s now pretty obvious that you can’t withdraw 50% of $150,000 per year and expect it to last 30 years. But there’s no chance of you running out of money at $75,000/year from a $7.5M portfolio.

    The 4% that’s commonly cited is what the trinity study found to be the rate at which you’re highly likely not to run out of money. Those that want to be even more “safe” might plan on a withdrawal rate of 3.5 or even 3%, but the nest egg will need to be larger for that.

    #189164 Reply
    Liked by ID Doc, Indest, SLC OB, AZPT
    Avatar AZPT 
    Participant
    Status: Other Professional
    Posts: 76
    Joined: 02/02/2019

    Thanks IDDoc for the reply.

    That makes more sense when using distant numbers such as 50% to 1%. My confusion I guess comes in the next step of then determining how much money one must save each year (for 30 years) in order to produce 75,000/yr at a specific withdrawal rate.

    When I run the Future Value formula found on the WCI post, the numbers I come up with are in the attachment below. What I am using is:

    =FV(5%,30,-x,-100000)

    Where 5% is the average return, 30 is the number of years saving, x is the yearly amount saved, and 100000 is the beginning/already saved amount (specific to my individual situation).

    In this example, my calculations are showing that I would need to save $26k yearly for the portfolio to produce $75k at a 3.5% withdrawal rate and an averaged return of 5%. Comparatively, I would need to save only $22k yearly for the portfolio to produce $75k at a 4.0% withdrawal rate and still an averaged return of 5%.

    What am I missing here? Wouldn’t I need to be saving MORE each month, all else being equal, if I wanted to use a higher percentage of my portfolio each year? Maybe this is the same concept I am getting confused with previously and I just can’t see it.

    Attachments:
    You must be logged in to view attached files.
    #189169 Reply
    Avatar AZPT 
    Participant
    Status: Other Professional
    Posts: 76
    Joined: 02/02/2019

    Right after posting I think I just answered my own question…

    So, if I want to produce the same $75,000 each year and use a lower withdrawal percentage of my portfolio, the portfolio has to be LARGER to accommodate for that. Therefore I must save MORE each month, if at the lower withdrawal rate, because I need a larger portfolio fund to be withdrawing the money from at such a low rate.

    I think I got it!

    #189174 Reply
    Avatar ID Doc 
    Participant
    Status: Physician
    Posts: 60
    Joined: 02/23/2017

    No. Since the $75,000 is fixed, you need to save more yearly to get you to a bigger nest egg to allow you to take a smaller percent of your portfolio to produce the $75,000.

    If you start with $100,000:
    Saving $26,000 yearly for 30 years with a 5% return gets you to a nest egg of ~$2.14M. 3.5% of ~$2.14M is $75,000.
    Saving $22,000 yearly for 30 years with a 5% return gets you to a nest egg of ~$1.87M. 4.0% of ~$1.87M is $75,000.

    You need to save less money to get to a nest egg of $1.87M, but since you need to take a higher % of it to get to your desired income, it’s somewhat less safe.

    #189176 Reply
    Liked by Indest, AZPT
    Avatar ID Doc 
    Participant
    Status: Physician
    Posts: 60
    Joined: 02/23/2017

    Didn’t see that you had figured it out before I posted. You got it!

    #189177 Reply
    Liked by AZPT
    ENT Doc ENT Doc 
    Participant
    Status: Physician
    Posts: 3355
    Joined: 01/14/2017
    When I run the Future Value formula found on the WCI post, the numbers I come up with are in the attachment below. What I am using is: =FV(5%,30,-x,-100000) Where 5% is the average return, 30 is the number of years saving, x is the monthly amount saved, and 100000 is the beginning/already saved amount (specific to my individual situation).

    Click to expand…

    It looks like you meant to say that X is the yearly amount saved.  In any event, you calculated it as a yearly amount.  In any Excel formula you need to make sure the periods, rate, and payments all match – monthly, yearly, etc.  However, you know your FV already – it’s your total amount needed as determined by your annual withdrawal in $ and your safe withdrawal rate:

    $75,000/0.04 = $1,875,000   or   $75,000/0.035 = $2,142,857

    So don’t use the FV formula.  What you seem to be after, and what everyone doing this planning should be after, is how much you need to save yearly/monthly, given these assumptions, to reach your goal.  That calculation is determined by Excel’s PMT function:

    “=PMT(rate,nper,pv,fv,type)”

    where rate is your periodic rate (monthly, yearly), nper is your number of periods (months, years), pv is your current savings (negative if savings because it’s money being put in, or going away from your person), fv is your future goal (positive since it’s coming to you), and type is whether the PMT is contributed at the beginning of the period or at the end.

    The yearly amount is OK but it doesn’t fit reality very well.  It’s better to determine how much you’ll need to save monthly – this allows for the “getting into the trenches” budgeting and fighting to make those payments work.  It’s a lot harder to do when looking at it from a yearly perspective.  The monthly calculations you would use are:

    rate = 0.004074124 (monthly rate based on 5% APY…can go into this if you would like)
    nper = 360 (months)
    pv = -100000
    fv = 2142857
    type = 0 (assumed that you contribute savings at the end of the month)

    Plugging these into the Excel PMT function you get -$2,098.  That means you need to put close to $2,100 into your retirement every month to achieve the inflation-adjusted $75,000 yearly withdrawal.

    Hope this helps.

    #189187 Reply
    Liked by Tim
    Avatar AZPT 
    Participant
    Status: Other Professional
    Posts: 76
    Joined: 02/02/2019
    medical school scholarship sponsor

    When I run the Future Value formula found on the WCI post, the numbers I come up with are in the attachment below. What I am using is: =FV(5%,30,-x,-100000) Where 5% is the average return, 30 is the number of years saving, x is the monthly amount saved, and 100000 is the beginning/already saved amount (specific to my individual situation).

    Click to expand…

    It looks like you meant to say that X is the yearly amount saved.  In any event, you calculated it as a yearly amount.  In any Excel formula you need to make sure the periods, rate, and payments all match – monthly, yearly, etc.  However, you know your FV already – it’s your total amount needed as determined by your annual withdrawal in $ and your safe withdrawal rate:

    $75,000/0.04 = $1,875,000   or   $75,000/0.035 = $2,142,857

    So don’t use the FV formula.  What you seem to be after, and what everyone doing this planning should be after, is how much you need to save yearly/monthly, given these assumptions, to reach your goal.  That calculation is determined by Excel’s PMT function:

    “=PMT(rate,nper,pv,fv,type)”

    where rate is your periodic rate (monthly, yearly), nper is your number of periods (months, years), pv is your current savings (negative if savings because it’s money being put in, or going away from your person), fv is your future goal (positive since it’s coming to you), and type is whether the PMT is contributed at the beginning of the period or at the end.

    The yearly amount is OK but it doesn’t fit reality very well.  It’s better to determine how much you’ll need to save monthly – this allows for the “getting into the trenches” budgeting and fighting to make those payments work.  It’s a lot harder to do when looking at it from a yearly perspective.  The monthly calculations you would use are:

    rate = 0.004074124 (monthly rate based on 5% APY…can go into this if you would like)
    nper = 360 (months)
    pv = -100000
    fv = 2142857
    type = 0 (assumed that you contribute savings at the end of the month)

    Plugging these into the Excel PMT function you get -$2,098.  That means you need to put close to $2,100 into your retirement every month to achieve the inflation-adjusted $75,000 yearly withdrawal.

    Hope this helps.

    Click to expand…

    Yes, I absolutely meant Yearly savings (not monthly), good catch.

    Wow… thank you for the response as you are correct, that is exactly what I have been working towards calculating. I do like the “30,000 ft view” of seeing the Yearly savings needed for a certain nest egg, but you are spot-on that this isn’t reality, because the savings is done on a monthly basis in terms of it being within a budget plan. Can’t wait to play with this new formula more on a new spreadsheet devoted to monthly savings figures.

    And yes, I am interested in how you came up with the 4.074124% return based on 5% APY, if you don’t mind sharing.

    Thank you again ENT Doc!

    #189235 Reply
    ENT Doc ENT Doc 
    Participant
    Status: Physician
    Posts: 3355
    Joined: 01/14/2017

    To be clear, it’s not 4.074124% as the monthly rate. It’s 0.4074124%.

    APY = (1+Periodic Rate)^Periods – 1

    Periodic Rate = (1+APY)^(1/Periods) – 1

    #189258 Reply
    Avatar AZPT 
    Participant
    Status: Other Professional
    Posts: 76
    Joined: 02/02/2019

    Oh, ok, I see. That makes sense. Thanks for clarifying!

    #189259 Reply
    Liked by ENT Doc
    Hank Hank 
    Moderator
    Status: Attorney
    Posts: 1284
    Joined: 03/27/2017
    And yes, I am interested in how you came up with the 4.074124% return based on 5% APY, if you don’t mind sharing.

    Click to expand…

    You misplaced a decimal point.  You need a return of 0.4074124% per month to get your 5% annualized return.  Things compound and build month by month.  If you just multiply by 12, you see that it’s a little short of 5%.  1.004074124^12 gives you 5% annual returns.

    #189304 Reply
    Liked by AZPT, ENT Doc
    Avatar moabjer 
    Participant
    Status: Pharmacist
    Posts: 2
    Joined: 02/08/2019

     the inflation-adjusted $75,000 yearly withdrawal.

     

    Click to expand…

    Was the inflation-adjustment already included in the rate,nper,pv,fv,type numbers

     

    I only saw a 5% APY for returns not inflation.

     

    i thought the $75,000 was in today’s dollars.

    #189355 Reply
    ENT Doc ENT Doc 
    Participant
    Status: Physician
    Posts: 3355
    Joined: 01/14/2017

    $75,000 is in today’s dollars, meaning the amount has been inflation adjusted -$75k today, tomorrow, next year are all worth the same.

    The FV is in today’s dollars, or inflation adjusted, as well. The FV was derived from $75k, which is in today’s dollars.

    Matching the inflation adjustment concept, the rate too must be a real rate of return. That is why for his calculations the 5% must be a real rate of return.

    #189362 Reply
    Liked by AZPT

Reply To: Where is my calculation off?

In case of a glitch or error, please save your text elsewhere, clear browser cache, close browser, open browser and refresh the page.

Notifications Mark all as read  |  Clear