# 程序代写代做代考 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