程序代写代做代考 Excel ACSC12-201/ACSC71-201 FINANCIAL MATHEMATICS
ACSC12-201/ACSC71-201 FINANCIAL MATHEMATICS
Assignment 2
Due 4pm March 29th, 2017
Submit outside reception at Level 2 in Building 2
Assignment Cover Sheet needed
No late assignments will be accepted
This assignment is to be done alone
Attempt all questions
Total mark is 50
Worth 15% to final grade
Q1. (10 marks)
Prepare a spreadsheet to show a repayment schedule for a loan of
$100,000 repayable by quarterly instalments over 25 years calculated
at an interest rate of 8.4% per annum convertible quarterly.
Q2. (10 marks)
(a) Use an EXCEL spread sheet to calculate the Net Present Value
and Discounted Payback Period of the following project at 8% per
annum effective.
Date Cash Flow
1/1/1994 -$1 million
1/6/1994 -500,000
15/8/1994 +300,000
15/12/1994 +250,000
15/12/1995 +200,000
15/6/1996 +250,000
15/12/1996 +270,000
20/7/1997 +220,000
20/5/1998 +180,000
20/4/1999 +120,000
15/3/2000 +80,000
15/8/2000 -50,000
(b) Use your spread sheet to determine the internal rate of return
correct to 0.1% per annum.
Q3. (10 marks)
The following table describes the cash flow income from each of 4
projects. Each project has an initial cost of $100,000.
CFs at Year End Project A Project B Project C Project D
1 8,000 4,000 25,046 46,520
2 8,000 4,160 25,046 34,890
3 8,000 4,326 25,046 23,260
4 108,000 126,345 25,046
Use a spread sheet to calculate the net present value of the projects
under the assumption of each of the following 4 effective annual interest
rates:
2%, 6% , 10%, 14%
Can you explain why the NPV of project B changes most and the NPV
of project D changes least?
Q4. (10 marks)
Consider a loan of $75,000 repayable by equal quarterly instalments
over 15 years calculated at an interest rate of 8.8% per annum convert-
ible quarterly.
From the point of view of the lender, set out a cash flow model in
tabular form which covers repayments in the 10-th year of the loan
allowing for tax at 36% on the interest component of the loan.
Treating the opening balance as a payment and the closing balance as
a receipt, calculate the after tax internal rate of return achieved by the
lender on its investment over this loan year to an accuracy of 0.01%
per annum.
Q5. (10 marks)
Consider an institution which has a liability of a series of payments
of $100,000 per annum in arrear for 20 years. Government bonds with
annual coupons of 6% and 5 or 15 years to maturity can be purchased at
par. Calculate the present value, duration and convexity of this liability
at 6% per annum effective and the mix of 15 and 5 year bonds required
to match the duration of the liabilities. Estimate the convexity of
liabilities and assets by calculating present values at 5.9% and 6.1%
per annum. Note whether the conditions for immunisation were met
(or not) with this asset mix and whether the sign of the change in net
present value of assets less liabilities corresponds.
2
