We are considering switching from a project A to a project B. The decision to give up the project A cashflows in return for receiving the project B cashflows produces the sequence of before tax incremental cashflows shown in the second column of the table below. Assume the project's sponsor (owner) has enough other taxable income to absorb and utilise any tax deductions from this decision. Time cashflow before tax tax instalment tax instalment 2 cashflow after tax $414,720.00 S2,476,800.00 -$5,912,000.00 $0.00 0.00 -$414,720.00 $2,580,480.00 -$619,200.00$103,680.006,427,520.00 $7,050,000.00$1,478,000.00-$619,200.00$7,908,800.00 $4,200,000.00-$1,762,500.00$1,478,000.00-$4,484,500.00 $1,000,000.00$1,050,000.00-$1,762,500.00$287,500.00 $0.00 $103,680.00 4 0.00 $0.00 S0.00 -$250,000.00-$250,000.00 We can think of this decision to switch from A to B as a third project, project C and the cashflows from the project are fully taxable and involve no "capital" components. Suppose the cashflows in the 2nd column are in the cell range B2:B12 of a spreadsheet you have designed to analyse this project. The other cashflows are in the adjacent columns C, D & E respectively- (a) The before tax cashflow of project C has 2 IRRs. Type the cashflow before tax data into a spreadsheet. Using excel check if these IRRs are 1 1.1% and 25.0%. State whether these are the correct IRRs for the project, expressed as a percentage to 1 decimal place. How can you use excel's IRR function to find each of these solutions? Explain how you would do it or write down the excel code you would use (b) Suppose that the required return / hurdle rate for this project is F5% per annum. Compute the NPV of the pre tax cashflow for project C using this rate of interest. Show the excel code using the NPV function to compute this number. The pre tax cashflows are in column B, cell range B3:B10. 13 marksl (c) The payback period for an investment project is one measure of project viability. How suitable is it for this particular project? Give reasons why it may or may not be suitable [3 marks | (d) The accounting rate of return is another measure of project viability. Assuming that the time 0 pre tax cashflow is the initial investment the subsequent pre tax cashflows are profit / loss from the investment at the end of the life of the project we get our initial capital back in addition to the project's final cashflow of $1m, compute the accounting rate of return for this project based on the pre tax cashflows comment on the suitability of the accounting rate of return as a measure of project viability (e) Suppose that you have to pay tax on the cashflows (these are income cashflows and a negative income is tax deductible expense whereas a positive income is taxable revenue). The tax rate is 50% of the amount of the cashflow and the tax is payable in 2 equal size instalments, the first 1-year later than the cashflow is received, the 2nd 2-years later. These tax payments are shown in the table above, as is the total after tax cashflow, in the last column Show that / explain why the npv of the after tax cashflow can be computed from the npv of the before tax cashflow via the equation (NPVAFTER TAX . )-(NPIBEFORE TAX):{1-0.25xa(2i)} where (2)1-0+0 Using an interest rate of NPV of the before tax cashflows and the formula 5% compute the npv of the after tax cashflows from project C using the . NPVAFTERTAX)-NPVRTAx)1-025xa(2.) BEFORE TAX (f) The NPV of the after tax cashflow is the product of 2 factors: NPVBEPORE TAx) and 1-025xa(2.) An interest rate that makes either of these factors equal to zero is an internal rate of return for the after tax cashflows. We want to find the rate i that makes the factor 1-0.25xa(2.i))-0. Use the excel "rate" function to compute this rate. Show the values of the inputs to the rate function Check if the rate is approximately equal to-36% How many different IRRs are there for the after tax cashflows for this project? [4 Marksl (g) using a financing rate of 4% and a reinvestment rate of 6% compute the Modified Internal Rate of Return of the before tax cashflows. The project's before tax cashflows can be split into the positive and the negative flows as follows Time positive flows negative flows S414,720 S0 S0$5,912,000 S0 S0$4,200,000 S0 S0 $2,476,800 $7,050,000 $1,000,000 4 We are considering switching from a project A to a project B. The decision to give up the project A cashflows in return for receiving the project B cashflows produces the sequence of before tax incremental cashflows shown in the second column of the table below. Assume the project's sponsor (owner) has enough other taxable income to absorb and utilise any tax deductions from this decision. Time cashflow before tax tax instalment tax instalment 2 cashflow after tax $414,720.00 S2,476,800.00 -$5,912,000.00 $0.00 0.00 -$414,720.00 $2,580,480.00 -$619,200.00$103,680.006,427,520.00 $7,050,000.00$1,478,000.00-$619,200.00$7,908,800.00 $4,200,000.00-$1,762,500.00$1,478,000.00-$4,484,500.00 $1,000,000.00$1,050,000.00-$1,762,500.00$287,500.00 $0.00 $103,680.00 4 0.00 $0.00 S0.00 -$250,000.00-$250,000.00 We can think of this decision to switch from A to B as a third project, project C and the cashflows from the project are fully taxable and involve no "capital" components. Suppose the cashflows in the 2nd column are in the cell range B2:B12 of a spreadsheet you have designed to analyse this project. The other cashflows are in the adjacent columns C, D & E respectively- (a) The before tax cashflow of project C has 2 IRRs. Type the cashflow before tax data into a spreadsheet. Using excel check if these IRRs are 1 1.1% and 25.0%. State whether these are the correct IRRs for the project, expressed as a percentage to 1 decimal place. How can you use excel's IRR function to find each of these solutions? Explain how you would do it or write down the excel code you would use (b) Suppose that the required return / hurdle rate for this project is F5% per annum. Compute the NPV of the pre tax cashflow for project C using this rate of interest. Show the excel code using the NPV function to compute this number. The pre tax cashflows are in column B, cell range B3:B10. 13 marksl (c) The payback period for an investment project is one measure of project viability. How suitable is it for this particular project? Give reasons why it may or may not be suitable [3 marks | (d) The accounting rate of return is another measure of project viability. Assuming that the time 0 pre tax cashflow is the initial investment the subsequent pre tax cashflows are profit / loss from the investment at the end of the life of the project we get our initial capital back in addition to the project's final cashflow of $1m, compute the accounting rate of return for this project based on the pre tax cashflows comment on the suitability of the accounting rate of return as a measure of project viability (e) Suppose that you have to pay tax on the cashflows (these are income cashflows and a negative income is tax deductible expense whereas a positive income is taxable revenue). The tax rate is 50% of the amount of the cashflow and the tax is payable in 2 equal size instalments, the first 1-year later than the cashflow is received, the 2nd 2-years later. These tax payments are shown in the table above, as is the total after tax cashflow, in the last column Show that / explain why the npv of the after tax cashflow can be computed from the npv of the before tax cashflow via the equation (NPVAFTER TAX . )-(NPIBEFORE TAX):{1-0.25xa(2i)} where (2)1-0+0 Using an interest rate of NPV of the before tax cashflows and the formula 5% compute the npv of the after tax cashflows from project C using the . NPVAFTERTAX)-NPVRTAx)1-025xa(2.) BEFORE TAX (f) The NPV of the after tax cashflow is the product of 2 factors: NPVBEPORE TAx) and 1-025xa(2.) An interest rate that makes either of these factors equal to zero is an internal rate of return for the after tax cashflows. We want to find the rate i that makes the factor 1-0.25xa(2.i))-0. Use the excel "rate" function to compute this rate. Show the values of the inputs to the rate function Check if the rate is approximately equal to-36% How many different IRRs are there for the after tax cashflows for this project? [4 Marksl (g) using a financing rate of 4% and a reinvestment rate of 6% compute the Modified Internal Rate of Return of the before tax cashflows. The project's before tax cashflows can be split into the positive and the negative flows as follows Time positive flows negative flows S414,720 S0 S0$5,912,000 S0 S0$4,200,000 S0 S0 $2,476,800 $7,050,000 $1,000,000 4