Accounting for pension schemes

profilejammie
fap_mod3exercise01-02careerprojections.xlsx
Home>Business & Finance homework help>Accounting homework help>Accounting for pension schemes

Input

Society of Actuaries
Module 3 Exercise - Chocolate Factory
Defined Benefit Plan vs Defined Contribution Plan
Input Fields
Calculated Fields
Worksheet Worksheet
Member Data Defaults External Forces Defaults Variable Names
Age (enter integral age): 30 30 Average Wage: $50,000 $50,000
Sex (enter "M" or "F"): M M CPP Covered Earnings: 100,000 Age 30
Current Year ("YYYY"): 2006 2006 Maximum Pension Earnings: $200,000 Sex M
Prior Year Earnings: $65,000 $65,000 Year 2006
Past Service (enter years): 0 0 PriorYrEarnings $65,000
Initial Account Balance: $0 $0 PastSvYears 0
IAB $0
Projection Parameters Defined Benefit Accrual Rate - FROR 6.50%
Fund Return: 6.50% 6.50% up to covered earnings 1.30% 1.30% InflRate 3.50%
Inflation Rate: 3.50% 3.50% in excess of covered earnings 1.75% 1.75% AnnIncPenEaringsRate 4.50%
AnnIncCPPCovEarningsRate 4.00%
Annual Increase in: Defined Contribution Rate - AnnIncMaxPenEarningsRate 3.50%
Pensionable Earnings:* 4.50% up to covered earnings 12.00% 12.00% AvgWage $50,000
CPP Covered Earnings:** 4.00% in excess of covered earnings 17.00% 17.00% CPP_Covered_Earnings 100,000
Maximum Pension Earnings: 3.50% Maximum_Pension_Earnings $200,000
dbaccrualrate1 1.30%
dbaccrualrate2 1.75%
dccontnrate1 12.00%
dccontrate2 17.00%
* Earnings rates from age 50 to age 60 are 0.50% less per annum and from age 60 to age 65 are 1.0% less per annum
** Individual CPP covered earnings increases are limited to the increase in inflation after age 55

Results

Society of Actuaries
Module 3 Exercise - Chocolate Factory
Results
Note to Candidates: This worksheet is locked and cannot be modified.
Calculated Fields
Member Data Replacement Ratios at 65
Age: 30 Private Defined Benefit 44%
Sex: M Total Defined Benefit 68%
Current Year: 2006
Prior Year Earnings: $65,000 Private Defined Contribution 44%
Past Service: 0 Total Defined Contribution 68%
Initial Account Balance: 0
Projection Parameters Defined Benefit Accrual Rate -
Fund Return: 6.50% up to covered earnings 1.30%
Inflation Rate: 3.50% in excess of covered earnings 1.75%
Annual Increase in: Defined Contribution Rate -
Pensionable Earnings:* 4.50% up to covered earnings 12.00%
CPP Covered Earnings:** 4.00% in excess of covered earnings 17.00%
Maximum Pension Earnings: 3.50%
* Earnings rates from age 50 to age 60 are 0.50% less per annum and from age 60 to age 65 are 1.0% less per annum
** Individual CPP covered earnings increases are limited to the increase in inflation after age 55

Calcs

Society of Actuaries
Module 3 Exercise - Chocolate Factory
Calculations
Earnings Defined Benefit Pension Projections Defined Contribution Pension Projections
Private CPP Total Estimated Private CPP Total
Credited Pensionable CPP Covered Maximum Final Final Final Annual Total Pension CPP Pension Pension Annual Accumulated Deferred Private Pension CPP Pension Pension
Service in Current in Current in Current Pensionable CPP Covered Maximum Pension Accrued Percent Pension Percent Percent Contribution Account Annuity Pension Percent Pension Percent Percent
Year Age (years) Year Year Year Earnings Earnings Earnings Accrual Pension Accrual to age Accrual Accrual Amount Balance Factor at 65 Accrual Accrued Accrual Accrual
2006 30 0 65,000 100,000 200,000 62,201 95,694 191,388 808.61 0 0.00 15,550 0.24 0.24 7,800.00 0.00 1.59 0 0.00 15,550 0.24 0.24
2007 31 1 67,925 104,000 207,000 65,000 100,000 200,000 845.00 845 0.01 16,250 0.24 0.25 8,151.00 8,053.50 1.69 4,757 0.07 16,250 0.24 0.31
2008 32 2 70,982 108,160 214,245 67,925 104,000 207,000 883.03 1,766 0.02 16,981 0.24 0.26 8,517.84 16,992.89 1.80 9,425 0.13 16,981 0.24 0.37
2009 33 3 74,176 112,486 221,744 70,982 108,160 214,245 922.77 2,768 0.04 17,746 0.24 0.28 8,901.12 26,892.09 1.92 14,005 0.19 17,746 0.24 0.43
2010 34 4 77,514 116,985 229,505 74,176 112,486 221,744 964.29 3,857 0.05 18,544 0.24 0.29 9,301.68 37,830.48 2.05 18,498 0.24 18,544 0.24 0.48
2011 35 5 81,002 121,664 237,538 77,514 116,985 229,505 1,007.68 5,038 0.06 19,379 0.24 0.30 9,720.24 49,893.45 2.18 22,908 0.28 19,379 0.24 0.52
2012 36 6 84,647 126,531 245,852 81,002 121,664 237,538 1,053.03 6,318 0.07 20,251 0.24 0.31 10,157.64 63,172.67 2.32 27,235 0.32 20,251 0.24 0.56
2013 37 7 88,456 131,592 254,457 84,647 126,531 245,852 1,100.41 7,703 0.09 21,162 0.24 0.33 10,614.72 77,766.66 2.47 31,480 0.36 21,162 0.24 0.60
2014 38 8 92,437 136,856 263,363 88,456 131,592 254,457 1,149.93 9,199 0.10 22,114 0.24 0.34 11,092.44 93,781.19 2.63 35,646 0.39 22,114 0.24 0.62
2015 39 9 96,597 142,330 272,581 92,437 136,856 263,363 1,201.68 10,815 0.11 23,109 0.24 0.35 11,591.64 111,329.91 2.80 39,734 0.41 23,109 0.24 0.65
2016 40 10 100,944 148,023 282,121 96,597 142,330 272,581 1,255.76 12,558 0.12 24,149 0.24 0.36 12,113.28 130,534.73 2.98 43,744 0.43 24,149 0.24 0.67
2017 41 11 105,486 153,944 291,995 100,944 148,023 282,121 1,312.27 14,435 0.14 25,236 0.24 0.38 12,658.32 151,526.44 3.18 47,680 0.45 25,236 0.24 0.69
2018 42 12 110,233 160,102 302,215 105,486 153,944 291,995 1,371.32 16,456 0.15 26,372 0.24 0.39 13,227.96 174,445.38 3.38 51,541 0.47 26,372 0.24 0.71
2019 43 13 115,193 166,506 312,793 110,233 160,102 302,215 1,433.03 18,629 0.16 27,558 0.24 0.40 13,823.16 199,442.20 3.60 55,331 0.48 27,558 0.24 0.72
2020 44 14 120,377 173,166 323,741 115,193 166,506 312,793 1,497.51 20,965 0.17 28,798 0.24 0.41 14,445.24 226,678.35 3.84 59,048 0.49 28,798 0.24 0.73
2021 45 15 125,794 180,093 335,072 120,377 173,166 323,741 1,564.90 23,474 0.19 30,094 0.24 0.43 15,095.28 256,327.16 4.09 62,697 0.50 30,094 0.24 0.74
2022 46 16 131,455 187,297 346,800 125,794 180,093 335,072 1,635.32 26,165 0.20 31,449 0.24 0.44 15,774.60 288,574.30 4.35 66,276 0.50 31,449 0.24 0.74
2023 47 17 137,370 194,789 358,938 131,455 187,297 346,800 1,708.92 29,052 0.21 32,864 0.24 0.45 16,484.40 323,618.90 4.64 69,788 0.51 32,864 0.24 0.75
2024 48 18 143,552 202,581 371,501 137,370 194,789 358,938 1,785.81 32,145 0.22 34,343 0.24 0.46 17,226.24 361,674.27 4.94 73,235 0.51 34,343 0.24 0.75
2025 49 19 150,012 210,684 384,504 143,552 202,581 371,501 1,866.18 35,457 0.24 35,888 0.24 0.48 18,001.44 402,969.19 5.26 76,616 0.51 35,888 0.24 0.75
2026 50 20 156,763 219,111 397,962 150,012 210,684 384,504 1,950.16 39,003 0.25 37,503 0.24 0.49 18,811.56 447,748.68 5.60 79,935 0.51 37,503 0.24 0.75
2027 51 21 163,034 227,875 411,891 156,763 219,111 397,962 2,037.92 42,796 0.26 39,191 0.24 0.50 19,564.08 496,275.28 5.97 83,191 0.51 39,191 0.24 0.75
2028 52 22 169,555 236,990 426,307 163,034 227,875 411,891 2,119.44 46,628 0.28 40,759 0.24 0.52 20,346.60 548,733.08 6.35 86,370 0.51 40,759 0.24 0.75
2029 53 23 176,337 246,470 441,228 169,555 236,990 426,307 2,204.22 50,697 0.29 42,389 0.24 0.53 21,160.44 605,408.60 6.77 89,475 0.51 42,389 0.24 0.75
2030 54 24 183,390 256,329 456,671 176,337 246,470 441,228 2,292.38 55,017 0.30 44,084 0.24 0.54 22,006.80 666,608.31 7.21 92,507 0.50 44,084 0.24 0.74
2031 55 25 190,726 266,582 472,654 183,390 256,329 456,671 2,384.07 59,602 0.31 45,848 0.24 0.55 22,887.12 732,659.87 7.67 95,467 0.50 45,848 0.24 0.74
2032 56 26 198,355 275,912 489,197 190,726 266,582 472,654 2,479.44 64,465 0.32 47,682 0.24 0.57 23,802.60 803,913.72 8.17 98,359 0.50 47,682 0.24 0.74
2033 57 27 206,289 285,569 506,319 198,355 275,912 489,197 2,578.62 69,623 0.34 49,589 0.24 0.58 24,754.68 880,744.29 8.70 101,182 0.49 49,589 0.24 0.73
2034 58 28 214,541 295,564 524,040 206,289 285,569 506,319 2,681.76 75,089 0.35 51,572 0.24 0.59 25,744.92 963,551.88 9.27 103,939 0.48 51,572 0.24 0.72
2035 59 29 223,123 305,909 542,381 214,541 295,564 524,040 2,789.03 80,882 0.36 53,635 0.24 0.60 26,774.76 1,052,764.38 9.87 106,631 0.48 53,635 0.24 0.72
2036 60 30 232,048 316,616 561,364 223,123 305,909 542,381 2,900.60 87,018 0.38 55,781 0.24 0.62 27,845.76 1,148,839.00 10.51 109,261 0.47 55,781 0.24 0.71
2037 61 31 240,170 327,698 581,012 232,048 316,616 561,364 3,016.62 93,515 0.39 58,012 0.24 0.63 28,820.40 1,252,264.29 11.20 111,828 0.47 58,012 0.24 0.71
2038 62 32 248,576 339,167 601,347 240,170 327,698 581,012 3,122.21 99,911 0.40 60,043 0.24 0.64 29,829.12 1,363,418.53 11.93 114,323 0.46 60,043 0.24 0.70
2039 63 33 257,276 351,038 622,394 248,576 339,167 601,347 3,231.49 106,639 0.41 62,144 0.24 0.66 30,873.12 1,482,839.30 12.70 116,748 0.45 62,144 0.24 0.70
2040 64 34 266,281 363,324 644,178 257,276 351,038 622,394 3,344.59 113,716 0.43 64,319 0.24 0.67 31,953.72 1,611,100.35 13.53 119,105 0.45 64,319 0.24 0.69
2041 65 35 275,601 376,040 666,724 266,281 363,324 644,178 3,461.65 121,158 0.44 66,570 0.24 0.68 33,072.12 1,748,814.09 14.41 121,395 0.44 66,570 0.24 0.68
2042 66 36 285,247 389,201 690,059 275,601 376,040 666,724 3,582.81 128,981 0.45 68,900 0.24 0.69 34,229.64 1,896,633.97 14.41 131,656 0.46 68,900 0.24 0.70
2043 67 37 295,231 402,823 714,211 285,247 389,201 690,059 3,708.21 137,204 0.46 71,312 0.24 0.71 35,427.72 2,055,257.28 14.41 142,667 0.48 71,312 0.24 0.72
2044 68 38 305,564 416,922 739,208 295,231 402,823 714,211 3,838.00 145,844 0.48 73,808 0.24 0.72 36,667.68 2,225,428.12 14.41 154,479 0.51 73,808 0.24 0.75
2045 69 39 316,259 431,514 765,080 305,564 416,922 739,208 3,972.33 154,921 0.49 76,391 0.24 0.73 37,951.08 2,407,940.33 14.41 167,148 0.53 76,391 0.24 0.77
2046 70 40 327,328 446,617 791,858 316,259 431,514 765,080 4,111.37 164,455 0.50 79,065 0.24 0.74 39,279.36 2,603,640.94 14.41 180,733 0.55 79,065 0.24 0.79
2047 71 41 338,784 462,249 819,573 327,328 446,617 791,858 4,255.26 174,466 0.51 81,832 0.24 0.76 40,654.08 2,813,433.54 14.41 195,296 0.58 81,832 0.24 0.82
2048 72 42 350,641 478,428 848,258 338,784 462,249 819,573 4,404.19 184,976 0.53 84,696 0.24 0.77 42,076.92 3,038,282.06 14.41 210,904 0.60 84,696 0.24 0.84
2049 73 43 362,913 495,173 877,947 350,641 478,428 848,258 4,558.33 196,008 0.54 87,660 0.24 0.78 43,549.56 3,279,214.81 14.41 227,628 0.63 87,660 0.24 0.87
2050 74 44 375,615 512,504 908,675 362,913 495,173 877,947 4,717.87 207,586 0.55 90,728 0.24 0.79 45,073.80 3,537,328.70 14.41 245,546 0.65 90,728 0.24 0.90
2051 75 45 388,762 530,442 940,479 375,615 512,504 908,675 4,883.00 219,735 0.57 93,904 0.24 0.81 46,651.44 3,813,793.76 14.41 264,736 0.68 93,904 0.24 0.92
2052 76 46 402,369 549,007 973,396 388,762 530,442 940,479 5,053.91 232,480 0.58 97,191 0.24 0.82 48,284.28 4,109,857.97 14.41 285,288 0.71 97,191 0.24 0.95
2053 77 47 416,452 568,222 1,007,465 402,369 549,007 973,396 5,230.80 245,848 0.59 100,592 0.24 0.83 49,974.24 4,426,852.26 14.41 307,292 0.74 100,592 0.24 0.98
2054 78 48 431,028 588,110 1,042,726 416,452 568,222 1,007,465 5,413.88 259,866 0.60 104,113 0.24 0.84 51,723.36 4,766,196.05 14.41 330,848 0.77 104,113 0.24 1.01
2055 79 49 446,114 608,694 1,079,221 431,028 588,110 1,042,726 5,603.36 274,565 0.62 107,757 0.24 0.86 53,533.68 5,129,403.17 14.41 356,060 0.80 107,757 0.24 1.04
2056 80 50 461,728 629,998 1,116,994 446,114 608,694 1,079,221 5,799.48 289,974 0.63 111,529 0.24 0.87 55,407.36 5,518,087.90 14.41 383,041 0.83 111,529 0.24 1.07
2057 81 51 477,888 652,048 1,156,089 461,728 629,998 1,116,994 6,002.46 306,125 0.64 115,432 0.24 0.88 57,346.56 5,933,971.71 14.41 411,910 0.86 115,432 0.24 1.10
2058 82 52 494,614 674,870 1,196,552 477,888 652,048 1,156,089 6,212.54 323,052 0.65 119,472 0.24 0.89 59,353.68 6,378,890.20 14.41 442,794 0.90 119,472 0.24 1.14
2059 83 53 511,925 698,490 1,238,431 494,614 674,870 1,196,552 6,429.98 340,789 0.67 123,654 0.24 0.91 61,431.00 6,854,800.73 14.41 475,830 0.93 123,654 0.24 1.17
2060 84 54 529,842 722,937 1,281,776 511,925 698,490 1,238,431 6,655.03 359,372 0.68 127,981 0.24 0.92 63,581.04 7,363,790.29 14.41 511,161 0.96 127,981 0.24 1.21
2061 85 55 548,386 748,240 1,326,638 529,842 722,937 1,281,776 6,887.95 378,837 0.69 132,461 0.24 0.93 65,806.32 7,908,084.08 14.41 548,944 1.00 132,461 0.24 1.24
2062 86 56 567,580 774,428 1,373,070 548,386 748,240 1,326,638 7,129.02 399,225 0.70 137,097 0.24 0.94 68,109.60 8,490,054.57 14.41 589,342 1.04 137,097 0.24 1.28
2063 87 57 587,445 801,533 1,421,127 567,580 774,428 1,373,070 7,378.54 420,577 0.72 141,895 0.24 0.96 70,493.40 9,112,231.28 14.41 632,530 1.08 141,895 0.24 1.32
2064 88 58 608,006 829,587 1,470,866 587,445 801,533 1,421,127 7,636.79 442,934 0.73 146,861 0.24 0.97 72,960.72 9,777,310.75 14.41 678,697 1.12 146,861 0.24 1.36
2065 89 59 629,286 858,623 1,522,346 608,006 829,587 1,470,866 7,904.08 466,341 0.74 152,002 0.24 0.98 75,514.32 10,488,167.89 14.41 728,042 1.16 152,002 0.24 1.40
2066 90 60 651,311 888,675 1,575,628 629,286 858,623 1,522,346 8,180.72 490,843 0.75 157,322 0.24 1.00 78,157.32 11,247,867.34 14.41 780,777 1.20 157,322 0.24 1.44
2067 91 61 674,107 919,779 1,630,775 651,311 888,675 1,575,628 8,467.04 516,489 0.77 162,828 0.24 1.01 80,892.84 12,059,676.15 14.41 837,129 1.24 162,828 0.24 1.48
2068 92 62 697,701 951,971 1,687,852 674,107 919,779 1,630,775 8,763.39 543,330 0.78 168,527 0.24 1.02 83,724.12 12,927,076.96 14.41 897,340 1.29 168,527 0.24 1.53

Annuity Factors

Society of Actuaries
Module 3 Exercise - Chocolate Factory
Annuity Factor Summary Tables
Note to Candidates: This worksheet is locked and cannot be modified.
Mortality Table UP94 Projected to 2005
Form of Pension:
Life Pension payable during the member's life only
Life-10 Pension payable during the member's life with 120 monthly payments guaranteed
J&S(50) Pension payable during member's life and continuing at 50% on the member's death to a surviving spouse for the spouse's lifetime
Blended Blended 80% J&S and 20% Life-10 to reflect average marital status in Cascadia
All pensions increase at 50% of expected inflation
Male 65 Female 65
Interest Rate Inflation Rate Life Only Life-10 J&S(50) Blended Life Only Life-10 J&S(50) Blended
3.000% 1.500% 15.5568 16.3294 18.0536 17.7088 17.7573 18.2688 19.1539 18.9769
3.250% 1.625% 15.3626 16.1287 17.8027 17.4679 17.5103 18.0176 18.8766 18.7048
3.500% 1.750% 15.1722 15.9321 17.5573 17.2323 17.2687 17.7718 18.6055 18.4388
3.750% 1.875% 14.9856 15.7391 17.3170 17.0014 17.0323 17.5312 18.3404 18.1786
4.000% 2.000% 14.8028 15.5502 17.0821 16.7757 16.8012 17.2959 18.0813 17.9242
4.250% 2.125% 14.6237 15.3649 16.8522 16.5547 16.5750 17.0657 17.8279 17.6755
4.500% 2.250% 14.4480 15.1831 16.6273 16.3385 16.3537 16.8404 17.5801 17.4322
4.750% 2.375% 14.2758 15.0049 16.4071 16.1267 16.1372 16.6198 17.3378 17.1942
5.000% 2.500% 14.1068 14.8300 16.1914 15.9191 15.9251 16.4038 17.1005 16.9612
5.250% 2.625% 13.9414 14.6587 15.9805 15.7161 15.7178 16.1926 16.8687 16.7335
5.500% 2.750% 13.7790 14.4904 15.7739 15.5172 15.5146 15.9856 16.6417 16.5105
5.750% 2.875% 13.6199 14.3255 15.5717 15.3225 15.3159 15.7830 16.4197 16.2924
6.000% 3.000% 13.4637 14.1636 15.3736 15.1316 15.1212 15.5846 16.2024 16.0788
6.250% 3.000% 13.1602 13.8489 14.9896 14.7615 14.7439 15.1998 15.7814 15.6651
6.500% 3.000% 12.8680 13.5457 14.6211 14.4060 14.3819 14.8304 15.3780 15.2685
6.750% 3.000% 12.5867 13.2535 14.2673 14.0645 14.0344 14.4758 14.9912 14.8881
7.000% 3.000% 12.3155 12.9718 13.9274 13.7363 13.7007 14.1350 14.6199 14.5229
7.250% 3.000% 12.0543 12.7003 13.6009 13.4208 13.3802 13.8077 14.2639 14.1727
7.500% 3.000% 11.8024 12.4382 13.2870 13.1172 13.0721 13.4929 13.9219 13.8361
7.750% 3.000% 11.5595 12.1853 12.9852 12.8252 12.7759 13.1901 13.5934 13.5127
8.000% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
8.250% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
8.500% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
8.750% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
9.000% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
9.250% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
9.500% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
9.750% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
10.000% 3.000% 11.3250 11.9410 12.6947 12.5440 12.4909 12.8986 13.2777 13.2019
Note that annuity rates are limited to an underlying interest rate of 8.0% per annum