Confused by Two DinkyTown Calculators

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
Cheego
Posts: 45
Joined: Mon Jul 18, 2016 11:57 am

Confused by Two DinkyTown Calculators

Post by Cheego » Sun Jun 10, 2018 2:30 pm

I've used the calculators at Dinkytown to help friends and family members over the years. Earlier this week, I was helping a co-worker with his family's budget and retirement plans and we decided to plug some numbers into two different calculators at DT but the results were not the same. I've looked over this a dozen times and I don't see why the math from one calc doesn't match the other.

Anyone want to help?

Go here... http://www.dinkytown.net/java/RetirementIncome.html
And plug in these numbers...

Starting balance: $2,800,000.00
Annual contributions: $0 (zero because they want to retire now)
Current age: 50
Age of retirement: 51
Year of retirement: 39
Increase deposits: unchecked (irrelevant because there will be no future deposits if they retire)
Savings is tax-deferred: checked
Rate of return before retirement: 0% (irrelevant in this case since they are ready to retire now)
Rate of return during retirement: 3%
Current tax rate: 34%
Retirement tax rate: 25%
Inflation rate: 3.5%

It will calculate $7,313 of monthly income - after taxes and adjusted for inflation. That equals $87,756 annually and based on the last four years of their expenses, that number is more than they need annually for 39 years of retirement. Yes... we did talk about the ever-increasing costs of health insurance but that is not important here.

What concerns me is that this other calculator seems to show something much different.
Go here... http://www.dinkytown.net/java/Retiremen ... ution.html
And plug in these numbers...

Cumulative Savings: $2,800,000
Amount You want to Spend in Retirement: $87,759 (this is the number calculated above)
After Tax Rate of Return: 3%
Inflation: 3.5%

It says that this will only get them 29 years and 2 months of retirement funds. I don't understand why this is not showing 39 years instead, based on the first calculator.

I know this is a lot to ask but if anyone has time to put these two numbers in over at DT and help me understand the cause of the difference, it would be greatly appreciated. :sharebeer

Dottie57
Posts: 3428
Joined: Thu May 19, 2016 5:43 pm

Re: Confused by Two DinkyTown Calculators

Post by Dottie57 » Sun Jun 10, 2018 2:51 pm

The second one looks wrong. I put in 30 r retirement, My amount saved , current savings and an amount equal to 4% of withdrawl. I expected it would tell me greater than 14 years before money would be exhausted.

P.s. the growth rate was set to 4% real.

The first calculator gave me an amount which many calculators have come close to.

User avatar
FiveK
Posts: 4857
Joined: Sun Mar 16, 2014 2:43 pm

Re: Confused by Two DinkyTown Calculators

Post by FiveK » Sun Jun 10, 2018 5:13 pm

The second one seems closer to what Excel's financial functions return. E.g., =NPER(-.005,87756,-2800000,0,1) gives 29.7 years. The -0.005 is the approximate real return with 3% nominal and 3.5% inflation.

The first one seems strange. It talks about marginal tax rates, but then uses the retirement tax rate as an effective rate. To get the "after inflation" monthly number it simply divides the "before inflation" result by (1+inflation). Fine for the first year, but not so good after that. The "before inflation, before taxes" number also doesn't match Excel's PMT result.

Everyone has different tastes. Web sites that hide the calculation details, particularly when the results don't match simple checks, seem sketchy. A spreadsheet that allows one to see what it's doing, e.g., the personal finance toolbox spreadsheet, seems useful (see the 'Misc. calcs' tab rows 16-24 for this situation). But reasonable folks can differ on the definitions of "sketchy" and "useful". ;)

Jablean
Posts: 73
Joined: Sat Jun 02, 2018 2:38 pm

Re: Confused by Two DinkyTown Calculators

Post by Jablean » Sun Jun 10, 2018 6:04 pm

There's something wrong with how the inflation rate is calculating. Inflation and rate of return should cancel each other out and it does in the first.

I zeroed all the returns, taxes, inflation and tested against the first. Everything works fine unless you put inflation in at an amount above 0.

User avatar
#Cruncher
Posts: 2550
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Confused by Two DinkyTown Calculators

Post by #Cruncher » Sun Jun 10, 2018 9:08 pm

Cheego wrote:
Sun Jun 10, 2018 2:30 pm
Go here... http://www.dinkytown.net/java/RetirementIncome.html
… Year(s) of retirement: 39
… It will calculate $7,313 of monthly income - after taxes and adjusted for inflation. That equals $87,756 annually …

Go here... http://www.dinkytown.net/java/Retiremen ... ution.html
… Amount You want to Spend in Retirement: $87,759 (this is the number calculated above)
… It says that this will only get them 29 years and 2 months of retirement funds.
My calculations don't agree with either of the above. To last 39 years, one can spend only $48,803 after taxes in constant dollars annually. If one spends $87,759, money will run out in the 23rd year.

I follow these assumptions based on the original post:
  • 3% nominal return on investment balance every year.
  • After tax spending increases 3.5% every year (starting the first year).
  • Spending funded solely by withdrawals from tax deferred investments initially $2,800,000.
  • Withdrawals subject to 25% tax. I.e., one needs to withdraw an additional 1/3 to pay tax.
Here is the first case with $48,803 spending to last 39 years:

Code: Select all

Year   Income     Spend   Withdraw     Balance

Code: Select all

   0             48,803              2,800,000 
   1   84,000    50,511     67,348   2,816,652 
   2   84,500    52,279     69,705   2,831,446 
   3   84,943    54,109     72,145   2,844,244 
   4   85,327    56,003     74,670   2,854,902 
   5   85,647    57,963     77,284   2,863,265 
   6   85,898    59,991     79,988   2,869,175 
   7   86,075    62,091     82,788   2,872,462 
   8   86,174    64,264     85,686   2,872,950 
   9   86,189    66,513     88,685   2,870,454 
  10   86,114    68,841     91,789   2,864,779 
  11   85,943    71,251     95,001   2,855,721 
  12   85,672    73,745     98,326   2,843,067 
  13   85,292    76,326    101,768   2,826,591 
  14   84,798    78,997    105,330   2,806,059 
  15   84,182    81,762    109,016   2,781,225 
  16   83,437    84,624    112,832   2,751,830 
  17   82,555    87,586    116,781   2,717,604 
  18   81,528    90,651    120,868   2,678,264 
  19   80,348    93,824    125,098   2,633,514 
  20   79,005    97,108    129,477   2,583,042 
  21   77,491   100,506    134,009   2,526,525 
  22   75,796   104,024    138,699   2,463,622 
  23   73,909   107,665    143,553   2,393,977 
  24   71,819   111,433    148,578   2,317,219 
  25   69,517   115,333    153,778   2,232,957 
  26   66,989   119,370    159,160   2,140,786 
  27   64,224   123,548    164,731   2,040,279 
  28   61,208   127,872    170,496   1,930,991 
  29   57,930   132,348    176,464   1,812,457 
  30   54,374   136,980    182,640   1,684,190 
  31   50,526   141,774    189,032   1,545,684 
  32   46,371   146,736    195,648   1,396,406 
  33   41,892   151,872    202,496   1,235,802 
  34   37,074   157,188    209,584   1,063,292 
  35   31,899   162,689    216,919     878,272 
  36   26,348   168,383    224,511     680,109 
  37   20,403   174,277    232,369     468,143 
  38   14,044   180,376    240,502     241,686 
  39    7,251   186,690    248,919          17
And here is the second case with annual spending of $87,759:

Code: Select all

Year   Income     Spend   Withdraw     Balance

Code: Select all

   0             87,759              2,800,000 
   1   84,000    90,831    121,107   2,762,893 
   2   82,887    94,010    125,346   2,720,433 
   3   81,613    97,300    129,733   2,672,313 
   4   80,169   100,705    134,274   2,618,208 
   5   78,546   104,230    138,974   2,557,781 
   6   76,733   107,878    143,838   2,490,677 
   7   74,720   111,654    148,872   2,416,525 
   8   72,496   115,562    154,082   2,334,938 
   9   70,048   119,607    159,475   2,245,511 
  10   67,365   123,793    165,057   2,147,820 
  11   64,435   128,125    170,834   2,041,420 
  12   61,243   132,610    176,813   1,925,850 
  13   57,775   137,251    183,002   1,800,624 
  14   54,019   142,055    189,407   1,665,236 
  15   49,957   147,027    196,036   1,519,157 
  16   45,575   152,173    202,897   1,361,834 
  17   40,855   157,499    209,999   1,192,691 
  18   35,781   163,011    217,349   1,011,123 
  19   30,334   168,717    224,956     816,501 
  20   24,495   174,622    232,829     608,167 
  21   18,245   180,734    240,978     385,434 
  22   11,563   187,059    249,412     147,584 
  23    4,428   193,606    258,142    (106,130)

FiveK wrote:
Sun Jun 10, 2018 5:13 pm
The second one seems closer to what Excel's financial functions return. E.g., =NPER(-.005,87756,-2800000,0,1) gives 29.7 years. The -0.005 is the approximate real return with 3% nominal and 3.5% inflation.
I only get 22.6 years because I consider the spending to be after tax. Making this change (and tweaking the rate) I can use the Excel NPER function to get the same results as shown in my two tables:
39.0 = NPER(1.03 / 1.035 - 1, 48803 / 0.75, -2800000, 0, 0)
22.6 = NPER(1.03 / 1.035 - 1, 87759 / 0.75, -2800000, 0, 0)


The Excel PMT function can be used to calculate the spending that will last a given number of years. 39 years for example:
48,803 = PMT(1.03 / 1.035 - 1, 39, -2800000, 0, 0) * 0.75

Cheego
Posts: 45
Joined: Mon Jul 18, 2016 11:57 am

Re: Confused by Two DinkyTown Calculators

Post by Cheego » Mon Jun 11, 2018 8:49 pm

Thanks for the comments to everyone. Cruncher, I was hoping you'd chime in on this thread so big thanks to you as well.

I've been thinking more about this and perhaps the easiest thing to do is determine the math to calculate monthly available spending dollars based on these data points...
  • Starting balance
  • Number of years to spend down the balance
  • Tax rate during the term of the spend down
  • Estimated interested during the spend down term assuming 12 compounds per year
  • Estimated inflation during the spend down term assuming 12 compounds per year
Is there anything I'm missing? If not, then Cruncher could you give me the Excel formula for this?

User avatar
FiveK
Posts: 4857
Joined: Sun Mar 16, 2014 2:43 pm

Re: Confused by Two DinkyTown Calculators

Post by FiveK » Mon Jun 11, 2018 8:58 pm

Cheego wrote:
Mon Jun 11, 2018 8:49 pm
Thanks for the comments to everyone. Cruncher, I was hoping you'd chime in on this thread so big thanks to you as well.

I've been thinking more about this and perhaps the easiest thing to do is determine the math to calculate monthly available spending dollars based on these data points...
  • Starting balance
  • Number of years to spend down the balance
  • Tax rate during the term of the spend down
  • Estimated interested during the spend down term assuming 12 compounds per year
  • Estimated inflation during the spend down term assuming 12 compounds per year
Is there anything I'm missing? If not, then Cruncher could you give me the Excel formula for this?
Note that the tax rate should be the overall (aka effective) rate, and is unlikely to be anywhere near to 25% unless spending is over $400K/yr. ;)

With that, and a change to monthly instead of annual compounding (divide the first term and multiply the second term by 12), the formula including PMT in #Cruncher's post will work.

User avatar
#Cruncher
Posts: 2550
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Confused by Two DinkyTown Calculators

Post by #Cruncher » Tue Jun 12, 2018 1:40 pm

Cheego wrote:
Mon Jun 11, 2018 8:49 pm
Is there anything I'm missing? If not, then Cruncher could you give me the Excel formula for this?
I've built a little spreadsheet around the PMT function with the following changes from the approach in my previous post:
  • Ignore inflation and instead forecast in constant dollars with the assumed investment return in real terms.
  • Add option to specify an ending portfolio value other than $0.
  • Add option to use monthly compounding (by entering "12" in row 5).
  • Use 2018 tax brackets to calculate federal income tax for a Single or Joint Return. [1]
Here is an example showing one can withdraw $102,362 at the end of each year from a tax deferred retirement account given a 2% real return and still have $100,000 remaining after 39 years. The tax computation assumes $34,000 other taxable income [1] besides the retirement account withdrawal, and a $24,000 standard deduction on a Joint return. Total taxable income would be $112,362 on which $16,599 of federal income tax would be owed.

Code: Select all

Row         Col A                    Col B
  1  Real return every year          2.000%
  2  Years                           39.000
  3  Starting portfolio value     2,800,000
  4  Ending portfolio value         100,000
  5  Periods / year                       1
  6  Other taxable income            34,000
  7  Tax deduction                   24,000
  8  "Single" or "Joint" return       Joint
  9  Withdrawal                     102,362
 10  Taxable income                 112,362
 11  Federal tax                     16,599
 12  Withdrawal less federal tax     85,763
One can also use Excel's Goal Seek to back into the values in rows 1, 2, 3, or 4 that will produce a desired withdrawal net of federal tax in row 12. For example, the following shows that spending $120,000 annually ($10,000 at the end of each month) net of federal tax will cause the portfolio value to be exhausted in about 21 years 7 months (21.575 years).
  • Choose Goal Seek tool. (In Excel 2016 it's in the Data tab in What If Analyses).
  • Enter the following:

    Code: Select all

    Set cell:            B12
    To value:            120000
    By changing cell:    B2
  • Click [OK] twice

Code: Select all

  1  Real return every year          2.000%
  2  Years                           21.575
  3  Starting portfolio value     2,800,000
  4  Ending portfolio value               0
  5  Periods / year                      12
  6  Other taxable income            34,000
  7  Tax deduction                   12,000
  8  "Single" or "Joint" return      Single
  9  Withdrawal                     159,896
 10  Taxable income                 181,896
 11  Federal tax                     39,896
 12  Withdrawal less federal tax    120,000
Follow these steps to build the spreadsheet:
  • Select All, Copy, and Paste the following at cell A1 of an empty sheet: [2]

    Code: Select all

    Real return every year
    Years
    Starting portfolio value
    Ending portfolio value
    Periods / year
    Other taxable income
    Tax deduction
    "Single" or "Joint" return
    Withdrawal
    Taxable income
    Federal tax
    Withdrawal less federal tax
  • Select All, Copy, and Paste the following at cell B1: [2]

    Code: Select all

    0.02
    21.5745645775008
    2800000
    0
    12
    34000
    12000
    Single
    =B5*PMT(B1/B5,B5*B2,-B3,B4,0)
    =B9+B6-B7
    =IF(UPPER(LEFT(TRIM(B8),1))="S",10%*MAX(0,MIN(B10,9525))+12%*MAX(0,MIN(B10,38700)-9525)+22%*MAX(0,MIN(B10,82500)-38700)+24%*MAX(0,MIN(B10,157500)-82500)+32%*MAX(0,MIN(B10,200000)-157500)+35%*MAX(0,MIN(B10,500000)-200000)+37%*MAX(0,B10-500000),10%*MAX(0,MIN(B10,19050))+12%*MAX(0,MIN(B10,77400)-19050)+22%*MAX(0,MIN(B10,165000)-77400)+24%*MAX(0,MIN(B10,315000)-165000)+32%*MAX(0,MIN(B10,400000)-315000)+35%*MAX(0,MIN(B10,600000)-400000)+37%*MAX(0,B10-600000))
    =B9-B11
  • Format cells in column B if desired.
  • Modify assumptions in cells B1:B8
  1. For simplicity I ignore state and local income tax.
  2. Other income could represent the taxable portion of Social Security benefits. E.g., $34,000 being the maximum taxable 85% of $40,000 in benefits. If so the couple would have a total $125,763 to spend after paying federal income tax ($40,000 + $85,763).
  3. Depending on your browser and spreadsheet program you may need to use Paste Special and choose different options.

Cheego
Posts: 45
Joined: Mon Jul 18, 2016 11:57 am

Re: Confused by Two DinkyTown Calculators

Post by Cheego » Tue Jun 12, 2018 4:33 pm

Kudos and 2,559 points to #Cruncher!

Thank you for this very helpful spreadsheet. I'm thinking the next step would be to divide "portfolio value" into tax deferred and pre-taxed lines but my caveman math is telling me it doesn't matter.

I'll drop in a 7.5% state and local rate to get a final number.

Again, huge thanks to #Cruncher.

Post Reply