Excel LAB

Math/Stat 170 Lab Project 2 Purdue Life Profits

Part 1: Purdue Life sells 100, 30 year term insurance policies, each with a death benefit of $10,000 and each with an annual premium of $100/year, payable on January 1. We will initially assume that every December 31, one of our original 100 policy holders dies. The remaining policy holders renew for another year, paying another $100 premium. We will also initially assume that we invest all premiums at an interest rate of 10% per year. In the first part of this project, you will set up an Excel spreadsheet to compute a year-by-year record of Purdue Life's profit under the above scenario. Part 2: In the second part of the project you will study the effect of changes in the interest rate and the mortality experience of the insurance policies on Purdue Life's profit. Specifically, you will use the interest rate indicated in the “rate” column next to your name on page 5 of this handout instead of the 10% rate from Part 1. You will also assume that for the period of time listed under the “Period” column next to your name on page 5, 2 people die on Dec. 31 instead of 1. (This is 1, 2, 3, 4, or 5 years) In every other year, from year 1 to year 30, 1 person dies. You assume that the period of 2 deaths begins with the year listed in the columns labeled “Year Begins” next to your name on Page 5. (For example if you have a Period of 3 and a Year Begins of 12, you would have 2 deaths in years 12, 13, and 14 and 1 death in all other years.) Compute a year-by-year record of Purdue Life's profit under this scenario, storing it on Sheet2 of your spreadsheet. Please note that Claims are the same as deaths. Detailed Instructions: Part 1 1) Login and get into Excel. 2) Type your name, the Lab number, and the date in the upper left hand corner as indicated in the first picture

on third page of this project.

Tip: Change the format of the date if you need to so that it looks like the first picture on page 3. 3) Type your headers into rows 7 and 9. Format them to look like those in picture 1. 4) Enter a 1 into cells A10. Enter a formula into cell A11 and copy it down to A 39. Your screen should now look

like picture 2 below.

Tip: Remember, you can copy by right clicking a cell and selecting copy. Then, highlight an area by left clicking and holding it down while you drag your mouse over the area that you want to copy into. Right click the highlighted area and select paste. Note: Do not type in the numbers in A11 through A 39 or you will not receive full credit for the Lab. If you are not sure what formula to use, look at Lab 1.

5) Enter 100 into B10. This is the number of people who originally bought the 30 Year Term insurance. 6) Each person who is alive will pay a $100 premium. Enter a formula in C10 which gets you the correct premium

for year 1. 7) The beginning balance is the ending balance from the previous year plus the premium from the current year. In the

first year, there is no ending balance, so the beginning balance is just the premium from the first year. Therefore, set Cell D10 equal to C10. Use a formula.

8) We are given that the interest rate is 10% in all years. Enter .10 in column E (Cells E10 to E39)

Hint: You should type it into E10 and then copy it down. You should not type it into each cell.

9) Purdue Life will earn interest on the beginning balance at the interest rate in the rate column. Enter a formula for this calculation in F10.

10) We are given that one person will die each year. Therefore, enter 1 in column G (cells G10 to G39). 11) We are given that for each person that dies (each claim), a death benefit of 10,000 is to be paid. Enter a formula in

column H which calculates this benefit. This should be 10,000 times the number of claims. Be sure to enter it as a formula because in Part 2, the deaths will not be one in each year.

12) Our Ending Balance is the Beginning Balance plus the interest less the benefits. Enter a formula in I10 to calculate the Ending Balance in year 1. Your screen should now look like picture 3 below.

13) We need to enter a formula in B11 for the Number of People alive. Just as in Lab 1, the Number Alive is equal to the Number Alive in the previous year (B10) less the number of Claims (Cell G10).

14) We also need to enter a formula in D11 for the Beginning Balance. Remember from step 7, the beginning balance is the ending balance from the previous year plus the premium from the current year. In the second year, there is an ending balance, so we have to use a different formula than was used in D10. Enter the formula.

15) Now copy the last formula in each column down through row 39. 16) Format the cells to look like those in picture 4. This will demonstrated in class if you do not know how to do. The

columns must be properly formatted for you to receive full credit. This completes Part 1.

17) Modify your spreadsheet to do Part 2 described above. Put each on a separate sheet in your spreadsheet.

Note: You can create additional sheets in Excel using the insert menu at the top of the window. You can copy the entire sheet by right clicking the box in the upper left hand corner (above row 1 and to the left of column A) and selecting paste. Go to the bottom and click on a new sheet and then right click the same box and select paste.

18) When you have finished the project, save your work. Name it “YourNameL2.xls" 19) Upload the completed project to Blackboard prior to the deadline indicated in Blackboard following the

instructions in the “Instructions for submitting labs” found on the course web page. Key Vocabulary Terms: Term Insurance Policies Death Benefit Premium Claims

Picture 1

Picture 2

Picture 3

Picture 4