Present Value, Future Value using Excel

A state has a pension fund liability of $25 billion, due in 10 years. Each year the legislature is supposed to set aside an amount to fund the pension. This annuity is based on what the legislature believes it can earn on this money. (Often pension obligations, in reality, are not funded each year, due to budget shortfalls, etc.).

a. Estimate the amount needed each year for the next 10 years, assuming that the rate that can be earned on the money is 8%.

b. Estimate the amount needed each year for the next 10 years, assuming the legislature believes pension fund managers can earn 6%.

c. Assume the legislature sets aside an amount based on fund managers earning 8%/year. However, results after 5 years indicate actual returns have only been 6%. To meet the pension fund liability of $25 billion (which will now be due in only 5 years), how much must they put aside for the remaining 5 years.