Financial math with excel, assignment help

Anonymous
timer Asked: Oct 7th, 2016
account_balance_wallet $25

Question Description

Hi

Need to be done in 24 hours. Leave best price

Unformatted Attachment Preview

-Use a computing tool (e.g. Excel/Google Spreadsheets or Python) for financial calculations. -Use a financial information system (e.g. Reuters Eikon, or the Internet) for obtaining market data and information as well as harnessing well-documented API/library/models to make inferencing more expedient - You are required to present your answer in excel spreadsheet or python with working or steps. Do not provide answer without working. Question 1 The concept of the time value of money highlights the dynamic growth nature of money in a financial environment that aggressively competes for funds. Answer the following questions in relation to the concept: (a) Describe the nature of the time value of money and how it arises in reality. (10 marks) (b) Do the following exercises: 1) What is the future value after 120 days of $43 invested at 7.5%? 2) You invest $1,000 and receive back a total of $1,360.86 at end of 7 years. There was no interest payments during the 7 years. What annual yield does this represent? 3)You receive 12% paid annually. What are the equivalent quarterly rate and monthly rate? Be sure to express your answers clearly in your own words, based on your own understanding, with Python as an aid in your calculations and reasoning. (10 marks) ________________________________________________________________________________ Question 2 Statistical concepts are important whenever we are required to reason with a large amount of data. Answer the following questions in relation to this idea: (a) Review three (3) fundamental concepts in statistics in relation to financial data in a clear fashion that can be understood by your friend without a face-to-face explanation. (10 marks) (b) Do the following exercises: 1) What are the median, mode, arithmetic mean and geometric mean of the following numbers? 13, 7, 13, 4, 10, 15, 4, 12 2) What is the estimated standard deviation of the price of a cup of coffee in Manchester? You have the following sample data from six cafes: 95 pence 250 pence 160 pence 124 pence 180 pence 175 pence 3) Given a set of monthly returns with mean 1.2% and annual standard deviation 8%, assuming a normal probability distribution, what is the probability of a return worse than -5% over 1 month (i.e. one twelfth of a year)? Be sure to express your answers clearly in your own words, based on your own understanding, with Excel as an aid in your calculations and reasoning. (10 marks) _____________________________________________________________________________________ Question 3 The equity market is one of several major financial markets that are found in the world. Answer the following questions on this important market: (a) Outline the significance of the word "equity" in the present usage. (10 marks) (b) Do the following exercises: 1) Given the following data, what are the ROE, EPS, P/E ratio, P/B ratio and earnings yield? Total earnings during the year : $40 Million Book value: $250 Million No. of ordinary shares in issue: 15 Million Current share price: $21.93 2) Given the following data, what is the share value according to the Gordon model, and what is the percentage fall in the share value if the dividend growth rate falls to 7%? Current dividend per share: $1.03 Expected annual growth rate of the dividend: 8% Investor’s required rate of return: 10% 3) Given the following data, what is the share’s yield according to the T-model and the Gordon model? Total earnings during a year: $35,450,000 Book value at start of the year: $250 Million No. of ordinary shares in issue: 15 Million Share price at start of the year: $19.06 Share price at end of the year: $21.93 Dividend per share during the year: $1.03 Growth rate of book value: 8% Expected growth rate of dividend: 8% Be sure to express your answers clearly in your own words, based on your own understanding, with Python as an aid in your calculations and reasoning. (10 marks) ____________________________________________________________________________________ Question 4 A company, StarMoon Pte. Ltd., has recently paid dividends of $2 per share. According to stock analysts, the dividend is expected to grow at a constant rate of 4% for the next 5 years. After that, the dividend is expected to grow at the lower rate of 2% annually. Assume that the risk-free rate is 1.5%, the beta of the stock is 1.5 and the expected market return is 3%. (a) Calculate the value of StarMoon’s stock per share. (10 marks) (b) If dividend is expected to be held constant after year 5 instead, re-calculate the value of StarMoon’s stock per share. (10 marks) Assume that it is currently 1 Jan and dividends are paid out at the end of each year in your answer. Show your workings clearly with calculations performed on Excel. _________________________________________________________________________________ Question 5 Joachim opened a savings account with a bank for 5 years. He deposited $5,000 and $3,000 respectively in the first two years. In the third year, he bought a new house and could not save any money. In the 4th year, he withdrew $1,000. In the final year, he neither saved nor withdrew from his account. If the annual prevailing interest rates for the 5 years turn out to be as follows: Year 1st 2nd 3rd 4th 5th Interest Rate 3.5% 3.7% 3.9% 4.1% 4.3% Compute the amount of money that is found in his bank account at the end of 5 years, assuming that deposits are made at the BEGINNING of the year. Show your workings clearly with calculations performed with Python. (20 marks) ...
Purchase answer to see full attachment

Tutor Answer

strongboss5
School: Carnegie Mellon University

Dear student,Please find enclosed a doc file containing the step by step solution to the different questions, together with an xls file with the different calculations. In the xls file, each question is presented as an independent worksheet so that you can find them easily

Question 1
a) Time value of money represents the actual value we give to money. It appears as a necessary
concept to understand why even while a $100 bill has the same monetary value both now and in
the future. This can easily be understood by considering that if we have the money available right
now, we would be able of investing it, such that in the future we would have both the available
money (the $100 bill) and the interests that have matured from it. This concept is further analysed
through the examples in section b.
b)
1. Since no further information is provided, we will assume a simple interest investment to evaluate
the future value. According to this, the relationship between the current and future value of money
would be given by the formula:
𝑑𝑎𝑦𝑠
𝐹𝑢𝑡𝑢𝑟𝑒 𝑣𝑎𝑙𝑢𝑒 = 𝐶𝑢𝑟𝑟𝑒𝑛𝑡 𝑣𝑎𝑙𝑢𝑒 (1 + 𝑎𝑛𝑛𝑢𝑎𝑙 𝑖𝑛𝑡𝑒𝑟𝑒𝑠𝑡 𝑟𝑎𝑡𝑒 ∗
)
365
Taking this into account, we find:
Current value = $120
Interest rate = 7.5% annually
Time = 120 days
𝐹𝑢𝑡𝑢𝑟𝑒 𝑣𝑎𝑙𝑢𝑒 = 120 (1 + 0.075 ∗

120
) = $122.96
365

2. Since we are told that there are no interest payments in the 7 year period, we should assume a
simple interest regime. Taking this into account, we would calculate the annual interest rate by
applying the formula
𝐹𝑢𝑡𝑢𝑟𝑒 𝑣𝑎𝑙𝑢𝑒
1
𝑟=(
− 1) ∗
𝐶𝑢𝑟𝑟𝑒𝑛𝑡 𝑣𝑎𝑙𝑢𝑒
𝑛𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑦𝑒𝑎𝑟𝑠
Taking this into account, we find:
1360.86
1
𝑟=(
− 1) ∗ = 5.16%
1000
7
3. Considering an annual interest rate of 12%, we can easily calculate the corresponding quarterly and
monthly interest rates by taking into account that one year has 3 quarters and 12 months. Thus,
the corresponding quarterly and monthly interest rates would be:
𝑞𝑢𝑎𝑟𝑡𝑒𝑟𝑙𝑦 𝑖𝑛𝑡𝑒𝑟𝑒𝑠𝑡 𝑟𝑎𝑡𝑒 =
𝑚𝑜𝑛𝑡ℎ𝑙𝑦 𝑖𝑛𝑡𝑒𝑟𝑒𝑠𝑡 𝑟𝑎𝑡𝑒 =

𝑎𝑛𝑛𝑢𝑎𝑙 𝑖𝑛𝑡𝑒𝑟𝑒𝑠𝑡 𝑟𝑎𝑡𝑒 12
=
= 3%
4
4

𝑎𝑛𝑛𝑢𝑎𝑙 𝑖𝑛𝑡𝑒𝑟𝑒𝑠𝑡 𝑟𝑎𝑡𝑒 12
=
= 1%
12
12

If we calculate the effective annual rates assuming it was compounded quarterly or monthly, we
would find:
𝐴𝑛𝑛𝑢𝑎𝑙 𝑟𝑎𝑡𝑒 (𝑐𝑜𝑚𝑝𝑜𝑢𝑛𝑑𝑒𝑑 𝑞𝑢𝑎𝑟𝑡𝑒𝑟𝑙𝑦) = (1 + 0.03)4 = 12.55%
𝐴𝑛𝑛𝑢𝑎𝑙 𝑟𝑎𝑡𝑒 (𝑐𝑜𝑚𝑝𝑜𝑢𝑛𝑑𝑒𝑑 𝑚𝑜𝑛𝑡ℎ𝑙𝑦) = (1 + 0.01)12 = 12.68%

Question 2
a) Some statistical concepts that are commonly used in finance include:
The mode, which represents the financial value that is repeated most frequently
The arithmetic mean, which is calculated by adding up all the financial va...

flag Report DMCA
Review

Anonymous
Tutor went the extra mile to help me with this essay. Citations were a bit shaky but I appreciated how well he handled APA styles and how ok he was to change them even though I didnt specify. Got a B+ which is believable and acceptable.

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors