Confused about several calculations

Post questions about how to use the planner, user inputs, how the planner works, and comments and suggestions.
Post Reply
sphinx13
Posts: 3
Joined: Sat Jan 02, 2021 2:18 am

Confused about several calculations

Post by sphinx13 »

I started out doing some projections using Google sheets and then after discovering FRP decided to try it out.

Problem is I don't seem to be able to reconcile the numbers that FRP spits out with what I understand to be correct from running the same numbers through my own sheet.

This is the input that I used with FRP

Current Age: 76
Retirement Age: 76
Life Expectancy 95

Inflation - Average 2.0%
Inflation - Std Dev 0.0%

Investment Tax Rate 20%
Income Tax Rate 37%

Taxable Portfolio Value $1,000,000
Tax Deferred Portfolio Value $5,000,000

Return - Average: 8.0%
Return - Std Dev 0.0%

Annual Retirement Spending $50,000

First up, the RMD

Using FRP, my first year total withdrawal is reported as $134,091
frp.PNG
If I use the IRS RMD Worksheet https://www.irs.gov/pub/irs-tege/uniform_rmd_wksht.pdf I see that I my distribution period is 22 (age 76). $5,000,000 / 22 = $227,272.73 which is a long ways off from what FRP provides.

Second up, the Return Average

According to FRP my deferred portfolio value in 2021 is reported as $5,053,476.00, and in 2022 is reported as $5,098,346.00.

If you take into account the withdrawal that is reported for 2021 ($134,091) we can calculate the rate of return for that year.

($5,053,476.00 - $134,091)*(1 + r) = $5,098,346.00

When I solve for r I get ~3.6% which is a long ways off from the 8% that I used as an input.

I'm sure I'm missing something, but I'm quite confused as to what it is.


Thanks!
jimr
Posts: 821
Joined: Thu Feb 28, 2008 6:48 pm

Re: Confused about several calculations

Post by jimr »

First up, I'd suggest clicking the "show more detail" radio button on the top right of the detailed view window to show some extra columns, including some with the exact RMD details. You can also right-click on any column header to get a menu with an option to show even more columns.

The first year $134k total withdrawal number reflects the $50k withdrawn for spending plus an extra $84k of withdrawal to pay the taxes due on the $227k RMD. The part of the RMD that is reinvested into the taxable portfolio isn't counted as part of the total withdrawal.

Next up rate of return. It looks like the RMD for the second year would be $238k. That turns your return calculation into:
($5,053,476.00 - $238,000)*(1 + r) = $5,098,346.00 -> r = 5.9%

All of the amounts displayed in the year-to-year details view table are shown in present value (2021) dollars, so the r above is a real return, not a nominal return. If we convert the 8% nominal return to a real return we get:
real return = (1 + nominal return) / (1 + inflation) - 1 = (1.08/1.02) - 1 = 5.9%

It's early and I may have messed up the calculations, but it seems like everything squares up. If not, please don't hesitate to keep the questions coming.
sphinx13
Posts: 3
Joined: Sat Jan 02, 2021 2:18 am

Re: Confused about several calculations

Post by sphinx13 »

Ahh yes, I missed that radio button!

This explains everything, thanks Jim!

In my own sheet, I had been using inflation to forecast an increased cost of living (expenses), but had not factored it into the forecast value of my portfolio.

With FRP, it seems like the only way to get a forecast of the portfolio value in future dollars is to include an inflation rate of 0%, but then my expenses don't increase year to year like I'd like.

Is there any better way to view the portfolio value in future dollars?
jimr
Posts: 821
Joined: Thu Feb 28, 2008 6:48 pm

Re: Confused about several calculations

Post by jimr »

There isn't a way to that within the planner. However, you could export the detailed view table (right click on any cell and choose copy table to clipboard), then bring the data into excel (paste clipboard). Then you could manually adjust any amounts you'd like to adjust.
sphinx13
Posts: 3
Joined: Sat Jan 02, 2021 2:18 am

Re: Confused about several calculations

Post by sphinx13 »

sweet, thanks!
Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 28 guests