APR Calculator Workbook

[C. B. Zeller]

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

[david rigby]

Always talk to your actuary.

[C. B. Zeller]

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.

[CuseFan]

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. 

[C. B. Zeller]

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]

Yeah that one never ends!

[austin3515]

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.

[Bri]

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.

[austin3515]

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?

[Bri]

https://fred.stlouisfed.org/series/DGS10

[C. B. Zeller]

Also https://home.treasury.gov/resource-center/data-chart-center/interest-rates/TextView?type=daily_treasury_yield_curve&field_tdr_date_value=2021

[austin3515]

Where can I download the mortality tables?

[Bri]

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.

[austin3515]

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

[austin3515]

I guess I found the 2022 tables.  It was not easy find!

https://www.irs.gov/irb/2020-51_IRB#NOT-2020-85

[Bri]

That looks right, the unisex table on the far right (spot-check matches what I have in Relius)

[austin3515]

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

[Bri]

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]

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!

[austin3515]

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

[AmyO]

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

[austin3515]

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

 

[austin3515]

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!