Where is my calculation off?
Home › Beginners Helping Beginners › Where is my calculation off?


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.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.
Thanks for the reply. That’s hard to wrap my head around. I will do some more reading.
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 30year 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.
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.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!
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.
Didn’t see that you had figured it out before I posted. You got it!
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 inflationadjusted $75,000 yearly withdrawal.
Hope this helps.
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 inflationadjusted $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 spoton 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!
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
Oh, ok, I see. That makes sense. Thanks for clarifying!
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.
the inflationadjusted $75,000 yearly withdrawal.
Click to expand…Was the inflationadjustment 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.
$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.