Task 1: Accumulation phase modelling (40%) You are to build a spreadsheet in Excel that will...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Task 1: Accumulation phase modelling (40%) You are to build a spreadsheet in Excel that will model the accumulation phase of your superannuation account. You can assume that following: . ● ● Assume that you will start your actuarial job at exactly age 22. Your birthday is at the beginning of the year. Superannuation contributions are made at the end of each year, at a rate of 9.5% of your yearly salary. Salary increases are made at the end of each year, right after superannuation contributions are made. The superannuation contribution tax rates are 15%. The investment income tax rates are 15%. Superannuation administrative fees of $200 per year currently paid at the end of the year. These fees increase at a rate of 3% per year. ● ● You cancel your life insurance from your superannuation fund as it does not provide significant value for you and so you do not pay life premiums from your superannuation fund (note: while this may be true currently when you are young, later on in life you may wish to consult a financial planner to figure out your optimal investment portfolio and insurance levels.) ● Your investment manager charges a 1% investment management fee of funds under management. This 1% should be deducted from the after-tax returns. The challenging part of the assignment concerns how the actuary salary scale changes over time. In this assignment, we will focus on passing the actuarial examinations after you complete your university actuarial degree. We make the following assumptions: Your investment manager achieves a random return on investment of about 8% per annum. The return should be calculated as 8% + a normally distributed random variable with mean 0 and standard deviation 0.1. This return should differ for each period as well as for each simulation path that you will generate. You do not need to adjust for investment income tax in the case of negative investment returns. You have already obtained all exemptions for the Part I subjects during your university degree. You have 4 Part Il subjects and 4 Part III subjects that you need to pass in order to obtain Fellowship with the Actuaries Institute. You may take one exam each year with the following pass rates (in reality, for some subjects, you can take the exam twice a year but we will ignore this for reasons of simplicity): O O O For the Part II subjects, each subject has a pass rate of 40% For the Part III subjects, each subject has a pass rate of 20% You will pass all of your Part Il exams before moving onto the Part IIIl exams. Your salary increases are tied to your progress through your actuarial exams. The rules are the following: • Assume that you begin with a $80,000 yearly salary at age 22. Salary increases are applied at the end of the year for the next year's salary. You get a standard salary increase of 3% per year. Each time you pass a Part II exam, you get a bonus 7.5% salary increase at the end of the year (so a 10.5% increase in total for that year) • Each time you pass a Part III exam, you get a bonus 15% salary increase at the end of the year (so a 18% increase in total for that year) ● ● Based on the above, your salary scale is now stochastic. How we will deal with this in this assignment is to use simulations in Excel in order to account for the randomness. The ultimate goal of the task is to find how much you will have in your superannuation account at age 67 when you retire. More specifically, we will try to determine the distribution of the value of your superannuation savings account. You should design your spreadsheet so that the assumptions listed (apart from the starting age) can be changed without manually adjusting each of the formulas. Task 1 Part A: Generate 10,000 random simulations of your exam progress over time. I have provided a template for you to do complete this task in the Worksheet "Task 1 Part A". Hint: You may want to turn your formula calculations (found in the formula tab) to "Manual Calculations". This will prevent Excel from refreshing your spreadsheet's random numbers every time you make a small change. To refresh your random numbers, press the "Calculate Now" button or press F9. The final output should look something like the below: 9 Simulation 10 11 12 13 14 15 16 17 Inputs Part II Pass Rate Part III Pass Rate Exam Progress Time Age 1 123 4 5 6 7 8 9 C 40% 20% 0 ONOOOOOO 22 0 0 0 0 0 0 0 D 120000. 23 0 1 0 0 E 2 24 0 1 1 1 1 1 1 0 3 25 1 1 1 1 2 1 2 1 0 G 4 26 1 2222 1 H 5722 2222 1 3 2 1 62232~~~ 28 1 4 2 745 29 2 3 Wat 32 2 3 1 4 8 30 2 3 4 2 3 1 31 2 3 4 3 3 2 4 In each of the cells of the table, I have tracked the exam stage of simulation path which is a number from 0 to 8. Each row of the table represents one simulation path from ages 22 to 67. The table consists of 50,000 rows. Once this progress tracker is complete, the next task is to track the accumulated value of the superannuation accounts over time. Task 1 Part B: Generate 10,000 random simulations of your superannuation account value over time. In the second tab labelled "Task 1 Part B", you should link each simulation path of your superannuation account value with the corresponding path in the exam progress simulation. A template of the required table has been provided. Note that if you would like to split up the working into more manageable portions, you can do this on other worksheets and link the step-by-step results using formulas that reference these other sheets (e.g. for example, if you are having some issues completing the task, try having a separate spreadsheet that just contains how much your salary is for each simulation path). Task 1: Accumulation phase modelling (40%) You are to build a spreadsheet in Excel that will model the accumulation phase of your superannuation account. You can assume that following: . ● ● Assume that you will start your actuarial job at exactly age 22. Your birthday is at the beginning of the year. Superannuation contributions are made at the end of each year, at a rate of 9.5% of your yearly salary. Salary increases are made at the end of each year, right after superannuation contributions are made. The superannuation contribution tax rates are 15%. The investment income tax rates are 15%. Superannuation administrative fees of $200 per year currently paid at the end of the year. These fees increase at a rate of 3% per year. ● ● You cancel your life insurance from your superannuation fund as it does not provide significant value for you and so you do not pay life premiums from your superannuation fund (note: while this may be true currently when you are young, later on in life you may wish to consult a financial planner to figure out your optimal investment portfolio and insurance levels.) ● Your investment manager charges a 1% investment management fee of funds under management. This 1% should be deducted from the after-tax returns. The challenging part of the assignment concerns how the actuary salary scale changes over time. In this assignment, we will focus on passing the actuarial examinations after you complete your university actuarial degree. We make the following assumptions: Your investment manager achieves a random return on investment of about 8% per annum. The return should be calculated as 8% + a normally distributed random variable with mean 0 and standard deviation 0.1. This return should differ for each period as well as for each simulation path that you will generate. You do not need to adjust for investment income tax in the case of negative investment returns. You have already obtained all exemptions for the Part I subjects during your university degree. You have 4 Part Il subjects and 4 Part III subjects that you need to pass in order to obtain Fellowship with the Actuaries Institute. You may take one exam each year with the following pass rates (in reality, for some subjects, you can take the exam twice a year but we will ignore this for reasons of simplicity): O O O For the Part II subjects, each subject has a pass rate of 40% For the Part III subjects, each subject has a pass rate of 20% You will pass all of your Part Il exams before moving onto the Part IIIl exams. Your salary increases are tied to your progress through your actuarial exams. The rules are the following: • Assume that you begin with a $80,000 yearly salary at age 22. Salary increases are applied at the end of the year for the next year's salary. You get a standard salary increase of 3% per year. Each time you pass a Part II exam, you get a bonus 7.5% salary increase at the end of the year (so a 10.5% increase in total for that year) • Each time you pass a Part III exam, you get a bonus 15% salary increase at the end of the year (so a 18% increase in total for that year) ● ● Based on the above, your salary scale is now stochastic. How we will deal with this in this assignment is to use simulations in Excel in order to account for the randomness. The ultimate goal of the task is to find how much you will have in your superannuation account at age 67 when you retire. More specifically, we will try to determine the distribution of the value of your superannuation savings account. You should design your spreadsheet so that the assumptions listed (apart from the starting age) can be changed without manually adjusting each of the formulas. Task 1 Part A: Generate 10,000 random simulations of your exam progress over time. I have provided a template for you to do complete this task in the Worksheet "Task 1 Part A". Hint: You may want to turn your formula calculations (found in the formula tab) to "Manual Calculations". This will prevent Excel from refreshing your spreadsheet's random numbers every time you make a small change. To refresh your random numbers, press the "Calculate Now" button or press F9. The final output should look something like the below: 9 Simulation 10 11 12 13 14 15 16 17 Inputs Part II Pass Rate Part III Pass Rate Exam Progress Time Age 1 123 4 5 6 7 8 9 C 40% 20% 0 ONOOOOOO 22 0 0 0 0 0 0 0 D 120000. 23 0 1 0 0 E 2 24 0 1 1 1 1 1 1 0 3 25 1 1 1 1 2 1 2 1 0 G 4 26 1 2222 1 H 5722 2222 1 3 2 1 62232~~~ 28 1 4 2 745 29 2 3 Wat 32 2 3 1 4 8 30 2 3 4 2 3 1 31 2 3 4 3 3 2 4 In each of the cells of the table, I have tracked the exam stage of simulation path which is a number from 0 to 8. Each row of the table represents one simulation path from ages 22 to 67. The table consists of 50,000 rows. Once this progress tracker is complete, the next task is to track the accumulated value of the superannuation accounts over time. Task 1 Part B: Generate 10,000 random simulations of your superannuation account value over time. In the second tab labelled "Task 1 Part B", you should link each simulation path of your superannuation account value with the corresponding path in the exam progress simulation. A template of the required table has been provided. Note that if you would like to split up the working into more manageable portions, you can do this on other worksheets and link the step-by-step results using formulas that reference these other sheets (e.g. for example, if you are having some issues completing the task, try having a separate spreadsheet that just contains how much your salary is for each simulation path).
Expert Answer:
Answer rating: 100% (QA)
To generate 10000 random simulations of exam progress over time and the corresponding superannuation account value over time well follow these steps T... View the full answer
Related Book For
Processes Systems and Information An Introduction to MIS
ISBN: ?978-0133546750
2nd edition
Authors: Earl McKinney, David M. Kroenke
Posted Date:
Students also viewed these organizational behavior questions
-
Using the following information, set up a spreadsheet in Excel (based on the one on the next page). Then, determine which of the systems you will choose based on the best savings in the first year....
-
Lindsay is 25 years old and has a new job in web development. Lindsay wants to make sure she is financially sound in 30 years, so she plans to invest the same amount into a retirement account at the...
-
Assume Congress increases individual tax rates on ordinary income while leaving all other tax rates constant. How would this change affect the overall tax rate on corporate taxable income? How would...
-
Craps is a dice game in which two fair dice are cast. If the roller shoots a 7 or 11 on the rst roll, he or she wins. If the roller shoots a 2, 3, or 12 on the rst roll, he or she loses. (a) Compute...
-
Job 303 includes direct materials costs of $ 500 and direct labor costs of $ 430. If the predetermined overhead allocation rate is 80% of direct labor cost, what is the total cost assigned to Job 303?
-
A small metal crucible is taken from a 200C oven and immersed in a tub full of water at room temperature (this process is often referred to as quenching). What is the approximate final equilibrium...
-
How can CPA aid management decision making?
-
You are a government controller. A division manager being audited objects to the transfer price he is being charged by the audit group for the audit services. The manager observes, If I have to pay...
-
Hello, I'm trying to double check all my homework, and can't quite find an answer to this one. If an estimate of a contingent loss made in good faith turns out to be incorrect, the financial...
-
In the figure above, if Mac and Izzie each initially start at point a and then both completely specialize and trade with each other what are the gains from trade? 24 computers and 24 TV sets per...
-
Problem 20-4A (Algo) Manufacturing: Preparation of a complete master budget LO P1, P2, P3 The management of Zigby Manufacturing prepared the following balance sheet for March 31 Cash Assets ZIGBY...
-
Why is it cheaper per unit to produce more of a product?
-
Can all costs be clearly categorized as fixed or variable costs?
-
The Financial Accounting Standards Board worked many years to develop a conceptual framework for U.S. GAAP. a. Identify the financial statement elements that are specified in the conceptual...
-
It is assumed that a specific decision affecting one product will not affect other products. Do you think this will always hold true?
-
What are the business consequences of classifying variable and fixed costs incorrectly?
-
Question 3 Expected sales for the upcoming five months (March July, 2023) in "GBB" company is as follows. Months March Amount RO 40,000 April 44,000 May 50,000 June 56,000 July 60,000 Sales policy is...
-
For the given transfer function: Vo(s) / Vi(s) = (s^2C^2R^2 + 1) / (s^2C^2R^2 + 4sCR + 1) Assumiing that 1/(CR) = 120 PI so write the matlab code to find the magnitude plot
-
How likely are the vulnerabilities you identified in question 12-3? If you were the owner or a senior manager in these organizations, which of the items you described in questions 12-4 through 12-7...
-
Using the same four main actors, this time show with the arrows how the material (the bikes) moves.
-
What types of skills are necessary to use this system?
-
The chapter says that a measurement system can affect a business in several ways. As an example, consider a worldwide company with headquarters in New York City and divisions in many different...
-
Under the No Child Left Behind Act, passed in 2001, every public elementary school that receives some federal funding is required to give its students certain tests of reading and math every year....
-
Consider a college transcript as the output of a measurement system. A. Identify the: a. Object being measured b. Attribute of the object being measured c. Rules for measurement d. Standard-setters...
Study smarter with the SolutionInn App