Consider a certain defined benefit pension plan:

(1) Employees work for 45 years earning salaries that increase at the yearly real rate of 2% (= the yearly nominal rate - the yearly inflation rate).

(2) The pension fund is invested in bonds, which earn the yearly real rate of 1.5%.

(3) They retire with an annuity equal to 70% of their final salaries. This annuity decreases at the yearly real rate of 1%.

(4) The annuities are received for 18 years.

Calculate (using Excel) the percentage R of an employee's salary that must be contributed to the pension plan if it is to remain solvent.

Assume that the initial salary is $100,000 per year. Note that the salary of the employee makes no difference to the answer. This is because it has the effect of scaling all numbers up or down.

Do all calculations in real rather than nominal dollars.

(Given that the required contribution rate R is 25.02% per year / per month by both the owner and an employee.)