Assignment
The purpose of this assignment is to explore the capabilities of Excel spreadsheets and its applications for finance. This assignment must be done individually, is worth 20% of the total assessment for this subject and is not redeemable.
You are required to submit one excel file containing two excel spreadsheets, one for each question.
The link for the Turnitin Assignment tool has been created under the Assignment page entitled “Assignment- Turnitin Submission” on the MyUni site. You can upload your assignment directly by following the prompts.
A late assignment submission will be penalized by a 1 mark reduction (or 5% of the possible maximum mark for the assignment) for each day that it is late.
There are 2 questions for this assignment. The first requires you to produce data for an amortized home loan whilst the second question requires you to determine the impact of changes in interest rate on bond value.
A separate spreadsheet is required for each question.
Question 1: (50% of marks)
You are required to produce an amortization table for a home loan and a diagram demonstrating the link between loan repayments and principal outstanding. Please see slide 34 from Topic 2 (or p146 from textbook) for an example of the layout of the table.
The home loan is for $250,000 and is to be amortized over a time period of 15 years requiring annual payments. All calculations should be executed in excel. From your table produce a diagram that demonstrates the relationship between the outstanding principal and the number of years into the loan.
The interest rate to be used is 3% plus the last digit of your student number. For example if your student number ends in a ‘0’ then the interest rate is 3%, but if your student number ends in a ‘9’ then the interest rate is 12%. Assume that interest rates do not change over the life of the loan.
Question 2: (50% of marks)
You are required to produce a table showing bond values and the impact of changes in interest rate over the life of a bond and a diagram demonstrating the link between the changes in values (due to changes in interest rate) and time to maturity.
The bond has a face value of $1,000, pays a coupon rate of 7% p.a paid annually and it is issued with 10 years to maturity. All calculations should be executed in excel.
Your table should show the following:
- The value of the bond, year by year (from date of issue until its maturity), assuming all other things remain the same.
- The value of the bond, year by year, from date of issue until its maturity, assuming that market interest rate increases by 1.5% (hence yield to maturity increases by 1.5%), all other things remain the same.
- The potential % change in value, year by year, from the date of issue until its maturity.
The % change in value demonstrates the impact of the increase in interest rate on the bond value (or interest rate risk), for each year of maturity.
- From your table produce a diagram that demonstrates the relationship between % change in value and time to maturity.
The initial market interest rate (yield to maturity) to be used is 5% plus the last digit of your student number. For example if your student number ends in a ‘0’ then the yield to maturity is 5%, but if your student number ends in a ‘9’ then the yield to maturity is 14%.
Getting started: Instructions for Excel.
Excel spreadsheets is very useful application in which to organize and manipulate data and to organize your own problem solving skills. There is plenty of help offered on-line. Just type in “excel spreadsheets explained” or something like it and you will find many references. Your text book is also a useful learning tool. I think that the best way of learning excel is by ‘doing’. So if you are totally unfamiliar with excel then read some basic introductory information about and then attempt some problem solving below. All of the calculation problems we have encountered in lectures and tutorials for Topics 2 & 3 can be solved by excel, so there is plenty of practice. There are references in the lectures slides to some excel functions in these topics that you may find useful as well. In the following discussion I provide a simple ‘hands-on’ approach to excel. Once you start using excel you may find it hard to stop because you will be on a constant learning curve.
Introduction
Calculations in excel can be executed by formula determined by yourself or using a prescribed excel function that execute the formula for you. For example, for the problem of determining the future value for a series of annual payments of $100 for 20 years given an interest rate of 7% can be executed by the following 3 approaches.
Firstly, knowing the formula for an annuity, , I can produce the following formula in excel <=100*(((1.07)^20-1)/0.07)>. What is inside the brackets <> is what you type in excel. I need to begin the instruction with an “=” because it signifies that you wish to perform a calculation in excel rather than just keying-in data. I have to use brackets “()” to separate each function so that the correct mathematical operation is performed. It is a good idea to check your answers in excel using your calculator until you become familiar with the process. It is easy to overuse the brackets “()” but it doesn’t necessarily mean that it is incorrect- excel will tell you if its use is not logical. Note the following:
- Multiplication in excel is “*”; hence <100*> excel reads this as 100 x. Whenever you wish to multiply two numbers together use the “*” key
- Addition, subtraction and division in excel are as you expect “+, -, /”. Excel performs multiplication and division operations before addition and subtraction. Use “()” to arrange the correct order of calculations for your formula.
- <(1.07)^20> excel reads this as (1.07)20
- <(1.07)^20-1> excel reads this as (1.07)20 -1 not as (1.07)19
The answer is $4,099.55. Check this in your calculator.
Secondly, I can designate 3 cells in excel to contain the input data for my formula- one cell for each input C, r, t and then use another cell to perform the calculation by reference to these cells. For example
Cell A1 <C>; cell B1 <100>
Cell A2 <r>; cell B2 <.07>
Cell A3 <t>; cell B3 <20>
Cell C4 <Answer>; cell D4 <=B1*(((1+B2)^B3-1)/B2)>.
I could have performed the calculation in any other cell but I chose D4. Each cell is identified by a column (A,B,C,D, etc) and row (1,2,3,4 etc). For this problem excel spreadsheet will look like this:
| A | B | C | D | |
| 1 | C | 100 | ||
| 2 | r | .07 | ||
| 3 | t | 20 | ||
| 4 | Answer | =B1*(((1+B2)^B3-1)/B2) |
Thirdly, after keying-in the data, I can use the prescribed function in excel to calculate the future value of an annuity. You will find the list of functions in the tool bar menu of excel at the top of screen, click on Formulas/ financials and then scroll down to the list to find the function you want. Since we want to perform a future value calculation we should select “FV”. Each function requires certain data to perform the calculation. The excel spreadsheet will look like this
| A | B | C | D | |
| 1 | C | 100 | ||
| 2 | r | .07 | ||
| 3 | t | 20 | ||
| 4 | Answer | =FV(B2,B3,B1, ,0) |
Other excel functions that are useful include:
- PV: present value
- RATE: discount rate
- NPER: number of periods
- PMT: annuity payments
For each of these functions you are required to indicate whether the annuity is for an ordinary annuity or annuity due. This is provided by the last data input that is required for each function. By default an ordinary annuity is = “0” and an annuity due = “1”.
Producing Diagrams in excel
Once you have produced data outputs in excel, you can arrange it in diagram (table or other form). Highlight the data you wish to produce in a diagram and then click on Insert in the menu toolbar, then click on one of the suggested displays. I suggest you click on Recommended Charts as it will provide you with a number of options. It is important that you arrange your data in a particular order to get the display that you want – this may take a bit of practice.
RD & GG
