Show yournumerical answer(s) and the Excel function(s) and inputs you usedto get the answer. You may use up to 25 words (50 for#4) to supplement your numbers, tables and Excelfunctions.
1. StateRetirement Funding (5 points – 1 page with table, functions and 25words)
A state retirement plan has beenfrozen. It is considered fully-funded, with$635,244,352.26 of assets on hand and makes payouts to 1,000recipients. It assumes it will earn 7.5% per year onthese assets. The most recent total payout was$50,000,000. Next year it will be $51,000,000, which includes a 2%COLA increase in benefits. This payout amount isscheduled to increase by 2% per year for inflation. Allinterest earned and payments occur at the end of theyear. For this cohort of retirees the final payment willbe made in exactly22 years from today. The fund balanceat that time will be zero.
The effective ratefor annuities like this is RATE = .
The PV was calculated as=PV(RATE,22,-50000000,0,0)
- Create an amortization table that shows the pension isfully-funded.
- Suppose that instead of 7.5% the assets earn 5% peryear. By how much is the pension under-funded assumingthe 2% COLA adjustment continues.
- At a 5% growth rate what total annual payments can the originalasset balance support for 22 years with no inflationadjustment? I.e., the same amount each year.
- Given the initial balance of $635,244,352.26 and assuming a 2%COLA increase ever year, what initial payment can be made tobeneficiaries?
