EXCEL WITH FORMULAS

1. Yianni has funds in his superannuation account and is considering purchasing a pension. In exchange for a lump sum payment now, Polysuper offers an annual pension over twenty-five years beginning with a payment of $61,000 at the end of the first year. There are twenty-five payments in total and the payments will increase at an annual rate of 2.6%pa. The appropriate opportunity cost of funds is j2 = 9.36 %pa what is the amount of the lump sum needed today to purchase this pension?

2. Your supervisor has asked you to do the following calculations:

a)A ninety-day bank bill with 90 days to maturity has a price of $97915. What is the effective annual yield implied by this price and maturity? What is the annual nominal yield? Face value is $100,000. Make sure your answers are clearly labelled. CH

b)The All Ordinaries price index opened the year at 5428 and closed at 6113 by the end of the year. What is the annual rate of return on the index?

c) Using the approach covered in your textbook calculate the geometric average annual rate of return over four years given the following annual rates, year 1 = 4.39%, year 2 = 5.63%, year 3 = 6.25%, year 4 = 5.99%.

d)Polycorp dividends per share have increased from $6.15 to $13.62 over eight years. Calculate the annual compounded growth rate in dividends over that period.

3. Polycorp Treasury a company in the land of Zanadu is holding a parcel of Zanadu Government Bonds with a face value of $3,000,000.The bonds were issued six years and nine months ago, with an initial maturity of 10 years.They pay a coupon rate of interest of 5.55% pa, with interest paid semi-annually. Currently the market yield quoted for Zanadu bonds is 3.82% pa.The convention in Zanadu financial markets is that the market yield and coupon rate are quoted as annual nominal rates.What is the current market value of the bonds?

4.Polycorp has a dividend of $6.00 due in a year's time. It is expected to pay a dividend $6.50 at the end of the second year.Its dividends will grow at 6.5%pa for the following four years. Dividends are then expected to grow at 3.25%pa for another two years, after which they are expected to grow at 2.5%pa forever. Shareholders required return on equity is 10.25% pa.What is the current price (cum-dividend) of Polycorp shares? D0 was $5.

5. The required rate of return on the shares in the companies identified below is 12.25% pa. Calculate the current share price in each case.

(a)The current earnings per share of Alpha Ltd are $2.60. The company does not reinvest any of its earnings. Earnings are expected to remain constant.

(b)Beta Ltd's current dividend is $2.25 (D0) and dividends are expected to grow at 5% pa indefinitely.

(c)Gamma Ltd is not expecting to pay dividends for four years, at the end of year five a dividend of $2.60 is planned and dividends are expected to grow at 2.75% pa forever after that.

(d)Delta limited plans to pay dividends of 1.55, 2.35, and 3.55 at the end of years 3, 4, 5 respectively followed by a dividend of 4.29 pa in perpetuity after that.

6. You wish to insure your Ferrari.Mooncorp Insurance has quoted you an annual premium to insure your car of $12915. You are offered a 12% discount if you pay the lump sum immediately. They also offer an alternative payment method.You can pay the account in full by making 11 equal end-of-the month payments of $1174, rather than the lump sum, with no payment in the first month (i.e. the first payment is at the end of the second month followed by ten further monthly payments). What is the effective annual opportunity cost of paying monthly? In other words, what interest rate are you being charged if you decide to use the repayment plan as opposed to the lump sum?

You must provide one complete manual trial calculation of the IRR to demonstrate that you understand the process.Failure to follow this instruction will attract a mark of zero.