Confused by Two DinkyTown Calculators [Retirement planning]

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

Confused by Two DinkyTown Calculators [Retirement planning]

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: 3938
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: 5141
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: 185
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: 2614
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: 50
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

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: 5141
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: 2614
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: 50
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

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.

Cheego
Posts: 50
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Need Help with Spread Sheet Calculation

Post by Cheego » Sat Jul 28, 2018 8:25 pm

[Thread merged into here, see below. --admin LadyGeek]

I'm working on some retirement details and I've found a need for a variation of the spread sheet coding that #Cruncher previously offered in another thread. That coding is shown below.
What I'm looking for is a way to input the "Withdrawal less federal tax" and have the spread sheet calculate the "Starting portfolio value".
EDIT: This was a mistake on my part. What I really need is a way to calculated the number of years that an investment will generate a specified after-tax return given a real return percentage as well as 2018 federal taxes and 7.75% SAL taxes.
Last edited by Cheego on Mon Jul 30, 2018 4:17 pm, edited 1 time in total.

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Need Help with Spread Sheet Calculation

Post by LadyGeek » Sat Jul 28, 2018 8:37 pm

Which of #Cruncher's threads are you referring to? Is it this one: Effect of Social Security when Retiring before Benefits Begin
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Cheego
Posts: 50
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Re: Need Help with Spread Sheet Calculation

Post by Cheego » Sat Jul 28, 2018 8:57 pm

Its the one posted here...
viewtopic.php?f=2&t=251399#p3969698
Last edited by Cheego on Mon Jul 30, 2018 4:18 pm, edited 1 time in total.

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Confused by Two DinkyTown Calculators

Post by LadyGeek » Sat Jul 28, 2018 9:03 pm

OK, let's do it this way. I merged your question back into the original post. First, it provides the complete background so readers can understand what's going on.

Second, #Cruncher posted in this thread. It will show up under Your posts and he'll hopefully see your question.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

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

Re: Need Help with Spread Sheet Calculation

Post by #Cruncher » Sun Jul 29, 2018 8:56 am

Cheego wrote:
Sat Jul 28, 2018 8:25 pm
What I'm looking for is a way to input the "Withdrawal less federal tax" and have the spread sheet calculate the "Starting portfolio value".
I've modified the spreadsheet from my last post to do this.

Code: Select all

  1  Withdrawal less federal tax    120,000
  2  Other taxable income            34,000
  3  Tax deduction                   12,000
  4  1 for Single or 2 for Joint          1
  5  After tax income               142,000
  6  Taxable income                 181,896
  7  Federal tax                     39,896
  8  Withdrawal                     159,896
  9  Periods / year                      12
 10  Real return every year          2.000%
 11  Years                           21.575
 12  Starting portfolio value     2,800,000
 13  Ending portfolio value               0
The major element is a long formula in cell B6 that calculates 2018 taxable income from after tax income. (The spreadsheet in my last post did the reverse: calculate federal tax from taxable income.) Once "Withdrawal" is determined in cell B8, the values in cells B10:B13 can be calculated, respectively, with the Excel RATE, NPER, PV, and FV functions.

To calculate any one of the four, enter the assumptions in column C for the other three. E.g., to get the table above I entered 2% in C10, 21.575 in C11, and 0 in C13. I then blanked cell C12. The formula in B12 detects this and computes the $2,800,000 starting value. To create the spreadsheet, Select All, Copy, and Paste [*] the following at cell A1 of a blank Excel sheet:

Code: Select all

Withdrawal less federal tax	120000
Other taxable income	34000
Tax deduction	12000
1 for Single or 2 for Joint	1
After tax income	=B1+B2-B3
Taxable income	=IF(B4=1,IF(B5>349310.5,500000+(B5-349310.5)/63%,IF(B5>154310.5,200000+(B5-154310.5)/65%,IF(B5>125410.5,157500+(B5-125410.5)/68%,IF(B5>68410.5,82500+(B5-68410.5)/76%,IF(B5>34246.5,38700+(B5-34246.5)/78%,IF(B5>8572.5,9525+(B5-8572.5)/88%,IF(B5>0,B5/90%,0))))))),IF(B4=2,IF(B5>438621,600000+(B5-438621)/63%,IF(B5>308621,400000+(B5-308621)/65%,IF(B5>250821,315000+(B5-250821)/68%,IF(B5>136821,165000+(B5-136821)/76%,IF(B5>68493,77400+(B5-68493)/78%,IF(B5>17145,19050+(B5-17145)/88%,IF(B5>0,B5/90%,0))))))),0))
Federal tax	=B6-B5
Withdrawal	=B1+B7
Periods / year	12
Real return every year	=IF(ISNUMBER(C10),C10,RATE(C11*B9,B8/B9,-C12,C13,0)*B9)	0.02
Years	=IF(ISNUMBER(C11),C11,NPER(C10/B9,B8/B9,-C12,C13,0)/B9)	21.5745645766804
Starting portfolio value	=IF(ISNUMBER(C12),C12,-PV(C10/B9,B11*B9,B8/B9,C13,0))	
Ending portfolio value	=IF(ISNUMBER(C13),C13,FV(C10/B9,C11*B9,B8/B9,-C12,0))	0
* Pasting may or may not work depending on your browser and spreadsheet application. On my computer it worked when I chose Paste Special and "Text" in Excel.

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Confused by Two DinkyTown Calculators

Post by LadyGeek » Sun Jul 29, 2018 10:04 am

#Cruncher wrote:
Sun Jul 29, 2018 8:56 am
To create the spreadsheet, Select All, Copy, and Paste [*] the following at cell A1 of a blank Excel sheet:
The spreadsheet code block is in tab delimited format - the tab character (0x09) is separating the cells. This is the same as a Comma Separated Value (.csv) format except the tab character is used instead of a comma. You can't see the tab characters, but they are present.

The copy-n-paste directly into LibreOffice Calc doesn't work. Use this method instead:

To create the spreadsheet, Select All, Copy, and Paste the code block into a text editor. I'm using a Linux text editor, but it should be equivalent to notepad on MS Windows. Save the text to a file.

In LibreOffice Calc, File --> Open, the text import wizard appears. Separator Options --> Separated by --> Tab. OK.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Cheego
Posts: 50
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Re: Confused by Two DinkyTown Calculators

Post by Cheego » Sun Jul 29, 2018 6:31 pm

Many thanks to LadyGeek and #Cruncher! You are both a huge asset to this forum.

I'm not able to get that latest spreadsheet to work because Excel says the taxable income calculation is too long.

I should quit while I'm ahead here but I'm struggling to make this work within a spreadsheet I've been working on for many months. I'm very hesitant to ask for more but I'm so close to finishing this retirement planner that I can't help but ask...

Would you be willing to write the sheet to do the following? That last line is the answer I'm trying to get to.

Code: Select all

Desired annual withdrawal after taxes				(user entered)
Expected real return						(user entered)
Starting portfolio value					(user entered)
Periods / year							(user entered)
"Single" or "Joint" return					(user entered)


Withdrawal							(calculated answer)
Taxable income							(calculated answer)
Federal tax							(calculated answer)
State and local tax at 7.75%					(calculated answer)

Years that expected return can be achieved			(calculated answer)

 
Last edited by Cheego on Tue Jul 31, 2018 4:32 pm, edited 1 time in total.

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

Re: Confused by Two DinkyTown Calculators

Post by #Cruncher » Tue Jul 31, 2018 4:31 pm

Cheego wrote:
Sun Jul 29, 2018 6:31 pm
The 'if' statements are causing issues within my existing spreadsheet.
Which "IF" statements, Cheego? In what cells of my last spreadsheet? (I sent you a private message two days ago with this question.)
Cheego in same post wrote:Would you be willing to write the sheet to do the following? That last line is the answer I'm trying to get to.

Code: Select all

…
State and local tax at 7.75%					(calculated answer)
Years that expected return can be achieved			(calculated answer)
Adding state income taxes to the spreadsheet from my previous post was very difficult for me. There may be an easier way, but I had to add about 35 rows that combine the federal and state tax brackets. I explain this later. But to get an answer for you, Cheego, I set the state standard deduction to $0 and the tax rate to 7.75% for every state bracket. Here is what the results look like with this change. As explained in my previous post, to have the "Years" calculated, I entered values in column C for three of the four possible items (Return, Starting Portfolio, and Ending Portfolio), and blanked the cell in column C for Years, the fourth item.

Code: Select all

Row  Col A                              Col B      Col C
  1  Single = 1 Joint = 2                   1
  2                                   Federal  ??? State
  3  Other taxable income              34,000          0
  4  Standard deduction                12,000          0
 42  Desired withdrawal less taxes    100,000
 43  Index in After Tax                    11
 44  Withdrawal pre tax               147,269
 45  Periods per year                      12
 46  Real return every year            2.000%
 47  Years                             23.942
 48  Starting portfolio value       2,800,000
 49  Ending portfolio value                 0
Here is what the results looked like when I set it up for California to test my approach. I used the standard deduction and tax brackets from the Personal finance toolbox which member FiveK often references.

Code: Select all

Row  Col A                              Col B      Col C
  1  Single = 1 Joint = 2                   1
  2                                   Federal California
  3  Other taxable income              34,000          0
  4  Standard deduction                12,000      4,236
 42  Desired withdrawal less taxes    100,000
 43  Index in After Tax                    11
 44  Withdrawal pre tax               145,384
 45  Periods per year                      12
 46  Real return every year            2.000%
 47  Years                             24.342
 48  Starting portfolio value       2,800,000
 49  Ending portfolio value                 0
To make this work, I first added rows 7 to 13 for the 2018 federal tax brackets and rows 14 to 23 for the California brackets. "Use" in Column D does two things. It selects either Single or Joint based on the assumption in cell B1, and also adjusts the brackets for "Other taxable income" and "Standard deduction" in cells B3:C4. This makes both the federal and state brackets correspond to the taxable IRA withdrawal and not to taxable income. Column A uses the Excel RANK function to rank each of the federal and state tax brackets. E.g., the 22% federal bracket (# 5 $16,700) comes between the 2% and 3% CA brackets (#4 $12,479 and #6 $23,731).

Code: Select all

Row Col A      Col B      Col C      Col D    Col E
     Rank     Single      Joint        Use     Rate
     ----     ------     ------     ------   ------
  7     1          0          0    (22,000)  10.00%
  8     2      9,525     19,050    (12,475)  12.00%
  9     5     38,700     77,400     16,700   22.00%
 10    10     82,500    165,000     60,500   24.00%
 11    11    157,500    315,000    135,500   32.00%
 12    12    200,000    400,000    178,000   35.00%
 13    15    500,000    600,000    478,000   37.00%
 14     3          0          0      4,236    1.00%
 15     4      8,223     16,446     12,459    2.00%
 16     6     19,495     38,990     23,731    3.00%
 17     7     30,769     61,538     35,005    4.00%
 18     8     42,711     85,422     46,947    8.00%
 19     9     53,980    107,960     58,216    9.30%
 20    13    275,738    551,476    279,974   10.30%
 21    14    330,884    661,768    335,120   11.30%
 22    16    551,473  1,000,000    555,709   12.30%
 23    17  1,000,000  1,074,996  1,004,236   13.30%
Rows 25 to 41 order the federal and state brackets according to their rank previously determined. The "Comb" rate in column E combines the federal and state tax rates for each of the Combined Brackets. Formulas in cells B43 and B44 use this table to back into the amount of the pre tax IRA withdrawal necessary to produce the desired After tax withdrawal the user specifies in cell B42.

Code: Select all

Row Col A     Col B    Col C   Col D   Col E    Col F    Col G
           Combined  -------- Rates --------
        #   Bracket  Federal   State    Comb     Tax  After Tax
       --   -------  -------   -----  ------    ----- ---------
 25     1   (22,000)  10.00%     n/a  10.00%        0   (22,000)
 26     2   (12,475)  12.00%     n/a  12.00%      953   (13,428)
 27     3     4,236   12.00%   1.00%  13.00%    2,958     1,278 
 28     4    12,459   12.00%   2.00%  14.00%    4,027     8,432 
 29     5    16,700   22.00%   2.00%  24.00%    4,621    12,079 
 30     6    23,731   22.00%   3.00%  25.00%    6,308    17,423 
 31     7    35,005   22.00%   4.00%  26.00%    9,126    25,879 
 32     8    46,947   22.00%   8.00%  30.00%   12,231    34,716 
 33     9    58,216   22.00%   9.30%  31.30%   15,612    42,604 
 34    10    60,500   24.00%   9.30%  33.30%   16,327    44,173 
 35    11   135,500   32.00%   9.30%  41.30%   41,302    94,198 
 36    12   178,000   35.00%   9.30%  44.30%   58,855   119,145 
 37    13   279,974   35.00%  10.30%  45.30%  104,029   175,945 
 38    14   335,120   35.00%  11.30%  46.30%  129,010   206,110 
 39    15   478,000   37.00%  11.30%  48.30%  195,164   282,836 
 40    16   555,709   37.00%  12.30%  49.30%  232,697   323,012 
 41    17 1,004,236   37.00%  13.30%  50.30%  453,821   550,415
For those wishing to use my new spreadsheet, first Select All, Copy, and Paste the following at cell A1 of a blank Excel sheet. (If you have problems pasting, refer to LadyGeek's post explaining an alternate method.)

Code: Select all

Single = 1 Joint = 2	1
	Federal	California
Other taxable income	34000	0
Standard deduction	12000	4236
		Bracket		
Rank	Single	Joint	Use	Rate
=RANK(D7,D$7:D$23,1)	0	0	=IF(B$1=1,B7,C7)-B$3+B$4	0.1
=RANK(D8,D$7:D$23,1)	9525	19050	=IF(B$1=1,B8,C8)-B$3+B$4	0.12
=RANK(D9,D$7:D$23,1)	38700	77400	=IF(B$1=1,B9,C9)-B$3+B$4	0.22
=RANK(D10,D$7:D$23,1)	82500	165000	=IF(B$1=1,B10,C10)-B$3+B$4	0.24
=RANK(D11,D$7:D$23,1)	157500	315000	=IF(B$1=1,B11,C11)-B$3+B$4	0.32
=RANK(D12,D$7:D$23,1)	200000	400000	=IF(B$1=1,B12,C12)-B$3+B$4	0.35
=RANK(D13,D$7:D$23,1)	500000	600000	=IF(B$1=1,B13,C13)-B$3+B$4	0.37
=RANK(D14,D$7:D$23,1)	0	0	=IF(B$1=1,B14,C14)-C$3+C$4	0.01
=RANK(D15,D$7:D$23,1)	8223	16446	=IF(B$1=1,B15,C15)-C$3+C$4	0.02
=RANK(D16,D$7:D$23,1)	19495	38990	=IF(B$1=1,B16,C16)-C$3+C$4	0.03
=RANK(D17,D$7:D$23,1)	30769	61538	=IF(B$1=1,B17,C17)-C$3+C$4	0.04
=RANK(D18,D$7:D$23,1)	42711	85422	=IF(B$1=1,B18,C18)-C$3+C$4	0.08
=RANK(D19,D$7:D$23,1)	53980	107960	=IF(B$1=1,B19,C19)-C$3+C$4	0.093
=RANK(D20,D$7:D$23,1)	275738	551476	=IF(B$1=1,B20,C20)-C$3+C$4	0.103
=RANK(D21,D$7:D$23,1)	330884	661768	=IF(B$1=1,B21,C21)-C$3+C$4	0.113
=RANK(D22,D$7:D$23,1)	551473	1000000	=IF(B$1=1,B22,C22)-C$3+C$4	0.123
=RANK(D23,D$7:D$23,1)	1000000	1074996	=IF(B$1=1,B23,C23)-C$3+C$4	0.133
	Bracket	Federal	State	Combined	Tax	After Tax
1	=VLOOKUP($A25,$A$7:$E$23,4,FALSE)	=VLOOKUP($B25,$D$7:$E$13,2,TRUE)	=VLOOKUP($B25,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C25),0,C25)+IF(ISERROR(D25),0,D25)	0	=B25-F25
=A25+1	=VLOOKUP($A26,$A$7:$E$23,4,FALSE)	=VLOOKUP($B26,$D$7:$E$13,2,TRUE)	=VLOOKUP($B26,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C26),0,C26)+IF(ISERROR(D26),0,D26)	=F25+E25*(B26-B25)	=B26-F26
=A26+1	=VLOOKUP($A27,$A$7:$E$23,4,FALSE)	=VLOOKUP($B27,$D$7:$E$13,2,TRUE)	=VLOOKUP($B27,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C27),0,C27)+IF(ISERROR(D27),0,D27)	=F26+E26*(B27-B26)	=B27-F27
=A27+1	=VLOOKUP($A28,$A$7:$E$23,4,FALSE)	=VLOOKUP($B28,$D$7:$E$13,2,TRUE)	=VLOOKUP($B28,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C28),0,C28)+IF(ISERROR(D28),0,D28)	=F27+E27*(B28-B27)	=B28-F28
=A28+1	=VLOOKUP($A29,$A$7:$E$23,4,FALSE)	=VLOOKUP($B29,$D$7:$E$13,2,TRUE)	=VLOOKUP($B29,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C29),0,C29)+IF(ISERROR(D29),0,D29)	=F28+E28*(B29-B28)	=B29-F29
=A29+1	=VLOOKUP($A30,$A$7:$E$23,4,FALSE)	=VLOOKUP($B30,$D$7:$E$13,2,TRUE)	=VLOOKUP($B30,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C30),0,C30)+IF(ISERROR(D30),0,D30)	=F29+E29*(B30-B29)	=B30-F30
=A30+1	=VLOOKUP($A31,$A$7:$E$23,4,FALSE)	=VLOOKUP($B31,$D$7:$E$13,2,TRUE)	=VLOOKUP($B31,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C31),0,C31)+IF(ISERROR(D31),0,D31)	=F30+E30*(B31-B30)	=B31-F31
=A31+1	=VLOOKUP($A32,$A$7:$E$23,4,FALSE)	=VLOOKUP($B32,$D$7:$E$13,2,TRUE)	=VLOOKUP($B32,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C32),0,C32)+IF(ISERROR(D32),0,D32)	=F31+E31*(B32-B31)	=B32-F32
=A32+1	=VLOOKUP($A33,$A$7:$E$23,4,FALSE)	=VLOOKUP($B33,$D$7:$E$13,2,TRUE)	=VLOOKUP($B33,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C33),0,C33)+IF(ISERROR(D33),0,D33)	=F32+E32*(B33-B32)	=B33-F33
=A33+1	=VLOOKUP($A34,$A$7:$E$23,4,FALSE)	=VLOOKUP($B34,$D$7:$E$13,2,TRUE)	=VLOOKUP($B34,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C34),0,C34)+IF(ISERROR(D34),0,D34)	=F33+E33*(B34-B33)	=B34-F34
=A34+1	=VLOOKUP($A35,$A$7:$E$23,4,FALSE)	=VLOOKUP($B35,$D$7:$E$13,2,TRUE)	=VLOOKUP($B35,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C35),0,C35)+IF(ISERROR(D35),0,D35)	=F34+E34*(B35-B34)	=B35-F35
=A35+1	=VLOOKUP($A36,$A$7:$E$23,4,FALSE)	=VLOOKUP($B36,$D$7:$E$13,2,TRUE)	=VLOOKUP($B36,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C36),0,C36)+IF(ISERROR(D36),0,D36)	=F35+E35*(B36-B35)	=B36-F36
=A36+1	=VLOOKUP($A37,$A$7:$E$23,4,FALSE)	=VLOOKUP($B37,$D$7:$E$13,2,TRUE)	=VLOOKUP($B37,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C37),0,C37)+IF(ISERROR(D37),0,D37)	=F36+E36*(B37-B36)	=B37-F37
=A37+1	=VLOOKUP($A38,$A$7:$E$23,4,FALSE)	=VLOOKUP($B38,$D$7:$E$13,2,TRUE)	=VLOOKUP($B38,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C38),0,C38)+IF(ISERROR(D38),0,D38)	=F37+E37*(B38-B37)	=B38-F38
=A38+1	=VLOOKUP($A39,$A$7:$E$23,4,FALSE)	=VLOOKUP($B39,$D$7:$E$13,2,TRUE)	=VLOOKUP($B39,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C39),0,C39)+IF(ISERROR(D39),0,D39)	=F38+E38*(B39-B38)	=B39-F39
=A39+1	=VLOOKUP($A40,$A$7:$E$23,4,FALSE)	=VLOOKUP($B40,$D$7:$E$13,2,TRUE)	=VLOOKUP($B40,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C40),0,C40)+IF(ISERROR(D40),0,D40)	=F39+E39*(B40-B39)	=B40-F40
=A40+1	=VLOOKUP($A41,$A$7:$E$23,4,FALSE)	=VLOOKUP($B41,$D$7:$E$13,2,TRUE)	=VLOOKUP($B41,$D$14:$E$23,2,TRUE)	=IF(ISERROR(C41),0,C41)+IF(ISERROR(D41),0,D41)	=F40+E40*(B41-B40)	=B41-F41
Withdrawal less taxes	100000
Index in After Tax	=MATCH(B42,G25:G41,1)
Withdrawal pre tax	=INDEX(B25:B41,B43,1)+(B42-INDEX(G25:G41,B43,1))/(1-INDEX(E25:E41,B43,1))
Periods per year	12
Real return every year	=IF(ISNUMBER(C46),C46,RATE(C47*B45,B44/B45,-C48,C49,0)*B45)	0.02
Years	=IF(ISNUMBER(C47),C47,NPER(C46/B45,B44/B45,-C48,C49,0)/B45)	
Starting portfolio value	=IF(ISNUMBER(C48),C48,-PV(C46/B45,B47*B45,B44/B45,C49,0))	2800000
Ending portfolio value	=IF(ISNUMBER(C49),C49,FV(C46/B45,C47*B45,B44/B45,-C48,0))	0
To modify for another state, replace the standard deduction in cell C4 and the tax brackets in cells B14:C23 and E14:E23. To use a single rate (as Cheego does) there is no need to change the bracket amounts. It's only necessary to replace every rate in cells E14:E23 with the single rate.

Cheego
Posts: 50
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Re: Confused by Two DinkyTown Calculators

Post by Cheego » Tue Jul 31, 2018 6:12 pm

Lots going on here. First let me again thank you for what you've done. Few people on any forum are so incredibly good at this and so willing to help.

I apologize for not seeing that PM you sent. To answer your question... it was the IF statement on the federal tax calculation line that was causing the issue. Excel abbreviates the whole formula and then throws an error. When you let Excel "fix" the error, it drops the "2" (Joint) conditional output. Open Office Calc allows the IF statement but won't produce a value (I get Error 508 - Pair missing).

I also want to apologize for not being clear in what I was trying to convey. My lack of specifics caused you to have to do a lot of work. When I wrote "State and local tax at 7.75%", I meant that is the combined state and local tax rate for where I live so I was just trying to get one number (7.75%) plugged in there to calculate the state and local taxes combined.

Next, I should mention that I edited my post yesterday as I was previously mistaken about the calc I was trying to achieve.

There is no doubt that what you wrote is exceptional. I can almost use what you previously posted without the "2" condition and I understand how it works where you leave a field blank to get an answer. That is great. But what I'm looking for is a calculation that simply outputs the number of years an investment will return a specified dollar amount (after fed, state, and local taxes) given a real return percentage and whether the taxes are filed joint or separate. I know your calc will do that but I'd like to get it without the leave-whichever-field-blank option for an answer.

Actually, I don't even need the single federal filing option. I'm planning for a joint filing for a very long time. To make it easier, I also don't need the "ending portfolio value", "Other taxable income" and "Tax deduction" lines.

Hope this is not asking too much or making this more confusing.

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

Re: Confused by Two DinkyTown Calculators

Post by #Cruncher » Thu Aug 02, 2018 10:21 am

Cheego wrote:
Sun Jul 29, 2018 6:31 pm
I'm not able to get that latest spreadsheet to work because Excel says the taxable income calculation is too long.
[Cheego is referring to an older version of my spreadsheet shown in this post.] Sorry about that. Even though the line is quite long and has several nested IF functions, it worked OK when pasted into either Excel 2016 on my Windows PC or Excel 2011 on my Mac. It might work for you if you split it into two parts for single and joint returns, and then choose one of them in a third line. The following worked for me when I pasted the three formulas into cell B6 (after inserting several blank rows following "After tax income" on row 5).

Code: Select all

=IF(B5>349310.5,500000+(B5-349310.5)/63%,IF(B5>154310.5,200000+(B5-154310.5)/65%,IF(B5>125410.5,157500+(B5-125410.5)/68%,IF(B5>68410.5,82500+(B5-68410.5)/76%,IF(B5>34246.5,38700+(B5-34246.5)/78%,IF(B5>8572.5,9525+(B5-8572.5)/88%,IF(B5>0,B5/90%,0)))))))
=IF(B5>438621,600000+(B5-438621)/63%,IF(B5>308621,400000+(B5-308621)/65%,IF(B5>250821,315000+(B5-250821)/68%,IF(B5>136821,165000+(B5-136821)/76%,IF(B5>68493,77400+(B5-68493)/78%,IF(B5>17145,19050+(B5-17145)/88%,IF(B5>0,B5/90%,0)))))))
=IF(B4=1,B6,B7)
But this is really academic if you use the revised spreadsheet from my last post. Instead of embedding the federal tax brackets and rates into a gargantuan formula, it uses much shorter Excel table lookup functions. (I had to do this since the brackets and rates now refer to combined federal and state brackets that change depending on the state brackets and also on the user's choice of Other taxable income and deductions & exemptions.)

Cheego wrote:
Tue Jul 31, 2018 6:12 pm
When I wrote "State and local tax at 7.75%", I meant that is the combined state and local tax rate for where I live so I was just trying to get one number (7.75%) plugged in there to calculate the state and local taxes combined.
According to the State Brackets sheet of the Personal finance toolbox there are ten states with a non-graduated income tax rate. None of these have a rate of 7.75%, however. Are you in one of these states with an additional non-graduated local income tax rate that brings the total up to 7.75%? [1]

Code: Select all

     Deduct & Exemp   -- Tax Rate --
     Single   Joint   Single   Joint
     ------   -----   ------   -----
CO        0	  0    4.63%   4.63%
IL    2,000   4,000    4.95%   4.95%
IN    1,000   2,000    3.23%   3.23%
MA    4,400   8,800    5.10%   5.10%
MI    4,000   8,000    4.25%   4.25%
NH    2,400   4,800    5.00%   5.00%
NC    8,750  17,500    5.50%   5.50%
PA        0       0    3.07%   3.07%
TN    1,250   2,500    3.00%   3.00%
UT    3,894   6,931    5.00%   5.00%
Cheego in same post wrote:But what I'm looking for is a calculation that simply outputs the number of years an investment will return a specified dollar amount ... I know your calc will do that but I'd like to get it without the leave-whichever-field-blank option for an answer.
You may only want to know the number of years the money will last. But others might want to know the necessary rate of return, the necessary starting balance, or the resulting ending balance given assumptions for two of the other three along with an assumption for the number of years . I'm not going to reduce the spreadsheet's flexibility to also do these things. But it's pretty easy to do yourself. Just enter your desired assumptions for rate of return. starting balance, and ending balance in column B. Then change the formula for years as follows:

Code: Select all

From: =IF(ISNUMBER(C47),C47,NPER(C46/B45,B44/B45,-C48,C49,0)/B45)
To:   =                     NPER(B46/B45,B44/B45,-B48,B49,0)/B45
Cheego in same post wrote:Actually, I don't even need the single federal filing option. I'm planning for a joint filing for a very long time. To make it easier, I also don't need the "ending portfolio value", "Other taxable income" and "Tax deduction" lines.
Again, Cheego, you might not need these things. [2] But I'm not going to hard code a $0 ending portfolio, $0 other taxable income, a standard federal deduction, and a $0 state deduction. Having these be variables makes the spreadsheet more flexible for others.

  1. Cheego, I sent you a private message 1-1/2 days ago asking what state and locality you pay income taxes for, but have not heard back from you. This is the second PM to you with a delayed response. Have you checked the box under User Control Panel / Board Preferences / Edit Notification options to email you when someone sends you a PM?
  2. While certainly possible, it would be unusual for any retired person or couple not to have taxable income besides withdrawals from a retirement account (e.g., Social security, interest, or dividends). The spreadsheet needs an assumption for this (along with deductions & exemptions) in order to back into the pre tax withdrawal that will produce the desired amount after subtracting federal and state income taxes.

Cheego
Posts: 50
Joined: Mon Jul 18, 2016 11:57 am
Location: Greenville, SC

Re: Confused by Two DinkyTown Calculators

Post by Cheego » Thu Aug 02, 2018 4:45 pm

This just got way over my head. I'm bailing out. I appreciate your help though.
Months of spreadsheet work down the drain.

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

Re: Confused by Two DinkyTown Calculators

Post by #Cruncher » Sat Aug 04, 2018 10:58 am

I've modified the state tax table portion of the spreadsheet shown in this post for a taxpayer living in a county in Maryland. Maryland residents' income tax is based on the combination of a graduated tax table for the state and these local tax rates. Maryland also has a standard deduction and exemptions. The exemptions are subject to a phase-out which I explain below in a footnote. [*] I also made the following changes to the spreadsheet:
  • Separate deduction and exemptions into two lines.
  • At the bottom add a check of the tax calculation to confirm that my method of backing into the amount of retirement withdrawal before taxes is correct. To facilitate this I added two columns to the tax bracket tables showing the total tax up to each bracket floor.
Here's an example. Row 41 shows that for the couple to have $100,000 after taxes they must withdraw $133,159 before taxes. Row 44 shows this can be done for a little over 27 years given a 2% real return, $2,800,000 starting portolio value, and $0 ending portfolio value. The new section at the end shows that the $33,159 total federal and Maryland tax does equal the difference between the $100,000 after tax and $133,159 before tax amounts.

Code: Select all

Row  Col A                              Col B      Col C
  1  Single = 1 Joint = 2                   2
  2                                   Federal   Maryland
  3  Other taxable income              34,000          0
  4  Deduction                         24,000      4,000
  5  Exemptions                             0      3,200

 39  Desired withdrawal after taxes   100,000
 40  Index in after tax table               7
 41  Withdrawal before taxes          133,159
 42  Periods per year                      12
 43  Real return every year            2.000%
 44  Years funds last                  27.307

Code: Select all

Row  Col A                              Col B      Col C
 45  Starting portfolio value       2,800,000
 46  Ending portfolio value                 0

 47  Check tax calculation:           Federal   Maryland
 48  Adjusted gross income            167,159    133,159
 49  Taxable income                   143,159    125,959
 50  Bracket #                              3          4
 51  Bracket floor                     77,400      3,000
 52  Tax up to bracket                  8,907        182
 53  Rate on excess                    22.00%      7.81%
 54  Total tax                         23,374      9,785
 55  Federal + Maryland                33,159
To use this spreadsheet, Select All, Copy, and Paste the following at cell A1 of a blank spreadsheet.

Code: Select all

Single = 1 Joint = 2	2
	Federal	Maryland
Other taxable income	34000	0
Deduction	24000	4000
Exemptions	0	3200
		Bracket			Tax up to bracket	
Rank	Single	Joint	Use	Rate	Single	Joint
=RANK(D8,D$8:D$22,1)	0	0	=IF(B$1=1,B8,C8)-B$3+B$4+B$5	0.1	0	0
=RANK(D9,D$8:D$22,1)	9525	19050	=IF(B$1=1,B9,C9)-B$3+B$4+B$5	0.12	=F8+$E8*(B9-B8)	=G8+$E8*(C9-C8)
=RANK(D10,D$8:D$22,1)	38700	77400	=IF(B$1=1,B10,C10)-B$3+B$4+B$5	0.22	=F9+$E9*(B10-B9)	=G9+$E9*(C10-C9)
=RANK(D11,D$8:D$22,1)	82500	165000	=IF(B$1=1,B11,C11)-B$3+B$4+B$5	0.24	=F10+$E10*(B11-B10)	=G10+$E10*(C11-C10)
=RANK(D12,D$8:D$22,1)	157500	315000	=IF(B$1=1,B12,C12)-B$3+B$4+B$5	0.32	=F11+$E11*(B12-B11)	=G11+$E11*(C12-C11)
=RANK(D13,D$8:D$22,1)	200000	400000	=IF(B$1=1,B13,C13)-B$3+B$4+B$5	0.35	=F12+$E12*(B13-B12)	=G12+$E12*(C13-C12)
=RANK(D14,D$8:D$22,1)	500000	600000	=IF(B$1=1,B14,C14)-B$3+B$4+B$5	0.37	=F13+$E13*(B14-B13)	=G13+$E13*(C14-C13)
=RANK(D15,D$8:D$22,1)	0	0	=IF(B$1=1,B15,C15)-C$3+C$4+C$5	0.0506	0	0
=RANK(D16,D$8:D$22,1)	1000	1000	=IF(B$1=1,B16,C16)-C$3+C$4+C$5	0.0606	=F15+$E15*(B16-B15)	=G15+$E15*(C16-C15)
=RANK(D17,D$8:D$22,1)	2000	2000	=IF(B$1=1,B17,C17)-C$3+C$4+C$5	0.0706	=F16+$E16*(B17-B16)	=G16+$E16*(C17-C16)
=RANK(D18,D$8:D$22,1)	3000	3000	=IF(B$1=1,B18,C18)-C$3+C$4+C$5	0.0781	=F17+$E17*(B18-B17)	=G17+$E17*(C18-C17)
=RANK(D19,D$8:D$22,1)	100000	150000	=IF(B$1=1,B19,C19)-C$3+C$4+C$5	0.0806	=F18+$E18*(B19-B18)	=G18+$E18*(C19-C18)
=RANK(D20,D$8:D$22,1)	125000	175000	=IF(B$1=1,B20,C20)-C$3+C$4+C$5	0.0831	=F19+$E19*(B20-B19)	=G19+$E19*(C20-C19)
=RANK(D21,D$8:D$22,1)	150000	225000	=IF(B$1=1,B21,C21)-C$3+C$4+C$5	0.0856	=F20+$E20*(B21-B20)	=G20+$E20*(C21-C20)
=RANK(D22,D$8:D$22,1)	250000	300000	=IF(B$1=1,B22,C22)-C$3+C$4+C$5	0.0881	=F21+$E21*(B22-B21)	=G21+$E21*(C22-C21)
	Bracket	Federal	State	Total	Tax	After Tax
1	=VLOOKUP($A24,$A$8:$E$22,4,FALSE)	=VLOOKUP($B24,$D$8:$E$14,2,TRUE)	=VLOOKUP($B24,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C24),0,C24)+IF(ISERROR(D24),0,D24)	0	=B24-F24
=A24+1	=VLOOKUP($A25,$A$8:$E$22,4,FALSE)	=VLOOKUP($B25,$D$8:$E$14,2,TRUE)	=VLOOKUP($B25,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C25),0,C25)+IF(ISERROR(D25),0,D25)	=F24+E24*(B25-B24)	=B25-F25
=A25+1	=VLOOKUP($A26,$A$8:$E$22,4,FALSE)	=VLOOKUP($B26,$D$8:$E$14,2,TRUE)	=VLOOKUP($B26,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C26),0,C26)+IF(ISERROR(D26),0,D26)	=F25+E25*(B26-B25)	=B26-F26
=A26+1	=VLOOKUP($A27,$A$8:$E$22,4,FALSE)	=VLOOKUP($B27,$D$8:$E$14,2,TRUE)	=VLOOKUP($B27,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C27),0,C27)+IF(ISERROR(D27),0,D27)	=F26+E26*(B27-B26)	=B27-F27
=A27+1	=VLOOKUP($A28,$A$8:$E$22,4,FALSE)	=VLOOKUP($B28,$D$8:$E$14,2,TRUE)	=VLOOKUP($B28,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C28),0,C28)+IF(ISERROR(D28),0,D28)	=F27+E27*(B28-B27)	=B28-F28
=A28+1	=VLOOKUP($A29,$A$8:$E$22,4,FALSE)	=VLOOKUP($B29,$D$8:$E$14,2,TRUE)	=VLOOKUP($B29,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C29),0,C29)+IF(ISERROR(D29),0,D29)	=F28+E28*(B29-B28)	=B29-F29
=A29+1	=VLOOKUP($A30,$A$8:$E$22,4,FALSE)	=VLOOKUP($B30,$D$8:$E$14,2,TRUE)	=VLOOKUP($B30,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C30),0,C30)+IF(ISERROR(D30),0,D30)	=F29+E29*(B30-B29)	=B30-F30
=A30+1	=VLOOKUP($A31,$A$8:$E$22,4,FALSE)	=VLOOKUP($B31,$D$8:$E$14,2,TRUE)	=VLOOKUP($B31,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C31),0,C31)+IF(ISERROR(D31),0,D31)	=F30+E30*(B31-B30)	=B31-F31
=A31+1	=VLOOKUP($A32,$A$8:$E$22,4,FALSE)	=VLOOKUP($B32,$D$8:$E$14,2,TRUE)	=VLOOKUP($B32,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C32),0,C32)+IF(ISERROR(D32),0,D32)	=F31+E31*(B32-B31)	=B32-F32
=A32+1	=VLOOKUP($A33,$A$8:$E$22,4,FALSE)	=VLOOKUP($B33,$D$8:$E$14,2,TRUE)	=VLOOKUP($B33,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C33),0,C33)+IF(ISERROR(D33),0,D33)	=F32+E32*(B33-B32)	=B33-F33
=A33+1	=VLOOKUP($A34,$A$8:$E$22,4,FALSE)	=VLOOKUP($B34,$D$8:$E$14,2,TRUE)	=VLOOKUP($B34,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C34),0,C34)+IF(ISERROR(D34),0,D34)	=F33+E33*(B34-B33)	=B34-F34
=A34+1	=VLOOKUP($A35,$A$8:$E$22,4,FALSE)	=VLOOKUP($B35,$D$8:$E$14,2,TRUE)	=VLOOKUP($B35,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C35),0,C35)+IF(ISERROR(D35),0,D35)	=F34+E34*(B35-B34)	=B35-F35
=A35+1	=VLOOKUP($A36,$A$8:$E$22,4,FALSE)	=VLOOKUP($B36,$D$8:$E$14,2,TRUE)	=VLOOKUP($B36,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C36),0,C36)+IF(ISERROR(D36),0,D36)	=F35+E35*(B36-B35)	=B36-F36
=A36+1	=VLOOKUP($A37,$A$8:$E$22,4,FALSE)	=VLOOKUP($B37,$D$8:$E$14,2,TRUE)	=VLOOKUP($B37,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C37),0,C37)+IF(ISERROR(D37),0,D37)	=F36+E36*(B37-B36)	=B37-F37
=A37+1	=VLOOKUP($A38,$A$8:$E$22,4,FALSE)	=VLOOKUP($B38,$D$8:$E$14,2,TRUE)	=VLOOKUP($B38,$D$15:$E$22,2,TRUE)	=IF(ISERROR(C38),0,C38)+IF(ISERROR(D38),0,D38)	=F37+E37*(B38-B37)	=B38-F38
Desired withdrawal after taxes	100000
Index in after tax table	=MATCH(B39,G24:G38,1)
Withdrawal before taxes	=INDEX(B24:B38,B40,1)+(B39-INDEX(G24:G38,B40,1))/(1-INDEX(E24:E38,B40,1))
Periods per year	12
Real return every year	=IF(ISNUMBER(C43),C43,RATE(C44*B42,B41/B42,-C45,C46,0)*B42)	0.02
Years funds last	=IF(ISNUMBER(C44),C44,NPER(C43/B42,B41/B42,-C45,C46,0)/B42)	
Starting portfolio value	=IF(ISNUMBER(C45),C45,-PV(C43/B42,B44*B42,B41/B42,C46,0))	2800000
Ending portfolio value	=IF(ISNUMBER(C46),C46,FV(C43/B42,C44*B42,B41/B42,-C45,0))	0
Check tax calculation:	Federal	=C2
Adjusted gross income	=$B41+B3	=$B41+C3
Taxable income	=B48-B4-B5	=C48-C4-C5
Bracket #	=MATCH(B49,IF($B1=1,B8:B14,C8:C14),1)	=MATCH(C49,IF($B1=1,B15:B22,C15:C22),1)
Bracket floor	=INDEX(B8:C14,B$50,$B$1)	=INDEX(B15:C22,C$50,$B$1)
Tax up to bracket	=INDEX(F8:G14,B$50,$B$1)	=INDEX(F15:G22,C$50,$B$1)
Rate on excess	=INDEX(E8:E14,B$50,1)	=INDEX(E15:E22,C$50,1)
Total tax	=B52+B53*(B49-B51)	=C52+C53*(C49-C51)
="Federal + "&C2	=B54+C54
* As explained in section 10 Exemptions of the form 502 instructions the personal exemption phases out as follows based on Adjusted Gross Income from the federal return. In the example above this falls in the $150K - $175K range, so I entered $3,200 (2 X $1,600) in cell C5.

Code: Select all

  From       To     Single    Joint
-------   -------   ------    -----
      0   100,000    3,200    3,200 
100,000   125,000    1,600    3,200 
125,000   150,000      800    3,200 
150,000   175,000        0    1,600 
175,000   200,000        0      800 
200,000                  0        0

Post Reply