You are considering taking out a 10 year lease on a piece of specialised farming equipment.
The lease will
 have an interest rate of 6% p.a. convertible monthly
 be for an amount financed of $1,000,000
 have a residual payment of 20% of the amount financed paid at the end of the term (time 10 years) and
by making this payment the lessee can buy the equipment. However the lessee doesn’t have to buy the
equipment, and they can decide to not make the payment of the residual value and let the lessor keep the
equipment instead.
 be taken out on 1 january 2017
 be structured so as to have lease rental payments of level amount only in the months of September,
October, November, and December of each year, paid on the first day of those months. The lease
payment months coincide with when the farmer is harvesting the crop and receiving the revenues from
selling it.
You may decide to exit the lease contract early, at any time from exactly 5 years after the lease contract is
entered into. However if you do, you must make a penalty payment equal to the present value of the future
lease cashflows, This present value is computed at an interest rate of 3% p.a. convertible monthly.
(i) Assuming that the residual payment will definitely be paid, show that the equation of value for the lease
rentals payable in the months of September, October, November, and December is
7 1 1 1 1
1 1 0.20 1
n
i j n
R i jL L j
i j
 
                                     
where
 R is the amount of the lease payment made in the months of September, October, November, and
December, and
 L is the amount financed (cost of the asset being leased) and
 i is the interest rate per month and
 j is the effective annual rate of interest equivalent to a rate of i per month
 n is the the term of the lease in years
6 marks
(ii) What excel function could you use to compute j from i? Show that i = 0.5% and j = 6.1678% to 4
decimal places.
2 marks
(iii) Re-arrange the above equation to make R the subject of the equation (the thing on the left hand side of
the equals sign). Check whether or not the following excel code would correctly implement the calculation
of R when i=6% p.a. convertible monthly. In doing this identify which parts of the excel code compute
which parts of the above equation of value for R. The excel code is
=1e6*(1-0.2*PV(0.061678, 10,0,-1,0))/(PV(0.06/12,4,-1,0,0)*PV(0.06/12,7,0,-1,0)*PV(0.061678,10,-1,0,1))
6 marks
(iv) Check if it is true that R = $43,024.54 is the monthly payment made in the months of September,
October, November, and December. If this result is incorrect, what is the correct result for the monthly
payment due in those months?
4 marks
(v) Write down a mathematical formula (in terms of the rental R from part (iv)) for the penalty payment you
would need to make at time 5 years if you wanted to terminate the lease early.
Use this information to compute the penalty payment made at time 5 years if you decide to terminate the
lease at that time. Do this in a spreadsheet
6 marks
(vi) The above formulae assume that the residual payment will definitely be paid at the end of the lease term.
If instead the lessee can choose whether or not to make the payment how would that impact on the validity
of the formulae? Would the formulae still be valid? give reasons for your answer.
6 marks
Question 3: 40 marks
Part A
You work for a bank. Your employer pays a percentage c=20% of your pretax salary of S= $90,000 into
a superannuation fund. The contribution into the super fund would thus be $18,000 per year, and it is
paid in monthly. The superannuation fund pays tax on this money at a rate of t1=15%, so after the
contributions tax, it receives an annual contribution after tax of C1 = $15,300, paid as a monthly
contribution after tax of $1,275 per month in arrears in the first year.
Assuming that
 your salary grows at rate g1 = 4% per year, once a year in arrears
 the contributions are paid into the fund at the end of each month
 the contributions are constant in each year but increase once a year at the end of the year
 the superannuation fund invests the money till you reach retirement at age T=60, at a fund earning
rate of i=8% p.a. effective. Ignore tax on the super fund investment earnings, or equivalently,
assume this is the after tax rate of return on the investment earnings.
 you are aged exactly t=35 at entry to the superannuation fund
We want to compute the amount of money accumulated in the superfund at the age of T
(i) explain how you could use the NOMINAL function to compute the interest rate per month that is
equivalent to i=8% p.a. effective. What are the inputs to this function?
2 marks
(ii) Explain how you could use the FV function to compute the annual contribution received at the end of
1 year that has the same present value at rate i as the monthly contributions paid during the year.
What are the inputs to the FV function?
3 marks
(iii)Explain how you would compute the real interest rate that corresponds to a nominal rate of i=6% and
an inflation rate of g1=4%
1 marks
(iv)Explain how you could use the PV function to compute the present value of annual contributions
which are paid annually in arrears, which starts at $1 in year 1, and which grow at rate g per annum,
valued at rate i per annum. What are the inputs to the PV function?
3 marks
(v) Explain how you could use the FV function to compute a factor to accumulate $1 invested at time 0
at rate i per annum effective to time n in the future. What are the inputs to the FV function?
3 marks
Part B: Write a spreadsheet to do this calculation.
(i) Make
 the entry age, (a whole number)
 retirement age, (a whole number)
 salary per year at the start,
 employer contribution rate as a % of salary
 annual salary growth rate and
 the fund earning rate
 the super fund’s tax rate on contributions
into “input variables” that can be entered by the user of the spreadsheet and so that these can be changed
to other values.
The spreadsheet should be able to do similar calculations for any valid set of inputs
10
 Hence compute the amount accumulated in the super fund by retirement age
 What difference would it make to your result if the contributions were paid into the fund at the start
of each year instead of at the end of each month during the year for each year? compute the
accumulated lump sum in this case
14 marks
(ii) Using the following assumptions do a sensitivity analysis of the ratio of the accumulated lump
sum achieved at retirement to the salary received in the final year before retirment
Variable Base case Pessimistic value Optimistic value
Entry age (t) 35 40 30
Planned retirement age (T) 60 55 65
Annual salary at entry to super fund (S) $90,000.00 $75,000.00 $108,000.00
Employer contribution rate (c) 20% 15% 25%
Salary growth rate (g) 4% 2% 6%
Fund earning rate (i) 8% 4% 12%
 create a brief table setting out the results of the sensitivity analysis, and comment on which variables
the ratio is most sensitive to
7 marks
 create a 2 way table showing the combined effect of changing the fund earning rate (15%, 20%,
25%) and changing the term of superfund membership (15 years, 25 years and 35 years)
on the ratio of the lump sum received at retirement to the salary received in the final year before
retirement. This should be a table with 3 rows and 3 columns. Comment on the results
7 marks

Do you want your assignment written by the best essay experts? Then look no further. Our teams of experienced writers are on standby to deliver to you a quality written paper as per your specified instructions. Order now, and enjoy an amazing discount!!