Please SHOW ALL Excel formuas.
Previous information:
Problem I need help with:
Problem Set 7 - Long Term Financing and Leasing 1. In May 2011, Apache issued a 10-year, $263M bond paying 8.0% annually in two equal coupons each May and November. It is now May 2015 and Apache just paid the May coupon on its existing bond. Rates have come down, so it is thinking of buying back the bond and issuing a 5-year, $330M bond. This bond matures in May 2020 and will pay 3.0% per year in equal coupons each May and November. a. What is the price that Apache must pay the current bond holders to buy back the bond? (Hint - the present value of the coupon payments and the final face value) b. What are the cash flows associated with the new bond? c. What are the cash flow differentials to Apache? In other words, what are the net cash flows in or out for Apache each May and November when comparing both bonds? d. What is the present value of these cash flow differentials? e. What does the answer to Question 1, Part d tell you? Semi annual coupon starting Nov 2011 (since bonds were issued in May 2011) Value of the semi annual copupn = Face Value of the existing bond x Annual coupon rate / 2 = 263 x 8% / 2 = $ 10.52 million. Matures in May 2021 and will result into repayment of the par value. Hence the cash flow in the month of May 2021 = semi annual coupon + face value = 10.52 +263 = $ 273.52 The new bonds: Semi annual coupon starts in Nov 2015 (since bonds were issued in May 2015) Value of semi annual coupon = Face Value of the existing bond x Annual coupon rate / 2 = 330 x 3%/ 2 = $ 4.95 million. Assign a negative sign to it because it's an outflow. Mature in May 2020 and will result into repayment of the par value. Hence the cash flow in the month of May 2020 = semi annual coupon + face value = 4.95 + 330 = $ 334.95 Rest of the formula appear in the excel snapshot. Formula appears in the adjacent cell and are colored in blue. Formula snapshot: 2. Apache's real estate department is considering buying a hangar and leasing it out to private jet operators. They ask you to calculate the NPV and IRR of the investment and have given you the data below. Assume that the hangar is sold in year 25 and that the mortgage runs 25 years. Inflator Item Square Footage Property Price ($) Down Payment Interest Rate Closing Costs at Start Broker Fee in Year 25 Yearly Property Appreciation Rent/ sq. ft/Inflator Op. Costs/ year ($) Inflator Tax Rate Depreciation/ year ($) Value 1,910 1,015,000 10% 3.9% 8,000 5.0% 1.5% 3.00 1.0% 1.0% 11,640 21.0% 4,524 1 2 3 4 $ 6 7 8 9 10 Key Assumptions Square footage Property price (S) Down payment Interest rate Closing costs at start Broker fee in year 25 Property Value Yearly appreciation Mortgage Balance Net Property Value Operating Assumptions Rent/s/Inflator Op. costsyr. (S)/Inflator Tax rate Depreciation ycar (5) Cash Flows Rent Income minus: Operating Costs minus: Debt Amortization plus: Interest tax shield plus: Depreciation tax shick minus Initial Expenses plus: Sale Property in year 25 Total Cash Flows IRR NPV 5% Note: Tax shields are the tax gains from expensing interest or depreciation. The general formula is interest expense x tax rate for interest tax shield, Prob. 1 Prob. 2 Mort. Amort. 11 12 13 14 15 16 18 19 20 21 22 23 24 25 . Problem Set 7 - Long Term Financing and Leasing 1. In May 2011, Apache issued a 10-year, $263M bond paying 8.0% annually in two equal coupons each May and November. It is now May 2015 and Apache just paid the May coupon on its existing bond. Rates have come down, so it is thinking of buying back the bond and issuing a 5-year, $330M bond. This bond matures in May 2020 and will pay 3.0% per year in equal coupons each May and November. a. What is the price that Apache must pay the current bond holders to buy back the bond? (Hint - the present value of the coupon payments and the final face value) b. What are the cash flows associated with the new bond? c. What are the cash flow differentials to Apache? In other words, what are the net cash flows in or out for Apache each May and November when comparing both bonds? d. What is the present value of these cash flow differentials? e. What does the answer to Question 1, Part d tell you? Semi annual coupon starting Nov 2011 (since bonds were issued in May 2011) Value of the semi annual copupn = Face Value of the existing bond x Annual coupon rate / 2 = 263 x 8% / 2 = $ 10.52 million. Matures in May 2021 and will result into repayment of the par value. Hence the cash flow in the month of May 2021 = semi annual coupon + face value = 10.52 +263 = $ 273.52 The new bonds: Semi annual coupon starts in Nov 2015 (since bonds were issued in May 2015) Value of semi annual coupon = Face Value of the existing bond x Annual coupon rate / 2 = 330 x 3%/ 2 = $ 4.95 million. Assign a negative sign to it because it's an outflow. Mature in May 2020 and will result into repayment of the par value. Hence the cash flow in the month of May 2020 = semi annual coupon + face value = 4.95 + 330 = $ 334.95 Rest of the formula appear in the excel snapshot. Formula appears in the adjacent cell and are colored in blue. Formula snapshot: 2. Apache's real estate department is considering buying a hangar and leasing it out to private jet operators. They ask you to calculate the NPV and IRR of the investment and have given you the data below. Assume that the hangar is sold in year 25 and that the mortgage runs 25 years. Inflator Item Square Footage Property Price ($) Down Payment Interest Rate Closing Costs at Start Broker Fee in Year 25 Yearly Property Appreciation Rent/ sq. ft/Inflator Op. Costs/ year ($) Inflator Tax Rate Depreciation/ year ($) Value 1,910 1,015,000 10% 3.9% 8,000 5.0% 1.5% 3.00 1.0% 1.0% 11,640 21.0% 4,524 1 2 3 4 $ 6 7 8 9 10 Key Assumptions Square footage Property price (S) Down payment Interest rate Closing costs at start Broker fee in year 25 Property Value Yearly appreciation Mortgage Balance Net Property Value Operating Assumptions Rent/s/Inflator Op. costsyr. (S)/Inflator Tax rate Depreciation ycar (5) Cash Flows Rent Income minus: Operating Costs minus: Debt Amortization plus: Interest tax shield plus: Depreciation tax shick minus Initial Expenses plus: Sale Property in year 25 Total Cash Flows IRR NPV 5% Note: Tax shields are the tax gains from expensing interest or depreciation. The general formula is interest expense x tax rate for interest tax shield, Prob. 1 Prob. 2 Mort. Amort. 11 12 13 14 15 16 18 19 20 21 22 23 24 25
