xuebaunion@vip.163.com

3551 Trousdale Rkwy, University Park, Los Angeles, CA

留学生论文指导和课程辅导

无忧GPA：https://www.essaygpa.com

工作时间：全年无休-早上8点到凌晨3点

微信客服：xiaoxionga100

微信客服：ITCS521

程序代写案例-MATH1515

时间：2021-03-20

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.

学霸联盟

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.

学霸联盟