10 Math/Finance Problems using Excel

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