First Edition Actuarial Excel Data Analytics and Applications Guangwei Fan Guodong Li Jinfeng Wei Preface This iBook was written based on lecture notes from courses on actuarial technology and from materials of actuarial internships over the past twenty years in the Actuarial Science Program at Maryville University. Possessing the capability and expertise in Excel is a necessity in the actuarial profession. This iBook is intended to provide background and practical uses in actuarial science, including basic Excel skills in data analytics and applications using iPad interactive applications. A recent trend in higher education is to use e-textbooks in order to reduce costs and to increase accessibility. iBooks are convenient to read using an iPad, iPod, iPhone, MacBook, or any IOS device. Students can use the highlighting tool and use the notepad to type notes. Interactive media including videos, quizzes and links provide them with an interactive learning environment. Features of this iBook Each chapter includes an Introduction Video, a link for Interactive In-class Practice Exercise, an Interactive Review Quiz, an Excel Function Demo Video, and a link to the Assignments. These interactive components will enhance student's involvement and help them to reflect on the key points of each chapter. i Introduction Video: There is an introduction video at the beginning of each chapter which introduces the framework of the chapter, the names of the Excel functions to be explained, and a scenario for their application in actuarial science. Interactive In-class Practice Exercise Link: The explanation of Excel skills is followed by an interactive in-class practice exercise link in each chapter. This link provides interactive Excel programming windows with data for students to practice the Excel skills that they have learned in that chapter. After completing the problems, the students can share their answers in class using the Airdrop feature on their iPad. This way, the instructor is able to check how well the students understand the topics. cide whether or not to slow down or explain the concepts again. Excel Function Demo Video: There is an Excel function Demo video before the assignment section of each chapter. The video demonstrates the operations of the Excel functions step-by-step in the application of actuarial science. This provides instant assistance for students if they are having trouble with the assignment. Assignment Data Link: A link to the assignment data is included in the iBook, so students can open the link and do the work in the Excel programming active windows directly in their iPad, or they have the option to download the data and then do the work. Interactive Review Quiz: The first part of each chapter ends with an interactive review quiz about the Excel skills. The review quiz has two or three multiple choice questions about the key points from the chapter. The students may take the interactive quiz after class to reflect on whether they have mastered the Excel from the chapter. The interactive quiz will automatically check the answers. The instructor may use the review quiz in class to check the students' understanding of the concepts, and then deii Topics Covered in this iBook There are two main parts in each chapter, including Excel Skills and Applications in Actuarial Science. Acknowledgements The authors wish to thank Dr. Mary Ellen Finch, Vice President for Academic Affairs, for her initiation and for the funding from Chapter One: Rank Functions and their applications for Sorting Contributions. the Finch Center for Teaching and Learning, at Maryville Univer- Chapter Two: Indirect Functions and their applications for summarizing results generated by pension valuation We want to thank Samuel L. Harris, Director of Learning Tech- software, ProVal. Chapter Three: Pivot Tables and their applications for analyzing employee statistics sity. nology & Support, who provided us with Apple training and technical support. We also thank the students in the actuarial technology classes and actuarial internship classes at Maryville University for their comments and feedback. Chapter Four: Data Tables and their applications for calculating present values, durations, and convexities of given cash flows. The authors acknowledge the Programs of Mathematics and Actuarial Science at Maryville for offering the courses in actuarial technology and actuarial internship for the past twenty years. The authors are grateful to Dr. Johannes Wich-Schwarz for his proofreading of the book. Finally, thank you to Jennifer Liu and Ethan Edward for their audio record. iii About the Authors Guangwei Fan, Ph.D., A.S.A., and MAAA is a Professor of Mathematics and Actuarial Science at Maryville University. He has taught mathematics and actuarial science for over thirty years. He has published numerous papers in mathematics and actuarial science, and he has co-authored several textbooks in mathematics and actuarial science. Guodong Li, Ph.D., E.A., F.S.A., and MAAA, is a Director and Retirement Reviewing Actuary at Buck Consultants, A Xerox Company, with more than 20 years of retirement consulting experience. He also serves as The Honorary Distinction of Senior Fellowship of Mathematics and Actuarial Science at Maryville University. He had served as a volunteer on various SOA committees, as a chair of an EA exam committee, and he has served on the Advisory committee of Actuarial Science at Maryville University. Jinfeng Wei, Ph.D., is an Associate Professor of Mathematics at Maryville University. She has published multiple papers in the fields of numerical solutions and applied statistics, and coauthored a mathematics textbook. She has taught a variety of courses in mathematics, statistics and actuarial science. iv Rank Functions 1 Rank Functions Sorting Contributions Introduction Video of Chapter 1 Introduction Video of Chapter 1 6 1.1 Excel Skills: Rank Functions 1. Rank The function Rank returns the rank of a number (Number) in a list of numbers (Ref). It essentially determines its size relative to other values in the list. If more than one value has the same rank, the top rank of that set of values is returned. This function is available for compatibility with Excel 2007 and earlier versions. In Excel 2010, the function is replaced by Rank.EQ and Rank.AVG. The function's syntax is 1. Rank Rank(Number, Ref, Order) 2. Rank.EQ 3. Rank.AVG Number is the number for which you want to find the rank. Ref is a reference to a list of numbers. Nonnumeric values are ignored. Order determines how the numbers are to be ranked; a value of 0 ranks the numbers in descending order, 4. Application to Sorting 5. Interactive in-class Exercise! 6. Interactive Review Quiz and any nonzero value ranks the numbers in ascending order. Order is an optional argument, and its default value is 0. 2. Rank.EQ The function Rank.EQ returns the same value as the Rank function. This function is only available with Excel 2010 or later. The function's syntax and arguments are exactly the same as those described for the Rank function. 7 According to the table, Rank and Rank.EQ produce the same 3. Rank.AVG The function Rank.AVG is similar to the Rank function in that it returns the rank of a number (Number) in a list of numbers (Ref) by determining the number's size relative to other values in the list. The key difference, however, is that if more than one value has the same rank, the Rank.AVG function returns the average rank of that set of values. This function is only available with Excel 2010 or later. The function's syntax and argu- results, and Rank.AVG gives the average rank rather than the top rank when there are duplicate values. Michael's score is not available, so his rank could not be determined. Also, his score does not affect how the ranks for the other students are determined. 4. Application to Sorting ments are exactly the same as those described for the Rank Excel's Sort tool is perfect for sorting information in a database function. or list in either ascending or descending order. Occasionally, however, you may need to sort a list by using functions in Ex- The following table compares each student's rank produced by these three functions (Rank, cel, rather than using the sorting tool. Rank.EQ, and Rank.AVG), based on his or her score relative to his or her classmates: The key advantage in using formulas to sort information is that the calculations are done automatically, whereas sorting by means of the Sort tool requires an active application on the part of the user. The latter option requires that the user either knows how to properly use the Sort tool or how to construct and use command controls and macros. When we use the Sort tool in Excel, we always perform the action on the same list. But when we use formulas to sort a list, we place the sorted list in a different area as an output result. We will use the table below, in which the list does not 8 have duplicate values, to explain the steps needed for a simple sort. # Use any one of the three rank functions to determine the rank of the score for each student relative to his or her classmates. Since there are no duplicate scores, each student in the class should have their own unique rank. The Rank column in the intermediate calculations section contains the determined ranks. # Next, determine the line number in the input list that corresponds to the final rank number in the output list. You can use the Match function (or a Lookup function) to achieve this. If the ranks for intermediate calculations are in cells I4:I13 and the ranks in the output list are in cells K4:K13, the formula in cell J4 for the Line # column can be \"=MATCH(K4,I$4:I$13,0)\". # Enter the ranks in the output list. You can only enter the In the table above, we have three main sections: Input List numbers 1 through 10 in those cells. If you do not need (Name and Score), Output Sorted List (Rank, Name, and this column in the Output Sorted List section, you can al- Score), and the area for intermediate calculations (Rank and ways move it to the intermediate calculations area. Line #). We could build long formulas in the output list that # Now you are ready to put the other information in the would eliminate the need for intermediate calculations, but we final output list by using the Index function (or another will keep the intermediate calculations in order to see how eve- Lookup function). If the names in the input list are in cells rything is done. When the programing is finished, we will hide G4:G13, the formula in cell L4 in the Output section can the columns that contain the intermediate calculations. Here is be \"=INDEX(G$4:G$13,J4)\". the process we used to create the above table: 9 # Similarly, if the scores in the Input list are in cells H4:H13, the formula in cell M4 in the Output section can be \"=INDEX(H$4:H$13,J4)\". We have added another column for Tie-Breaker in the Input list and another column for Adj Score in the area for intermedi- The above example is not very practical, since it is almost im- ate calculations to the table from the previous example. Here possible to not have duplicate scores in a class. So in order are the changes made to the process outlined in the previous for the above model to be more useful, it needs to be able to example: deal with duplicates. To do this, you will need a tie-breaker. You could set up a tie-breaker that keeps the original order, re- # Place a dummy number (100 in the table) above Adj verses the original order, or simply include a tie-breaker col- Score. This should be a very large number so that order- umn in the Input list. ing by Adj Score would not be different from ordering by Score for the students whose scores are not duplicated. In the next example (the table shown below), students with For example, if all scores are integral and the highest tie- the same score will be ranked according to who has the breaker is 11, then a dummy number of 12 or larger would greater tie-breaker number. be sufficient. # The numbers in the Adj Score column are found by dividing each tie-breaker by the dummy number, and adding that value to its corresponding actual score. # Use a rank function to determine the rank of each entry in the Adj Score column, rather than that of each entry in the Input section's Score column. # Everything else is the same as before. 10 The approach used in the last example can be easily applied to 6. Interactive Review Quiz about Rank Functions other situations. For example, you may want to: # Keep the original order seen in the Input section # Reverse the original order seen in the Input section # Rank the male students first, and then rank the female Interactive Review Quiz --Rank Functions Question 1 of 3 What is the Rank.AVG of Ann in the table below? students. 5. Interactive In-class Exercise Click the link below to find the rank by their scores. Use your iPad AirDrop to share your answer with the class https://www.dropbox.com/s/4iqmi548p61pev2/Rank%20Func tion%20Practice%20Link.xlsx?dl=0 A. 2 B. 4 C. 1 D. 2.5 Check Answer 11 1.2 Applications: Sorting Contributions 1. No Duplicates in Contribution Dates Contributions made to a retirement plan are used to meet both a minimum requirement and a quarterly requirement. In addition, a contribution must be first used to meet the balance on the prior year's unpaid minimum before it can be applied to the current year's minimum. It must also meet, in order, all the quarterly requirements. Contributions should be sorted by contribution dates and then applied to prior year and current year minimums and each quarterly requirement, unless users are expected to enter the contributions in order. 2. Contributions with Duplicates Dates 3. Rank Employer Contributions Ahead of Other Rank Contributions We will explore a few examples of sorting contributions in order to practice our skill at using Excel formulas to sort lists. 4. Discounted Value of Employer Contributions 5. Demo Video of Rank Functions 1. No Duplicates in Contribution Dates 6. Assignments--Chapter 1 In this scenario, we assume that no input contribution dates are duplicated. Thus, the sorting should be done in a manner similar to the earlier example using Excel formulas, except that the input information might not require the whole input area. In other words, some of the input area might be blank. Part of the input is shown below. The inputs for Dates and Contributions (Employer and Employees separately) are in cells 12 B10:D40. The counter (1, 2, 3, ..., 31) is shown in A10:A40. =IF(B10="",$N$2,B10) =RANK(L10,$L$10:$L$40,1) =MATCH(A10,$M$10:$M$40,0) These formulas can then be copied and pasted to the rest of the cells in those columns. We can also use a count function to determine the number of contributions that will be shown in the output. Using the below formula in cell N6, the number of contributions is 7. =COUNTIF($L$10:$L$40,"<"&N2) We can now place our sorted contributions in the output section. The formulas in cells F10:H10 for \"Date,\" \"Employer,\" and \"Employee\" in the output sections are We have our intermediate calculations for \"Adjusted Contribution Date,\" \"Rank,\" and \"Line#\" in columns L through N. We will hide these three columns once the project is done. For \"Adjusted Contribution Date,\" we assign a date in the distant future to any contributions that do not have a date listed. This way, those dates will appear at the bottom of the list after sorting. With cell N2 containing the future date (1/1/3000), the formulas in cells L10:N10 are =IF($A10>$N$6,"",INDEX(B$10:B$40,$N10)) =IF($A10>$N$6,"",INDEX(C$10:C$40,$N10)) =IF($A10>$N$6,"",INDEX(D$10:D$40,$N10)) We use an IF statement in each formula in order to make sure that nothing is shown in the output when there is no corresponding input. For this example, the output should appear as below: 13 2. Contributions with Duplicate Dates To the previous scenario we added another dummy number of 500 in cell N4, and we changed the formula in cell L10 to the one shown below. In this scenario, everything is the same as in the last example, =IF(B10="",$N$2,B10+(A10/$N$4)) except that we assume some input contribution dates are duplicated. We will use the following input information to test our The purpose of inserting \"A10/$N$4\" into the formula is to add model: a few hours or minutes to the date, so that each date in \"Adjusted Contribution Date\" will be unique. All other formulas are the same as in the first scenario. The output is shown below. 14 3. Rank Employer Contributions Ahead of Other Contributions Compared to the previous scenario, we changed the original dummy date from 1/1/3000 to 1/1/4000, add another dummy date of 1/1/3000 in cell N3, and changed the formula in cell The goal of this example is to sort records containing employer contributions ahead of the others. We will use the following input information to test our model: L10 to the one shown below: =IF(B10="",$N$2,B10+IF(C10=0,$N$3,0))+A10/$N$4 The \"IF(C10=0,$N$3,0)\" portion of the formula sorts the records with employer contributions ahead of other records. All other formulas are the same as those in the previous scenario. The output is shown below. 15 to drop the contributions made before 1/1/2011 or after 9/15/ 2012. We are also going to discount the contributions, so we need the interest rate (or discount rate) in cell D3 and the \"as of date\" (Plan Year start date) in cell D2, to which the value is discounted. We will use the following input information to test our model: 4. Discounted Value of Employer Contributions We will still sort the contributions by the dates they are made, but we will need to drop the contribution records that do not have an employer contribution. We also need to drop the records with a contribution made before the plan year start date or more than 8.5 months after the end of the plan year. In other words, if the plan year starts on 1/1/2011, then we need 16 Compared to the second scenario (Contributions with Duplicate Dates), we now have the formulas for intermediate calculations in columns N through P, rather than L through N. We also place the dummy value \"Last date\" in cell P3, which has the following formula: =DATE(YEAR(D2)+1,MONTH(D2)+8,DAY(D2)+15) We then change the formula in cell N10 (L10 in the second scenario) to the one shown below. =IF(OR(B10="",C10=0,B10<$D$2,B10>$P$3),$P$2,B10+ (A10/$P$4)) The extra portion in the IF condition is used to drop unwanted records properly. Cell P2 contains the dummy date of 1/1/3000, and all other formulas regarding the sorting are similar to those in the second scenario. The final output is shown below, with explanations of the extra calculations following. 17 The column \"Days to\" shoes the number of days between the contribution date and the plan year start date. The calculations \"Discounted value of accrued contributions as of 1/1/2012\" is are in cells H10:H40. 793,233 for this example. It is the sum, with one year interest, of the Discounted Employer Contributions in J10:J40 that are The column \"Discount Factor shows the interest factor dis- made on or after 1/1/2012. The date 1/1/2012 after \"as of\" counted from the contribution date to the plan year start date. should be programmed, not hard coded. The formula for this These factors are in cells I10:I40. cell is as follows: The column \"Discounted Employer Contributions\" shows the dis- =SUMIF($F$10:$F$40,">"&EDATE(D2,12)-1,J$10:J$40)* counted value as of the plan year start date of each employer # (1+D3) contribution. These values are in cells J10:J40. The formulas in cells H10:J10 are shown below. The IF statements allow the formulas to display blank cells when necessary. =IF($F10="","",F10-$D$2+1) =IF($F10="","",(1+$D$3)^(-H10/365)) =IF($F10="","",ROUND(G10*$I10,2)) \"Discounted value of contributions as of 1/1/2011\" is 4,352,021 for this example. It is the sum of the Discounted Employer Contributions in J10:J40. The date 1/1/2011 after \"as of\" should be programmed, not hard coded. 18 Assignments -- Chapter 1 5. Demo Video of Rank Function Applications Click the link below to download the data for the four asDemo Video of Rank Functions signments. https://www.dropbox.com/s/5h3lw3pz10pw03f/Rank%20 Function%20Assignment%20Link.xlsx?dl=0 1. No Duplicates in Contribution Dates Use Excel functions to sort input contribution records by contribution dates in ascending order, and present the sorted contribution records in the output. Each contribution record contains a contribution date, employer contribution, and employee contribution. You are provided with less than 31 records, but your model should be able to handle up to 31 contribution records. You can assume that no input contribution dates are duplicated in this assignment. When completed, your output table should show the same information as the input, but in the required sorted order. The 19 output section should also know how many records it needs dates in each category (those with employer contributions and to show. Please format the model in the same manner as the those without employer contributions). samples provided. The worksheet should be named \"Sort ER first\". The worksheet should be named \"Contrib wo duplicates\". 4.!Discounted Value of Employer Contributions 2.!Contributions with Duplicates Dates Use Excel functions to sort contribution records by contribution Use Excel functions to sort input contribution records by con- date in ascending order, and then present the sorted records tribution date in ascending order and then present the sorted and the discounted values of their corresponding employer con- contribution records in the output. It is the same as Assign- tributions. ment #1, but with one exception: Some input contribution dates may be duplicated. Each contribution record contains a contribution date, employer contribution, and employee The worksheet should be named \"Contrib w duplicates\". contribution. Even though you may be given less than 31 records, your model should be able to handle up to 31 contribution records. Be aware that 3.!Rank Employer Contributions Ahead of Other Contributions some input contribution dates may be duplicated. To calculate the discounted value of each employer contribution, you will need to have two more input items: the valuation date (or the This assignment's requirements are the same as those for As- plan year start date) and the effective interest rate. signment #2, with one exception. The output should have records that contain employer contributions placed ahead of the The output table should display the contribution dates, em- records that do not have employer contributions. You must also ployer contributions, number of days between the valuation sort the contribution records in ascending order of contribution date and the date of each contribution, discount factor, and dis20 counted employer contributions, listed in the required sorted year. (Both approaches are acceptable even though they order. The output section should show how many records to produce different results.) display. Please format your model in the same manner as the provided samples. # A discounted contribution is just the product of the discount factor and the corresponding contribution amount. During the sorting process, you also need to drop any records # The total as of a date for discounted contributions is the where a contribution is made before the plan year start date total value of the discounted contributions made on or af- or after 8.5 months past the end of the plan year. In other ter that date. words, if the plan year starts on 1/1/2011, you need to drop any contributions made before 1/1/2011 or after 9/15/2012. # The accrued contributions as of a specific date are all those contributions that are made on or after that given date. Some helpful hints for certain calculation items are provided below: # Since the discounted value for each contribution is calculated as of the valuation date, you have to increase it with interest (using effective interest rate) when # \"Days to valuation date\" is the number of days between the valuation date and a given contribution date. you calculate the total of the discounted contributions as of the following valuation date. # The discount factor is (1+i)-t. If a contribution is made in the valuation year, t equals \"days to valuation date\" di- The worksheet should be named \"Discounted Value\". vided by the total number of days in the valuation year. If a contribution is made in the year following the valuation year, t equals 1 + m, where m is the excess of \"days to the valuation date\" over the total number of days in the valuation year, divided by the total number of days in either the valuation year or the year following the valuation 21 Sample Outputs of Assignments-- Chapter 1 The following pages in this session provide a sample for the appearance of your completed model's outputs. These sample outputs may provide guidelines regarding what the outputs should display. 22 23 Indirect Functions 2 Indirect Function Proval Introduction Video of Chapter 2 Introduction Video of Chapter 2 25 2.1 Excel Skills: Indirect Functions Some Excel functions can be tricky to work with. The Indirect function is one of them. This is not necessarily because you don't understand it, but because you don't see when and how it can be used. It is important to know how to build the formula in the function, but it is even more important to have a handle on how you can use it to enhance your Excel model. Once you truly understand indirect formulas, you can save a lot of time in performing many tasks. 1. Indirect Function 2. Indirect Formulas 1. Indirect Function This function returns a reference that is specified by a text string. The Indirect function has one required argument, which 3. Pros and Cons is a text string or reference, or a formula that results in a text 4. Case Studies R1C1-style. You can use either type of reference with this func- string. There are two types of cell references: A1-style and tion. The function does have an optional argument, which speci- 5. Interactive in-class Exercise 6. Interactive Review Quiz fies the type of reference for the first argument. If the reference type is not specified, the function assumes A1-style. Example 1. Let's enter the following two formulas in cell C3 and cell C4, respectively: 26 # = INDIRECT("C2") # = INDIRECT("R2C3", FALSE) If the number 5 is in cell C2, then both cells C3 and C4 will display the number 5. Cells C3 and C4 will actually show whatever is contained in cell C2. The reference \"R2C3\" in the second formula means the cell in the second row and third column. It is important to note that we do not have to hard code the cell reference in the function used above; using named cells is an alternative to the previous example. We could link the argument to other cells (say, C6 and D6) as long as those cells contain the same text string, which we will see in the next example. Example 2. Assume that cells C6 and D6 display \"C2\" and \"R2C3\