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. 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. There should be one row in the table for each month. Calculate the NPV of the rental income streams (after income tax) from flat 1 and from flat 2 separately. (b) Compute the NPV of the sale of each of the two flats, after capital gains taxes. (c) Compute the NPV of total cash flow for each of the two flats separately, and the NPV of the investment project as a whole. Be careful about how you apply rent increases on the two flats: one has a percentage increase and the other has a total annual increase that is a fixed amount of money. Similarly, be careful about the increases in maintenance payments: one has a percentage (of the previous maintenance) increase, whereas the other increases by a percentage of the amount that the rent increases. 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'. (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 initial investment in each flat, the NPV of the rental income streams (net of maintenance and taxes) and the NPV of the sale of each flat (net of capital gains tax). And, for each value of the interest rate, compute the total NPV for each flat, and the total NPV of the project. Hint: there are several different ways you could do this. Excel functions like =SUMPRODUCT or =NPV might be useful, but be careful, and be sure to check that your table is consistent with the NPV calculation you did in task 1. (b) Make a chart of the NPV for flats 1 and 2, and of the grand total for the whole project, 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 for each flat separately and for the combined project. Determine also the cross-over rate for the two flats. 3: Compare the investments in the two flats. Suppose the investor decides to buy only one of the two flats, not both. Which one should the investor choose? Your analysis should be based on considerations such as - the size of the investment and the return for each flat; - the present value for each flat, whether these are positive or negative at the given Interest rate, and how the present values depends on interest rate; - the yields from each of the two flats and the cross-over rate; - the payback period; - and any other considerations you think are relevant. You should explain carefully how you came to your conclusion on which flat to chose. Any relevant calculations should be included in the 'Compare the investments in the two flats' worksheet. 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. 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. There should be one row in the table for each month. Calculate the NPV of the rental income streams (after income tax) from flat 1 and from flat 2 separately. (b) Compute the NPV of the sale of each of the two flats, after capital gains taxes. (c) Compute the NPV of total cash flow for each of the two flats separately, and the NPV of the investment project as a whole. Be careful about how you apply rent increases on the two flats: one has a percentage increase and the other has a total annual increase that is a fixed amount of money. Similarly, be careful about the increases in maintenance payments: one has a percentage (of the previous maintenance) increase, whereas the other increases by a percentage of the amount that the rent increases. 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'. (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 initial investment in each flat, the NPV of the rental income streams (net of maintenance and taxes) and the NPV of the sale of each flat (net of capital gains tax). And, for each value of the interest rate, compute the total NPV for each flat, and the total NPV of the project. Hint: there are several different ways you could do this. Excel functions like =SUMPRODUCT or =NPV might be useful, but be careful, and be sure to check that your table is consistent with the NPV calculation you did in task 1. (b) Make a chart of the NPV for flats 1 and 2, and of the grand total for the whole project, 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 for each flat separately and for the combined project. Determine also the cross-over rate for the two flats. 3: Compare the investments in the two flats. Suppose the investor decides to buy only one of the two flats, not both. Which one should the investor choose? Your analysis should be based on considerations such as - the size of the investment and the return for each flat; - the present value for each flat, whether these are positive or negative at the given Interest rate, and how the present values depends on interest rate; - the yields from each of the two flats and the cross-over rate; - the payback period; - and any other considerations you think are relevant. You should explain carefully how you came to your conclusion on which flat to chose. Any relevant calculations should be included in the 'Compare the investments in the two flats' worksheet