Solve the 10 questions using Excel and Manual solution both.

Questions are in Excel Sheet. 10 Questions.

Student to complete

Student

Number:

Student Name:

Assignment Number:

Part A

Title:

Assignment Part A

Tutor’s Name:

Due Date:

Day and Time of Class:

DECLARATION: By submitting this assignment I declare that:

·

This work is entirely my own, and no part of it has been copied from any other person’s work, words or ideas, except as specifically acknowledged through the use

of inverted commas and in-text references;

·

No part of this assignment has been written for me by any other person except where such collaboration has been authorised by the Unit Coordinator concerned;

I understand my assignment may be scanned as part of the assessment process, and that plagiarism detection software may be utilised;

·

This assignment has not been submitted for any other unit at QUT or any other institution, unless authorised by the relevant Unit Coordinator;

·

I have read and abided by all of the requirements set down for this assignment.

·

If the above declaration is found to be false, you may receive reduced or zero marks for this assignment, and you will be dealt with under QUT’s Student Rule No.

29 – Academic Dishonesty, and the associated procedures for Academic Dishonesty which are available at:

http://www.qut.edu.au/admin/mopp/Appendix/append01cst.html#Rule29 and http://www.qut.edu.au/admin/mopp/C/C_09_07.html

I acknowledge that I have read all the instructions on this declaration sheet.

Assignment A

You have just started working for Polycorp Pyramid Financial Services, and your supervisor gives you some

problems to answer/solve for clients. You should solve the following ten questions showing your full workings

and explanations (remember you are explaining to your client who knows very little about finance). Each

question is worth one mark. You must have the correct answer and show your working and correct

explanation to get one mark (very brief or one to two lines of answers are not acceptable). As well as solving

the problems manually using the appropriate formulas, you must also solve the problems using Excel

functions/calculations. An incorrect answer or an answer without the working receives zero. Include cash

flow maps or tables wherever possible. Avoid rounding error.

Read questions carefully, do not assume they are the same as other questions you have covered in this unit.

Remember: 2 answers; one using excel to do the calculations and the other, with full explanation and

manual calculations typed in a textbox. See samples on Blackboard. You must have both correctly

completed with full answers to get your mark.

EFN406: MANAGERIAL FINANCE

Assignment: Part A, Financial Mathematics and Security Valuation

See Blackboard for due date:

General Information

1.

eas, except as specifically acknowledged through the use

has been authorised by the Unit Coordinator concerned;

oftware may be utilised;

2.

3.

sed by the relevant Unit Coordinator;

and you will be dealt with under QUT’s Student Rule No.

mopp/C/C_09_07.html

aration sheet.

ur supervisor gives you some

ions showing your full workings

little about finance). Each

working and correct

cceptable). As well as solving

problems using Excel

Include cash

s you have covered in this unit.

with full explanation and

have both correctly

4.

5.

6.

7.

8.

9.

10.

11.

12.

•

•

•

Marks: 10 – ten questions each worth one mark. You must have the correct answer and a

gain the marks allocated to each question (you must provide two solutions, one as if you

the exam [but typed] and one in excel). Failure to provide both will result in a mark of ze

flow maps and/or tables wherever possible. Avoid rounding error. Providing a formula an

the working is not enough.

Weight: 10%.

Format: Calculation and brief working or short answer with explanation as well as the cal

spreadsheet. For each calculation you must provide a manual solution and a solution usin

without doing the calculations in excel does not qualify as solving in excel.

assignment with explanations in excel and upload one file. Type your explanation and ma

example file provided on Blackboard.

Word Limit: A few pages (500 words as a very rough guide; mostly calculations)

Due: see Blackboard

The assignment must be done in excel and must be your

circumstances should you use submit a hard copy.

Make sure to highlight or underline your final answer/s in some

Upload a soft copy of your Excel file to Blackboard under

compatible). Failure to upload will result in a mark of zero. Keep

your file/s then send them by email (before due date and time)

Late submissions will receive a mark of zero.

A hard copy is not required.

Try to be as accurate as possible. Unless otherwise told you should

a. PV and FV accurate to the nearest dollar

b. Prices accurate to two decimal places (to the nearest cent)

c. Rates accurate to one basis point

To avoid mixing up assignments, save and name your assignment

unit code, your last name, your first name, your student number,

for example: EFN406 last name first name n1234567 Assignment

Save your file in excel using this name and then upload it to Blackboard

2023, 1

Security Valuation

. You must have the correct answer and a correct explanation plus working, to

on (you must provide two solutions, one as if you were answering the questions for

Failure to provide both will result in a mark of zero for the question. Include cash

ble. Avoid rounding error. Providing a formula and the final answer, without showing

r short answer with explanation as well as the calculation solved in an Excel

ust provide a manual solution and a solution using excel. Typing straight into excel

oes not qualify as solving in excel. Use the excel template provided. Do all the

nd upload one file. Type your explanation and manual answer in text boxes. See the

a very rough guide; mostly calculations)

your own work (scanned documents are not acceptable). Under no

some way. (e.g. Answer = $5089)

under Assessment by the due date and time (must be Microsoft

zero. Keep a copy of your assignment. If you have problems uploading

time) to seungho.choi@qut.edu.au

you should use the following approach:

nearest cent)

assignment using the following format:

number, and assignment number

Assignment Part A

to Blackboard.

Question 1

Now that she has accumulated a deposit of $300,000, Wendy wishes to use this deposit and take out a housing loan t

home. The home costs $900,000. The loan is to be repaid in equal quarterly instalments over a term of 15 years. Wen

interest rate quoted by the bank is an annual nominal rate of 4.2%pa compounded quarterly.

to be made), Wendy gets a large bonus at work and decides to pay out what is owing on the loan.

(i) How much is the quarterly repayment?

(ii) How much is owed at the time of the pay out?

(iii) Provide Wendy with a repayment schedule using excel at the time the loan is made.

(Answers should be accurate to the nearest dollar)

s to use this deposit and take out a housing loan to purchase a

uarterly instalments over a term of 15 years. Wendy recall s that the

pa compounded quarterly. After 5 years (20th repayment just about

ut what is owing on the loan.

me the loan is made.

TEXTBOX

Type your full manual answer here.

ull manual answer here.

Question 2

John is going to establish a University Fund for his daughter Mary, who has just been born. He plans to make the first deposit of $20,000 today and then

annual deposits of $5,000 will be made until Mary’s 17th birthday. Given the long term nature of the investment, John anticipates a 5% pa return. The money is

transferred to an account for Mary on her 17th birthday and she will then withdraw the money in equal annual amounts for 5 years starting on her 18

birthday. Mary will only be able to earn 3% pa on her money.

(i) How much money will be available on Mary’s 17th birthday?

(ii) How much will Mary be able to spend each year?

(Your answers should be accurate to the nearest dollar)

0,000 today and then

5% pa return. The money is

starting on her 18th

TEXTBOX

Question 3

There are two options to purchase a car: 5-year loan vs. lease the car.

The price of the car is $50,000. If you purchase the car, you are going to pay it off in monthly payments over the next 5 years at an annual percentage rate

of 6.99 per cent. You expect to sell the car for $28000 in five years. If you lease the car, you have to pay 20% of the price of the car today and $550 per

month for the next five years. Should you lease or buy the car? What break-even resale price in five years would make you indifferent between two

options?

(Your answers should be accurate to the nearest dollar)

TEXTBOX

at an annual percentage rate

the car today and $550 per

erent between two

Question 4

Ella has just retired and has received a lump sum pay-out of $1,800,000. She invests part of this pay-out in a perpetual

investment which earns 4% per annum and provides a perpetual income to her of $30,000 per year (assuming end-ofyear withdrawals). She puts the rest of the pay out in another investment in the form of a growing perpetuity (growth

rate of 2% pa) which earns 4% pa. She wants to make annual withdrawals (starting in one year) from this growing

perpetuity to fund some holidays.

Required

(i) Calculate how much Ella has invested in the perpetual investment.

(ii) Show how much extra Ella can expect to spend each year (assuming end-of-year withdrawals), over and above the

$30,000 from the perpetual investment, from the growing perpetuity. Note: ignore tax in your calculations.

(Accurate to the nearest dollar)

TEXTBOX

TEXTBOX

Question 5

Your supervisor has asked you to do the following calculations:

(a) A bank bill with 120 days to maturity is issued with a yield of 0.05% pa. Face value is $1,000,000. Calculate the issue price of the

bill.

(b) The bill in part (a) is sold after 10 days at a yield of 0.15% pa. Calculate the selling price.

(Accurate to the nearest dollar)

TEXTBOX

e of the

Question 6

Emma buys a bond with a face value of $100, a time to maturity of 5 years, a coupon of 2% pa with semi-annual payments

and a yield of 2.4% pa. Three year’s later (immediately after the sixth coupon has been paid), the Reserve Bank of Australia

unexpectedly decreases the cash rate. The yield on Julie’s bond decreases to 1.2% pa and she decides to sell.

Required

Calculate the buying and selling prices. Discuss why the price has changed.

(In dollars and cents accurate to the nearest cent)

TEXTBOX

Question 7

Polycorp plans to pay a dividend of $6 in one year’s time. Dividends are then expected to increase by a $1 a year for 5 years. After

that they are expected to grow at 2% pa forever. Shareholders required return on equity is 11% pa. What is the estimated value of

Polycorp shares?

(In dollars and cents accurate to the nearest cent)

TEXTBOX

of

Question 8

The required rate of return on the shares in the companies identified below is 9% pa. Calculate the current share price (ex-dividend) in each case.

(a) The current earnings per share of Alpha Ltd are $10.00. Earnings are expected to remain constant for the next three years. For the next 3 years, Alpha

anticipates having to put half of its earnings back into the business to maintain the level of earnings. After this it is expected that all earnings will grow by

and all earnings will be able to be paid out as dividends.

(b) Gamma Ltd is planning to reinvest earnings and not pay dividends until year 4, when a dividend of $6 is expected (D4 = $6). Dividends are expected to

constant after that.

(Accurate to the nearest cent)

TEXTBOX

dend) in each case.

or the next 3 years, Alpha

ed that all earnings will grow by 3% pa

= $6). Dividends are expected to remain

Question 9

Your client wishes to insure their Lamborghini. PMA Insurance has quoted an annual premium to insure the car of $50,000. PMA offers two other paymen

account can be paid in full by making 12 equal end-of-the month payments of $4,500. Alternatively two semi-annual payments (the first immediately) of

made. The appropriate interest rate to make the comparison is 6% pa compounded monthly. Which of the three options should you ch

(In dollars and cents accurate to the nearest cent)

offers two other payment methods. The

ments (the first immediately) of $26,000 can be

should you choose for your client?

TEXTBOX

Question 10

You are offered the choice of the following two income streams; $300,000 every 3 years in perpetuity with the first receipt in 3 years.

$26,000 every month for 10 years with the first receipt immediately.

The annual interest rate is 3.6% pa.

Which income stream do you choose?

(Answer to the nearest dollar)

TEXTBOX