Pricing Life Insurance in Excel

1. Pricing Life Insurance in Excel Richard J. Junker, FSA, MAAA Junker Consulting Actuarial Club Liaison, Technology Section

2. Expert Witness Report: ABC Trust v. Wizard Life Insurance Company • Description of Assignment • How to Land Expert Witness Assignments • Qualifications and Interview with Attorney • Conclusion: One-Page Summary Report • Actuarial Report • Peer Review Actuary

3. Pricing EssentialsApply Powerful Features of Excel • Introduce Case Study: Expert Witness--Calculation of Loss on Rescinded Policies • Assignment #1 – Determine annual premium by entering commissions and expenses • Assignment #2 - Assess impact of alternate interest rate assumptions • Assignment #3 – Assess Impact of 10% worse mortality on annual premium • Assignment #4 – Document Native American mortality factor to reproduce 10 years of reduced life expectancy • Final Topics – Q & A

4. Important Features of Excel • Useful Functions • Audit Formulas—mortality rates • Text handling • Data manipulation • Interpolation of missing data—premium rates per $1000 • Data Scrubbing—Fields with defective entries • Sorting—Arrange insureds by issue age to interpolate premium rates • Filtering—Data Scrubbing: identify surprising contents of a given field • Visual Basic • Calculate single premiums for a portfolio of policies • Modify VB logic for any iterative input set • Visualization • Scatter Diagrams to validate data of each field • Charts to assess mortality ratios • What-if analysis--Goal Seek, Solver—solve for expenses to produce targeted premium

5. Suplemental Topics • Best Practices for Excel Design • Macro Programming • Excel Resources Online ********************* • Professional Standards of Practice • Personal Finance • Be An Actuary

6. Personal Finance • Build your Future Financial Literacy curriculum, The Actuarial Foundation http://actuarialfoundation.org/programs/youth/BuildingYourFuture.shtml • Buy financial software, ideally Quicken (or Mint online for free) and Turbotaxonline at Staples or Costco or ww.intuit.com • Skills for Eternity--A New Retirement Paradigmhttp://www.contingenciesonline.com/contingenciesonline/20140708?folio=36#pg1

7. Be An Actuary • What to Study? • http://www.beanactuary.org/study/?fa=what-to-study • If you are currently pursuing an undergraduate degree and are interested in an actuarial career, your equation for success should include the following courses: • … • business courses, such as marketing • communication courses, such as speech, • business writing and technical writing • literature, history, art, political science, the humanities, and other liberal arts classes • If this curriculum gives you the idea that actuaries need a well-rounded education, you're right. What sets actuaries apart from other professionals is their ability to learn and assimilate a wide range of information and communicate it effectively. As their careers move beyond the traditional insurance industry boundaries, actuaries are finding that a versatile storehouse of knowledge pays off. • See more at: http://www.beanactuary.org/study/?fa=what-to-study#sthash.f8jw3fWT.dpuf

8. Q&A

9. EXCEL Resources Online (1) • Self-study: Mr. Excelhttp://mrexcel.com/ • The Excel Addicthttp://theexceladdict.com/ Question & Answer forums • Experts-Exchange. Pay site covering broad spectrum of computer related topics, with participation by 500 Microsoft MVPs among others. Has articles that cover interesting topics in depth. You can join for free if you answer questions. Members can search a database of over 2 million answered questions, or post a question and have a formula or macro written for their exact problem. http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/ • Microsoft Answers. Free forum with sections for each of Microsoft’s products. Hundreds of Microsoft MVPs earn their award by answering questions in this forum, along with other experts. http://answers.microsoft.com/en-us/office/forum/excel?tab=Threads • Mr Excel. Free forum dedicated to Excel, with participation by a number of Excel MVPs and other experts. Microsoft Excel MVP Bill Jelen posts podcasts on Excel topics on regular basis. http://www.mrexcel.com/ • StackOverflow. Free forum covering a broad spectrum of computer related topics. Both questions and possible answers are upvoted and downvoted by other members. Expect to get downvoted if you ask a dumb or poorly worded question. Site uses tags to categorize questions, so it’s more difficult to browse than the preceding sites. http://stackoverflow.com/tags

10. EXCEL Resources Online (2) Training videos • Excel Is Fun. Free videos on YouTube channel by Microsoft Excel MVP Mike Girvin. Over 2200 Excel topics on worksheet formulas, formatting & features. https://www.youtube.com/user/ExcelIsFun General Excel reference sites • Debra Dalgleish. Web pages don’t cover everything, but what they do cover is in great depth. Excellent coverage of conditional formatting, data validation and PivotTables. Ms. Dalgleish has been a Microsoft Excel MVP since 2001. http://contextures.com/tiptech.html • Ron de Bruin. Web pages covering a handful of specialized topics in great depth: emailing from Excel using macros, Macintosh Excel, and manipulating the ribbon. Mr. de Bruin has been a Microsoft Excel MVP since 2002. http://www.rondebruin.nl/ • Dick Kusleika. Blog covering Excel topics, both worksheet user interface and VBA. Updated several times a week, with articles written by Mr. Kusleika, other MVPs or Excel experts. http://dailydoseofexcel.com/ • Chip Pearson. Web pages cover numerous topics in worksheet user interface plus VBA programming. Mr. Pearson has been a Microsoft Excel MVP since 1998. Content covers the types of questions that appear most frequently in help forums. Best overall reference site in my opinion. http://www.cpearson.com/excel/topic.aspx • Jon Peltier. Web pages centering on charting. Some pages describe manual techniques to produce specialized charts, while others show VBA techniques. Dr. Peltier has been a Microsoft Excel MVP since 2001. http://peltiertech.com/ • John Walkenbach. Web pages cover many topics in worksheet user interface plus VBA programming. Mr. Walkenbach was an Excel MVP for many years, but decided to leave the program a couple of years ago. He writes an excellent series of books covering Excel use, formulas, VBA programming and charting. These books are updated for each new version of Excel. http://spreadsheetpage.com/index.php/site/tips/

11. Personal Resources Online • Professional Standards of Practicehttp://actuary.org/content/actuarial-standards-practice-asops • Buy financial software, ideally Quicken (or Mint online for free) and Turbotax, online at Staples or Costco or www.intuit.com • Build your Future Financial Literacy curriculum, The Actuarial Foundation http://actuarialfoundation.org/programs/youth/BuildingYourFuture.shtml • Be An Actuaryhttp://www.beanactuary.org/ • Skills for Eternity--A New Retirement Paradigmhttp://www.contingenciesonline.com/contingenciesonline/20140708?folio=36#pg1