Create an Excel spreadsheet to demonstrate how to calculate the benefit of using concessionary loan

Case Study: Centralia Corporation O A US manufacturer of small kitchen electrical appliances currently sells microwaves in Spain through an affiliate. Current sales are 9,600 units/year and increasing at a rate of 5%. 0 Price is $180 per unit, of which $35 represents the profit margin and expected to increase with inflation. O O Project: To build a manufacturing facility in Zaragoza, Spain Cost of plant is 5,500,000 O Creates a borrowing capacity $2,904,000; O New plant will be depreciated over 8 years; Centralia will get a special financing deal: O 4,000,000 at 5% per year; 0 Normal borrowing rate is 8% in dollars and 7% in ; O Principal to be repaid in eight equal installments; Sales to EU forecast at 25,000 units in the first year and expect to increase by 12% per year o Sales price is 200/unit and production cost is 160/unit in the 1st year Both are expected to increase with inflation. Other Information O Madrid sales affiliate accumulated a net amount of 750,000 from its operations, which can be used to partially finance construction cost (this is an example of RF); The accumulated funds (750,000) were earned under special tax concessions and taxed at a marginal rate of 20%. Expected inflation: 2.1% in Spain; 3% in the U.S The current exchange rate: $1.32/. Marginal tax rate in Spain and the U.S.: 35% Dollar all-equity cost of capital: 12% Should Centralia take the project? O PV of the concessionary loan payments (Exhibit 18.4) Year St Loan Outstanding Principal Interest Total Loan PMT PV of LP (id=8%) $ 1.3200 4,000,000 1.3316 3,500,000 500,000 200,000 863,097 1.3434 3,000,000 500,000 175,000 1.3552 2,500,000 500,000 150,000 932,145 906,777 880,890 854,476 827,528 800,038 777,415 699,279 628,065 1.3672 2,000,000 500,000 125,000 1.3792 1,500,000 500,000 100,000 563,202 1,000,000 500,000 75,000 504,160 1.3914 1.4036 500,000 500,000 50,000 771,999 450,454 401,638 1.4160 500,000 25,000 743,404 4,887,311 Benefit from the concessionary loan (Exhibit 18.5) S,CL.- SLP, 1=1(1 + id) O S.CLo: $ amount of concessionary loan= $1.32*4,000,000 The 2nd item represents the size of the equivalent loan available (in $) from borrowing at the normal borrowing rate with a debt service schedule equivalent to that of the concessionary loan. The difference $392,689 represents the PV of the benefit of the below market rate financing of the concessionary loan. 96 Case Study: Centralia Corporation O A US manufacturer of small kitchen electrical appliances currently sells microwaves in Spain through an affiliate. Current sales are 9,600 units/year and increasing at a rate of 5%. 0 Price is $180 per unit, of which $35 represents the profit margin and expected to increase with inflation. O O Project: To build a manufacturing facility in Zaragoza, Spain Cost of plant is 5,500,000 O Creates a borrowing capacity $2,904,000; O New plant will be depreciated over 8 years; Centralia will get a special financing deal: O 4,000,000 at 5% per year; 0 Normal borrowing rate is 8% in dollars and 7% in ; O Principal to be repaid in eight equal installments; Sales to EU forecast at 25,000 units in the first year and expect to increase by 12% per year o Sales price is 200/unit and production cost is 160/unit in the 1st year Both are expected to increase with inflation. Other Information O Madrid sales affiliate accumulated a net amount of 750,000 from its operations, which can be used to partially finance construction cost (this is an example of RF); The accumulated funds (750,000) were earned under special tax concessions and taxed at a marginal rate of 20%. Expected inflation: 2.1% in Spain; 3% in the U.S The current exchange rate: $1.32/. Marginal tax rate in Spain and the U.S.: 35% Dollar all-equity cost of capital: 12% Should Centralia take the project? O PV of the concessionary loan payments (Exhibit 18.4) Year St Loan Outstanding Principal Interest Total Loan PMT PV of LP (id=8%) $ 1.3200 4,000,000 1.3316 3,500,000 500,000 200,000 863,097 1.3434 3,000,000 500,000 175,000 1.3552 2,500,000 500,000 150,000 932,145 906,777 880,890 854,476 827,528 800,038 777,415 699,279 628,065 1.3672 2,000,000 500,000 125,000 1.3792 1,500,000 500,000 100,000 563,202 1,000,000 500,000 75,000 504,160 1.3914 1.4036 500,000 500,000 50,000 771,999 450,454 401,638 1.4160 500,000 25,000 743,404 4,887,311 Benefit from the concessionary loan (Exhibit 18.5) S,CL.- SLP, 1=1(1 + id) O S.CLo: $ amount of concessionary loan= $1.32*4,000,000 The 2nd item represents the size of the equivalent loan available (in $) from borrowing at the normal borrowing rate with a debt service schedule equivalent to that of the concessionary loan. The difference $392,689 represents the PV of the benefit of the below market rate financing of the concessionary loan. 96