Among its many financial products, the Prudent Financial Services Corporation (normally referred to as PFS) manages a well-regarded pension fund that is used by a number of companies to provide pensions for their employees. PFS€™s management takes pride in the rigorous professional standards used in operating the fund. Since the near collapse of the financial markets during the protracted Great Recession that began in late 2007, PFS has redoubled its efforts to provide prudent management of the fund. It is now December 2013. The total pension payments that will need to be made by the fund over the next 10 years are shown in the table below.

By using interest as well, PFS currently has enough liquid assets to meet all these pension payments. Therefore, to safeguard the pension fund, PFS would like to make a number of investments whose payouts would match the pension payments over the next 10 years. The only investments that PFS trusts for the pension fund are a money market fund and bonds. The money market fund pays an annual interest rate of 2 percent. The characteristics of each unit of the four bonds under consideration are shown in the next table.

All of these bonds will be available for purchase on January 1, 2014, in as many units as desired. The coupon rate is the percentage of the face value that will be paid in interest on January 1 of each year, starting one year after purchase and continuing until (and including) the maturity date. Thus, these interest payments on January 1 of each year are in time to be used toward the pension payments for that year. Any excess interest payments will be deposited into the money market fund. To be conservative in its financial planning, PFS assumes that all the pension payments for the year occur at the beginning of the year immediately after these interest payments (including a year€™s interest from the money market fund) are received. The entire face value of a bond also will be received on its maturity date. Since the current price of each bond is less than its face value, the actual yield of the bond exceeds its coupon rate. Bond 3 is a zero-coupon bond, so it pays no interest but instead pays a face value on the maturity date that greatly exceeds the purchase price.
PFS would like to make the smallest possible investment (including any deposit into the money market fund) on January 1, 2014, to cover all its required pension payments through 2023. Some spreadsheet modeling needs to be done to see how to do this.
(a) Visualize where you want to finish. What numbers are needed by PFS management? What are the decisions that need to be made? What should the objective be?
(b) Suppose that PFS were to invest $28 million in the money market fund and purchase 10,000 units each of bond 1 and bond 2 on January 1, 2014. Calculate by hand the payments received from bonds 1 and 2 on January 1 of 2015 and 201 6. Also calculate the resulting balance in the money market fund on January 1 of 2014, 2015, and 2016 after receiving these payments, making the pension payments for the year, and depositing any excess into the money market fund.
(c) Make a rough sketch of a spreadsheet model, with blocks laid out for the data cells, changing cells, output cells, and objective cell.
(d) Build a spreadsheet model for years 2014 through 2016, and then thoroughly test the model.
(e) Expand the model to consider all years through 2023, and then solve it.

Transcribed Image Text:

Year Pension Payments (S milllons) 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 12 13 14 16 17 20 21 24 Current Price Coupon Maturity Date Face Value Bond 1 Bond 2 Bond 3 Bond 4 980 920 750 800 Rate 4% 0 Jan. 1, 2015 Jan. 1, 2017 Jan. 1, 2019 Jan. 1, 2022 1,000 1,000 1,000 1,000