MATH1515: Interest rates and cashflowmodelling
Assessed coursework 2 (Excel-based)
Hand in your written answers by 2pm onWednesday 24th March 2021 on Turnitin (on
Minerva) – not on Gradescope.
This piece of coursework counts for 15% of your module mark.
Late hand-in until 5pm on Wednesday 24th March is possible, but a 50% mark deduction will
be applied. Any requests for an extension of the deadline should be made to MATH1515@leeds.ac.uk
in advance; requests for exemptions should be made to the Maths Taught Student Office.
Instructions for preparing your submission.
• You must use Turnitin (on Minerva) and submit two files separately:
1. an Excel spreadsheet file containing the solutions to all problems posed below;
2. a PDF file containing a succinct report of what you did in the spreadsheet and
the final results obtained in each question. This report should be typed and a
maximum of three pages. It should also include a completed academic integrity
form.
• You must name your spreadsheet and your .pdf file as follows: Surname-Firstname-StudentID.xls
(or .xlsx) and Surname-Firstname-StudentID-report.pdf, where your ‘Surname’ should
be consistent with your university records.
• Turnitin allows you multiple submissions, and you are advised to upload your work-in-progress
as you progress. You can continue to amend your submission until the deadline. Doing
this will reduce the impact of last-minute wifi and computer problems.
• As this work may contribute towards the award of actuarial exemptions, it is important
you prepare your own spreadsheet and report independently.
Your work will be assessed on the:
• mathematical and numerical accuracy of your spreadsheet: are the formulae and
numbers correct?
• flexibility of your spreadsheet: are cell references used so that changing a value in
one place automatically updates the whole spreadsheet? Have you avoided hard-coding
the numbers as much as possible?
• presentation of your spreadsheet: is the material laid out clearly and logically, with
cells, rows and columns labelled so that the reader can easily find and understand
what the entries are? Are numbers formatted to an appropriate number of decimal
places?
• presentation of your report: is the report written in scientific English language? Is the
text and are the formulae formatted correctly? Are the figures clearly captioned?
• reasoning and critical analysis of your spreadsheet and report: are all questions
and tasks (listed below) clearly answered? Is the logic behind the construction of your
spreadsheet clearly and concisely explained? Have you explained the rationale for
choosing the mathematical formulae that you have used?
Problem statement: please read this very carefully.
• Open the spreadsheet provided and you will find three worksheets, labelled ‘Input
data’, ‘Cash flow and NPV’, and ‘NPV vs interest rate’.
• Input your student ID in the ‘Input data’ worksheet in cell B1. The spreadsheet will
then generate all the relevant input data for the tasks below, and will automoatically
copy them in to the ‘Cash flow and NPV’ worksheet.
• In the text below the student ID are values labelled ‘Interest rate’, ‘Initial capital’, . . . ,
‘Increase in value’ – these quantities are referred to by name in the tasks, and are
different for every student. Please notify me immediately if any of your numbers
are negative.
The worksheets contain minimal templates that are intended to help with the tasks below,
but it is not compulsory to use them.
An individual invests Initial Capital (pounds) to purchase two flats that are going to be
rented to provide a monthly rental income to the investor. The first flat costs Initial value
flat 1 and the second flat costs Initial value flat 2. Rent is paid to the investor at the start of
each month for Number of years. The annual effective interest rate is Interest rate.
For the first flat we have:
• The initial monthly rental income for the first 12 months is Rent 1. Each subsequent
year, the rent increases at the start of the year at a rate of Increase 1% p.a. effective,
and is then constant for the next 12 months;
• Maintenance costs are also paid by the investor from the rental income at the start of
each month. The initial amount is Maintenance 1 per month, and is fixed for 12 months.
Like the rent, it increases at the start of each subsequent year at a rate Increase in
maintenance 1% p.a. effective.
For the second flat we have:
• The initial monthly rental income for the first 12 months is Rent 2. Each subsequent
year, the rent increases at the start of the year by a fixed amount. The total annual
increase is Increase 2, which is divided equally between the monthly rental amounts;
• Maintenance costs are paid at the start of each month. The initial amount is Maintenance 2
per month, and is fixed for 12 months. Like the rent, it increases at the start of each
subsequent year. The amount by which it increases is fixed as Increase in maintenance 2%
of the increase in rent.
Taxes on both properties are paid every month at a rate Taxes% on the monthly rental
income net of maintenance costs (that is, monthly rental income minus the maintenance
costs). After Number of years both properties are sold. Their value has increased at an
annual rate of Increase in value% p.a. and capital gains taxes (CGT) are paid at a rate of
Taxes% on the capital gains.
Task 1: Compute the Net Present Value (NPV) of the cash flow at the given Interest rate.
(a) In the worksheet ‘Cash flow and NPV’, create a table in which, for each flat, the following
are displayed: monthly rental income, monthly maintenance costs, monthly tax
payment, net monthly income, present value at time zero of net monthly income, for
months starting from time zero to the end of the cash flow. For this task, the monthly
payments of rent, maintenance and tax are all rounded to the nearest penny. Calculate
the NPV of the income streams from flat 1 and from flat 2 separately. You can use the
template if you wish.
(b) Compute the net present value of the sale of the two flats together, after capital gains
taxes.
(c) Compute the net present value of cash flow for the investment project as a whole.
Task 2: Compute the approximate Internal Rate of Return (IRR) for the investment
project. The investor wants to assess the sensitivity of the investment project to shifts in
the interest rate. Please put these calculations in a separate worksheet labelled ‘NPV vs
interest rate’. You can use the template if you wish.
(a) Create a table in which you consider interest rate values ranging from 0.5% p.a. effective
to an appropriate level, with increments of 0.5%. Your table should include the NPV of
the rental income stream (net of maintenance and taxes) and the NPV of the buying
and selling of the flats (net of capital gains tax). And, for each value of the interest rate
compute the total net present value of the project.
Hint: there are several different ways you could do this, but one way is to create a row
whose entries are the various interest rates; then create a column whose entries are
the total of the two net monthly incomes from the first worksheet; fill in the rest by
discounting each amount and adding up as appropriate, including the sale of the flats.
(b) Make a chart of the NPV of the rental income, the NPV of the buying and selling, and
the total NPV as a function of interest rate over an appropriate range. Include this
chart in your report.
(c) Determine from this plot the approximate internal rate of return of this project.
Task 3: rental income to achieve 5% IRR.
The investor has a target yield in mind of 5% p.a. effective. Find, by a method of your choice,
the minimum value of the initial rent for the first flat that would guarantee a 5% p.a. internal
rate of return (effective) for the whole project, without changing any of the other parameters
(rate of rental increases, the second flat rental value, the maintenance values, etc.).
You should document your method carefully in your report, and justify your choice of method.
The calculations should be clear from your spreadsheet and report. You are free to create an
additional worksheet if that would be helpful, and you should not allow this task to make it
harder to understand the worksheets for the previous two tasks.
Please remember to submit a completed Academic Integrity formwith your report.


















































































































学霸联盟