C. B. Zeller Posted February 24, 2022 Share Posted February 24, 2022 I put together a spreadsheet that will calculate single life and 100% J&S APRs. This was inspired by comments from @401king and others in another recent thread. You must enter the interest rate and age on the Input tab. You can optionally enter an account balance, if you do it will calculate the annuity amounts in addition to the APRs. Important: you must enter the values from the correct mortality table on the "Mortality" tab. The mortality tables are published by the IRS, for example the 2021 table is here: https://www.irs.gov/pub/irs-drop/n-19-67.pdf Use the values in the column labeled, For distributions subject to 417(e). If someone wants to enhance this workbook to automatically pull the 417(e) tables, or the 10-year CMT rates, that would be fantastic. Use this workbook at your own risk. I believe it will generate correct results based on the inputs but I can not be responsible if it fails in some cases. I can not promise that it will not immediately delete all your files and melt your CPU the second you open it, either. Treat it like any other file you would download from an anonymous internet stranger. APR calculator.xlsx austin3515 1 Free advice is worth what you paid for it. Do not rely on the information provided in this post for any purpose, including (but not limited to): tax planning, compliance with ERISA or the IRC, investing or other forms of fortune-telling, bird identification, relationship advice, or spiritual guidance. Corey B. Zeller, MSEA, CPC, QPA, QKA Preferred Pension Planning Corp.corey@pppc.co Link to comment Share on other sites More sharing options...
david rigby Posted February 24, 2022 Share Posted February 24, 2022 Always talk to your actuary. I'm a retirement actuary. Nothing about my comments is intended or should be construed as investment, tax, legal or accounting advice. Occasionally, but not all the time, it might be reasonable to interpret my comments as actuarial or consulting advice. Link to comment Share on other sites More sharing options...
C. B. Zeller Posted February 24, 2022 Author Share Posted February 24, 2022 1 hour ago, david rigby said: Always talk to your actuary. Agreed, if you have an actuary available. With the lifetime income disclosure rules, some people might now be required to calculate present value factors who have not previously needed the services of an actuary. Free advice is worth what you paid for it. Do not rely on the information provided in this post for any purpose, including (but not limited to): tax planning, compliance with ERISA or the IRC, investing or other forms of fortune-telling, bird identification, relationship advice, or spiritual guidance. Corey B. Zeller, MSEA, CPC, QPA, QKA Preferred Pension Planning Corp.corey@pppc.co Link to comment Share on other sites More sharing options...
CuseFan Posted February 24, 2022 Share Posted February 24, 2022 3 hours ago, C. B. Zeller said: I can not promise that it will not immediately delete all your files and melt your CPU the second you open it Are you from Russia and asking for a Bitcoin ransom after all that happens? Just want to make sure we're not indirectly funding the war in Ukraine by opening this. Kenneth M. Prell, CEBS, ERPA Vice President, BPAS Actuarial & Pension Services kprell@bpas.com Link to comment Share on other sites More sharing options...
C. B. Zeller Posted February 25, 2022 Author Share Posted February 25, 2022 7 hours ago, CuseFan said: Are you from Russia and asking for a Bitcoin ransom after all that happens? Just want to make sure we're not indirectly funding the war in Ukraine by opening this. The only war I'm fighting is the one in my email inbox... CuseFan 1 Free advice is worth what you paid for it. Do not rely on the information provided in this post for any purpose, including (but not limited to): tax planning, compliance with ERISA or the IRC, investing or other forms of fortune-telling, bird identification, relationship advice, or spiritual guidance. Corey B. Zeller, MSEA, CPC, QPA, QKA Preferred Pension Planning Corp.corey@pppc.co Link to comment Share on other sites More sharing options...
CuseFan Posted February 25, 2022 Share Posted February 25, 2022 Yeah that one never ends! Kenneth M. Prell, CEBS, ERPA Vice President, BPAS Actuarial & Pension Services kprell@bpas.com Link to comment Share on other sites More sharing options...
austin3515 Posted May 12, 2022 Share Posted May 12, 2022 On 2/24/2022 at 10:21 AM, C. B. Zeller said: If someone wants to enhance this workbook to automatically pull the 417(e) tables, or the 10-year CMT rates, that would be fantastic How often are these updated? And your saying the 417e table is just straight from the notice? And the CMT rate is just the 1 interest rate, right (the 1.20%)? Where would you get that? I wrote an Excel loan program that automatically pulls in the prime rate from a website. I forgot what the feature is called but the functionality is in Excel to download the contents of a webpage, and then you just have to use a little trickery to get things where you want them to be... If you let me know where to find this, I'd try and automate it, because I personally see a lot of value in being ablet o calculate these things in Excel or in my case Access. But being able to calculate these APR's is the hurdle. Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
Bri Posted May 13, 2022 Share Posted May 13, 2022 I found a daily update to the 10-year CMT rate on what seems to be the St. Louis branch of the Fed's website. But yeah, you'd update the applicable mortality table annually, and pull the CMT rate as of the first of each month as appropriate, and a spreadsheet can do the calculations. Link to comment Share on other sites More sharing options...
austin3515 Posted May 13, 2022 Share Posted May 13, 2022 25 minutes ago, Bri said: I found a daily update to the 10-year CMT rate on what seems to be the St. Louis branch of the Fed's website. A link perhaps? Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
Bri Posted May 13, 2022 Share Posted May 13, 2022 https://fred.stlouisfed.org/series/DGS10 Link to comment Share on other sites More sharing options...
C. B. Zeller Posted May 13, 2022 Author Share Posted May 13, 2022 Also https://home.treasury.gov/resource-center/data-chart-center/interest-rates/TextView?type=daily_treasury_yield_curve&field_tdr_date_value=2021 Free advice is worth what you paid for it. Do not rely on the information provided in this post for any purpose, including (but not limited to): tax planning, compliance with ERISA or the IRC, investing or other forms of fortune-telling, bird identification, relationship advice, or spiritual guidance. Corey B. Zeller, MSEA, CPC, QPA, QKA Preferred Pension Planning Corp.corey@pppc.co Link to comment Share on other sites More sharing options...
austin3515 Posted May 13, 2022 Share Posted May 13, 2022 Where can I download the mortality tables? Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
Bri Posted May 13, 2022 Share Posted May 13, 2022 The IRS usually puts it in a PDF, which isn't great, but if you don't already have software with it preloaded, a search for "YYYY applicable mortality table" should do the trick. Link to comment Share on other sites More sharing options...
austin3515 Posted May 13, 2022 Share Posted May 13, 2022 Is this it on page 5? https://www.irs.gov/pub/irs-drop/n-22-22.pdf Or is this only for 2023 valuation dates? And which column? Unisex on far right? Another one for 2021, but no unisex one? https://www.govinfo.gov/content/pkg/FR-2022-04-28/pdf/2022-06330.pdf Man this is complicated.... Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
austin3515 Posted May 13, 2022 Share Posted May 13, 2022 I guess I found the 2022 tables. It was not easy find! https://www.irs.gov/irb/2020-51_IRB#NOT-2020-85 Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
Bri Posted May 13, 2022 Share Posted May 13, 2022 That looks right, the unisex table on the far right (spot-check matches what I have in Relius) Link to comment Share on other sites More sharing options...
austin3515 Posted May 13, 2022 Share Posted May 13, 2022 7 minutes ago, Bri said: (spot-check matches what I have in Relius) 1) Where in Relius? 2) It doesn't seem to match the mortality table that Zeller put in the spreadsheet... Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
Bri Posted May 13, 2022 Share Posted May 13, 2022 Data entry -> Tables -> Actuarial -> Table Entry When the window comes up, choose "table type" of Mortality, and then scroll through your list of Available Tables. CBZ's spreadsheet still had the 2021 mortality table in there. austin3515 1 Link to comment Share on other sites More sharing options...
austin3515 Posted May 13, 2022 Share Posted May 13, 2022 Arond and around we go. My newest one is from 2015 and I presume the issue is that we have not downlaoded the tables. My original quesiton on a different thread was where in Relius support website do I download the tables. Bri, do you know where I do that? I really really appreciate everyone's help at here! Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
austin3515 Posted May 13, 2022 Share Posted May 13, 2022 OK Pam Shoup on another thread hooked me up and now I have the tables in Relius! But there are 10 2021 tables to choose from. Which one is CBZ using? These are my choices. Nothing seemed to line up with CBZ... Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
AmyO Posted May 14, 2022 Share Posted May 14, 2022 On 2/24/2022 at 10:21 AM, C. B. Zeller said: If someone wants to enhance this workbook to automatically pull the 417(e) tables, or the 10-year CMT rates, that would be fantastic. 10 Yr CMT Rates can be imported from NASDAQ - see the right sidebar (I can't use the Excel add on, I'm on a Mac; but it's working for me to use the API > CSV import to Google Sheets using =IMPORTDATA("https://data.nasdaq.com/api/v3/datasets/FRED/DGS10.csv?api_key=r_sHspCBe6eXM5VoV57h",",","en_US")) Haven't been able to locate an importable mortality table yet... Link to comment Share on other sites More sharing options...
austin3515 Posted May 14, 2022 Share Posted May 14, 2022 You can easily convert PDFs to Excel so having in PDF is pretty much just as good. 2021 table if anyone wants it. https://www.irs.gov/pub/irs-drop/n-19-67.pdf 2022 table again just to put them both in the same post. This is already html so more of a copy/paste: https://www.irs.gov/irb/2020-51_IRB#NOT-2020-85 Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
austin3515 Posted May 14, 2022 Share Posted May 14, 2022 Attention Relius Users The name of the mortality table you want is called "20XX Applicable." If you navigate to Data Entry, Tables, Actuarial, Table Entry, pick "Mortality" from drop-down, and then select "2021 Applicable" (for the 2021 table) that table will be displayed. You can then click/drag on the Age and Factor headers which will highlight the rows and you can then copy/paste into Excel! Austin Powers, CPA, QPA, ERPA Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now