Jump to content

APR Calculator Workbook


Recommended Posts

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

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

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

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

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

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...

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

  • 2 months later...
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

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

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

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

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.

Link to comment
Share on other sites

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

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...

image.png.0813e1bdb05d76eeee06cbaf43236242.png

Austin Powers, CPA, QPA, ERPA

Link to comment
Share on other sites

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...