Please help to solve problem (a) - (d):

Supplementary slide #6 & #46 to help answer the question parts. We are basically redoing the problems on this slide

So, here's what I want you to do: Re-do the problem in Chapter 12 PowerPoint slides 6 and 46-, EXCEPT: . the purchase price and the NOI for the first year should be both double what's on slide 6 (in other words, $2,000,000 and $200,000, respectively), AND the "conversion percentage" (that you see on slide 46) should be not 65%, but INSTEAD 70% / 0% / 100% (see last table on the bottom of this page), AND the loan term should be 30 years (like on slide 46), the annual loan interest rate should be 8.5% (like on slide 46), the loan should require monthly payments (like on the slides), and the LTV on the loan should still be 70 percent of your new purchase price. If you can do the math in Excel with correct referencing to cells with intermediate results. This will allow you to avoid rounding errors. Otherwise, increase decimal places - the more the better! Say, 6 or even higher. (a) First, calculate the required annual loan payment: $ Do not use the "$" sign. Round to whole dollar. (b) Next, for the conventional fixed-rate loan case, calculate the investor's BTCFs for years 0 through 5. Also, use these annual cash flows to calculate the investor's BTIRR. In addition, calculate the lender's IRR using lender's monthly cash flows (like we did in class). Round the BTCFs to whole dollar and the two IRRs to 2 decimal places. Do not use the "$" or the "%" signs. Put negative signs where it's a cash outflow. Year o Year 1 Year 2 Year 3 Year 4 Year 5 $ $ $ $ $ IS Investor's BTCF Investor's BTIRR % Lender's IRR % (c) Next, for the convertible loan case, calculate the investor's BTIRR based on the investor's annual cash flows. Also, calculate the lender's IRR based on the lender's monthly cash flows (just like we did on the slides). Calculate both for the case when the investor uses the convertible loan with a different conversion percentage in each case, as shown in the table below. Put your answers in percent, not in decimals. Round all to 2 decimal places, and do not use the "%" signs. Put negative signs if any of your answers are negative. If the "conversion percentage" equals.......70% ...0% ..100% Investor's BTIRR % % % Lender's IRR % % % (d) Investor's annual return on invested funds in higher when the conversion percentage is Type "higher" or "lower". In addition, lender's annual return on lent funds in higher when the conversion percentage is Type "higher" or "lower". (1) Fixed-rate fully amortizing loan (3 of 9) EXAMPLE Calculate investor's annual rate of return Investor is considering the purchase of an apartment complex that's for sale for $1,000,000 90%: building (i.e., $900,000) 10%: land (i.e., $100,000) Estimated Nol in year 1 is $100,000 NOI & building value will increase by 3% per year Building's depreciable life is 27.5 years The property will be sold in 5 years Loan: Conventional, fixed-rate, constant loan payment, 70% LTV (i.e., $700,000), 10% annual interest, monthly payments, 15-year term Investor is in 28% tax bracket for ordinary income, capital gains, and depreciation recapture (5) Convertible loan (4 of 10) In our earlier example, EXAMPLES Loan: Lower than 10% on the conventional loan that we had earlier $700,000, fixed-rate, monthly payments, 30-year amortization term, 8.5% ,interest rate The 1.5% difference can be viewed as the cost to the Lender of acquiring this "option" Lender has the option to receive 65% of the resale price at the end of the 5th year So, here's what I want you to do: Re-do the problem in Chapter 12 PowerPoint slides 6 and 46-, EXCEPT: . the purchase price and the NOI for the first year should be both double what's on slide 6 (in other words, $2,000,000 and $200,000, respectively), AND the "conversion percentage" (that you see on slide 46) should be not 65%, but INSTEAD 70% / 0% / 100% (see last table on the bottom of this page), AND the loan term should be 30 years (like on slide 46), the annual loan interest rate should be 8.5% (like on slide 46), the loan should require monthly payments (like on the slides), and the LTV on the loan should still be 70 percent of your new purchase price. If you can do the math in Excel with correct referencing to cells with intermediate results. This will allow you to avoid rounding errors. Otherwise, increase decimal places - the more the better! Say, 6 or even higher. (a) First, calculate the required annual loan payment: $ Do not use the "$" sign. Round to whole dollar. (b) Next, for the conventional fixed-rate loan case, calculate the investor's BTCFs for years 0 through 5. Also, use these annual cash flows to calculate the investor's BTIRR. In addition, calculate the lender's IRR using lender's monthly cash flows (like we did in class). Round the BTCFs to whole dollar and the two IRRs to 2 decimal places. Do not use the "$" or the "%" signs. Put negative signs where it's a cash outflow. Year o Year 1 Year 2 Year 3 Year 4 Year 5 $ $ $ $ $ IS Investor's BTCF Investor's BTIRR % Lender's IRR % (c) Next, for the convertible loan case, calculate the investor's BTIRR based on the investor's annual cash flows. Also, calculate the lender's IRR based on the lender's monthly cash flows (just like we did on the slides). Calculate both for the case when the investor uses the convertible loan with a different conversion percentage in each case, as shown in the table below. Put your answers in percent, not in decimals. Round all to 2 decimal places, and do not use the "%" signs. Put negative signs if any of your answers are negative. If the "conversion percentage" equals.......70% ...0% ..100% Investor's BTIRR % % % Lender's IRR % % % (d) Investor's annual return on invested funds in higher when the conversion percentage is Type "higher" or "lower". In addition, lender's annual return on lent funds in higher when the conversion percentage is Type "higher" or "lower". (1) Fixed-rate fully amortizing loan (3 of 9) EXAMPLE Calculate investor's annual rate of return Investor is considering the purchase of an apartment complex that's for sale for $1,000,000 90%: building (i.e., $900,000) 10%: land (i.e., $100,000) Estimated Nol in year 1 is $100,000 NOI & building value will increase by 3% per year Building's depreciable life is 27.5 years The property will be sold in 5 years Loan: Conventional, fixed-rate, constant loan payment, 70% LTV (i.e., $700,000), 10% annual interest, monthly payments, 15-year term Investor is in 28% tax bracket for ordinary income, capital gains, and depreciation recapture (5) Convertible loan (4 of 10) In our earlier example, EXAMPLES Loan: Lower than 10% on the conventional loan that we had earlier $700,000, fixed-rate, monthly payments, 30-year amortization term, 8.5% ,interest rate The 1.5% difference can be viewed as the cost to the Lender of acquiring this "option" Lender has the option to receive 65% of the resale price at the end of the 5th year